2

As per my understandings, from sys.dm_exec_query_stats, we can cross apply other DMFs to get the statement level cached plans and the query text. From sys.dm_exec_cached_plans, we can get the cached plans and the query text (by cross apply other DMFs). So what's the differences?

1 Answer 1

1

In terms of getting the plan, using sys.dm_exec_query_stats you can use statement_start_offset and End offset to get the plan for individual statement in a stored procedure.

Eg

select top 100 object_name(object_id),ps.execution_count as proc_exec_count, qs.plan_generation_num, qs.execution_count as Stmt_exec_count,qs.total_elapsed_time/qs.execution_count as AVG_Run_Time,   qs.total_worker_time/qs.execution_count AS Avg_CPU_Time,
qs.last_elapsed_time, qs.min_elapsed_time, qs.max_elapsed_time,qs.min_rows,qs.max_rows,
SUBSTRING ([st].[text], 
        ([qs].[statement_start_offset] / 2) + 1, 
        ((CASE [statement_end_offset] 
            WHEN -1 THEN DATALENGTH ([st].[text]) 
            ELSE [qs].[statement_end_offset] END 
            - [qs].[statement_start_offset]) / 2) + 1) 
            AS [StatementText],cast(s3.query_plan as xml) query_plan
from sys.dm_exec_procedure_stats ps  
JOIN [sys].[dm_exec_query_stats] AS [qs]
        ON [ps].[plan_handle] = [qs].[plan_handle]
cross apply sys.dm_exec_text_query_plan ([qs].[plan_handle], statement_start_offset, statement_end_offset) s3
CROSS APPLY [sys].[dm_exec_sql_text]    ([qs].[sql_handle]) AS [st]
where database_id=db_id('DB') --and qs.execution_count >100 
and object_id in (object_id('SP Name')) 
ORDER BY avg_run_time DESC

And obviously it has different information regarding query execution stats and plans.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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