SQL Server – Troubleshoot NULL in Query Plan and SQL Text for Top Queries

dmvsql servertop

I'm using the following code to pull our top 20 queries (ordered by CPU):

SELECT TOP 20 qs.sql_handle
    ,qs.execution_count
    ,qs.total_worker_time AS [Total CPU]
    ,qs.total_worker_time / 1000000 AS [Total CPU in Seconds]
    ,(qs.total_worker_time / 1000000) / qs.execution_count AS [Average CPU in Seconds]
    ,qs.total_elapsed_time
    ,qs.total_elapsed_time / 1000000 AS [Total Elapsed Time in Seconds]
    ,st.TEXT
    ,qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC

However, I'm seeing this:
enter image description here

Can anyone shed some light as to why the Query Plan and SQL Text are showing up NULL? Are they some sort of system process or external application? We are running SQL 2008 R2.

Thanks, as always, everyone!

Best Answer

In terms of the Statement Text being NULL, when selecting the text from sys.dm_exec_sql_text, the text value is NULL for encrypted objects.

Check the encrypted value of the record from sys.dm_exec_sql_text.

Wait Stats

ASYNC_NETWORK_IO

On The Server

Network Interface:Output queue length
Redirector:Network error/sec
- This should always be zero
TCPv4:Segments retransmitted/sec & TCPv6:Segment retransmitted/sec
- When divided over the total number of segments should not be more than 3%.

Sql-server – sp_ExecuteSQL, performance and table variables

There is an implicit CREATE TABLE at the beginning and DROP TABLE @T at the end as the table goes out of scope that aren't associated with either the INSERT or the SELECT statement.

If you replace with a #temp table you should see some additional reads for both the drop and create statements. For me I see 36 for the CREATE and 100 for the DROP so that accounts for 136 of your missing 137 reads.

(Script with #temp table)

EXEC sp_executesql N'
   CREATE TABLE #T 
  ( 
     x INT 
  ) 

INSERT INTO #T 
            (x) 
SELECT TOP 10 number 
FROM   master..spt_values 

SELECT * 
FROM   #T 
UNION 
SELECT * 
FROM   #T 

DROP TABLE #T
'

Best Answer

Related Solutions

Sql-server – SQL 2008 R2 – Odd timeout issues

Wait Stats

On The Server

Sql-server – sp_ExecuteSQL, performance and table variables

Related Question