December 19, 2018 at 2:59 pm
Hello experts,
I'm learning more about Dynamic Management Views (DMVs) and noticed that sometimes the values for certain seemingly important columns are NULL.
I picked this example just to explain, not because I noticed any more NULLs in it than in any other examples I have found.
This page:
http://sqlserverplanet.com/dmvs/find-queries-taking-most-cpu-processor
Has this query (run it in the master database):--Find queries that have the highest average CPU usage
SELECT TOP 50
ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)
,TextData = qt.text
,DiskReads = qs.total_physical_reads --The worst reads, disk reads
,MemoryReads = qs.total_logical_reads --Logical Reads are memory reads
,Executions = qs.execution_count
,TotalCPUTime = qs.total_worker_time
,AverageCPUTime = qs.total_worker_time/qs.execution_count
,DiskWaitAndCPUTime = qs.total_elapsed_time
,MemoryWrites = qs.max_logical_writes
,DateCached = qs.creation_time
,DatabaseName = DB_Name(qt.dbid)
,LastExecutionTime = qs.last_execution_time
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.total_worker_time/qs.execution_count DESC
On my SQL Server, several of the output rows for this query have NULLs for ObjectName and DatabaseName, even though the DMV logged it as a high-CPU query and the SQLText column has a value in it.
Could someone explain to me how this can happen?
Thanks for any help.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
December 19, 2018 at 3:27 pm
In order for those functions to get the correct value - there must be an object_id and db_id value. For those rows - those values are NULL because the code is adhoc or prepared statements and not from a procedure or function.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 20, 2018 at 2:54 am
This was removed by the editor as SPAM
December 20, 2018 at 9:22 am
Thanks for the info! It gives me a sense of when and why these values would be blank.
Regarding this question from insharh2297 "Which brings up the issue: How would you see the arrangement of simply the piece of the SP that has been reserved? " I do not know, but perhaps someone else might see this thread and provide an answer.
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply