This is a script I use to look at the plans in use for a stored procedure and some basic stats about how each part is performing. Sometimes procedures will have multiple plans cached, you can see the last execution date and number of executions to get an idea of which plan you are interested in.

SELECT (SELECT TOP 1 SUBSTRING(x.[text],statement_start_offset / 2+1 , 
      ( (CASE WHEN statement_end_offset = -1 
         THEN (LEN(CONVERT(nvarchar(max),x.[text])) * 2) 
         ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS sql_statement,
         s.last_execution_time,
         s.execution_count,
         s.total_physical_reads/s.execution_count AvgDiskReads,
         s.total_logical_reads/s.execution_count AvgMemoryReads,
         s.total_logical_writes/s.execution_count AvgWrites,
         (s.total_elapsed_time/s.execution_count)/1000 AvgTime,
         s.max_elapsed_time/1000 MaxTime,
         s.total_rows/s.execution_count AvgRows, (s.total_worker_time/s.execution_count)/1000 AvgWorkerTime,
         s.last_execution_time,
         s.plan_handle,
         p.query_plan
FROM sys.dm_exec_query_stats S
CROSS APPLY sys.dm_exec_sql_text(S.sql_handle) X
OUTER APPLY sys.dm_exec_query_plan (S.plan_handle) P
WHERE X.objectid = OBJECT_ID('dbo.ProcName')
ORDER BY s.plan_handle, statement_start_offset

 

Tagged with →  

Leave a Reply

Your email address will not be published. Required fields are marked *