4

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!

6

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.

  • It is, in fact encrypted. Added st.encrypted to the query and they both have a value of 1. Marking as answer. – Kris Gruttemeyer Sep 10 '14 at 14:50
6

There are limitations when using sys.dm_exec_query_plan

5.4 When and How to Use Sys.dm_exec_text_query_plan

The query_plan column returned by sys.dm_exec_query_plan has type XML and suffers from the inherent limitation of the data type that it cannot have nest levels greater than or equal to 128. In SQL Server 2005 RTM and SP1, if the query_plan had a depth of greater than or equal to 128 levels this would prevent the query from returning. The second limitation is that it is not easy to get the query plan of a particular statement in the batch directly from the DMVs without parsing the query plan XML returned from sys.dm_exec_query_plan.

To address both these limitations, in SQL Server 2005 SP2, we introduced sys.dm_exec_text_query_plan. Sys.dm_exec_text_query_plan is a TVF that takes 3 parameters: plan_handle, statement_start_offset and statement_end_offset. It returns the showplan in text format for the batch or for a specific statement within the batch.

Instead of reinventing the wheel, best is to use Brent's - sp_BlitzCache or Glenn's Diagnostic queries. or Aaron's answer here.

From SQL Server Query Performance Analysis using DMVs :

-- Which Queries are taking the most time/cpu to execute
SELECT TOP 20
    total_worker_time, total_elapsed_time,
    total_worker_time/execution_count AS avg_cpu_cost, execution_count,
    (SELECT DB_NAME(dbid) + ISNULL('..' + OBJECT_NAME(objectid), '')
        FROM sys.dm_exec_sql_text([sql_handle])) AS query_database,
    (SELECT SUBSTRING(est.[text], statement_start_offset/2 + 1,
        (CASE WHEN statement_end_offset = -1
            THEN LEN(CONVERT(nvarchar(max), est.[text])) * 2
            ELSE statement_end_offset
            END - statement_start_offset) / 2
        )
        FROM sys.dm_exec_sql_text([sql_handle]) AS est) AS query_text,
    total_logical_reads/execution_count AS avg_logical_reads,
    total_logical_writes/execution_count AS avg_logical_writes,
    last_worker_time, min_worker_time, max_worker_time,
    last_elapsed_time, min_elapsed_time, max_elapsed_time,
    plan_generation_num, qp.query_plan
FROM sys.dm_exec_query_stats
    OUTER APPLY sys.dm_exec_query_plan([plan_handle]) AS qp
WHERE [dbid] >= 5 AND DB_NAME(dbid) IS NOT NULL
  AND (total_worker_time/execution_count) > 100
--ORDER BY avg_cpu_cost DESC;
--ORDER BY execution_count DESC;
ORDER BY total_worker_time DESC;
  • Great info here, thanks so much Kin. Turns out that it's encrypted so that's going to be my starting point. – Kris Gruttemeyer Sep 10 '14 at 14:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.