List all the statements in the Plan Cache along with the counts and CPU usage



SELECT t.[text] AS [Adhoc Batch or Object Call],


SUBSTRING(t.[text], (qs.[statement_start_offset]/2) + 1,


((CASE qs.[statement_end_offset]


WHEN 1 THEN DATALENGTH(t.[text]) ELSE qs.[statement_end_offset] END


qs.[statement_start_offset])/2) + 1) AS [Executed Statement]


, qs.[execution_count] AS [Counts]


, qs.[total_worker_time] AS [Total Worker Time], (qs.[total_worker_time] /


qs.[execution_count]) AS [Avg Worker Time]


, qs.[total_physical_reads] AS [Total Physical Reads],


(qs.[total_physical_reads] / qs.[execution_count]) AS [Avg Physical Reads]


, qs.[total_logical_writes] AS [Total Logical Writes],


(qs.[total_logical_writes] / qs.[execution_count]) AS [Avg Logical Writes]


, qs.[total_logical_reads] AS [Total Logical Reads],


(qs.[total_logical_reads] / qs.[execution_count]) AS [Avg Logical Reads]


, qs.[total_clr_time] AS [Total CLR Time], (qs.[total_clr_time] /


qs.[execution_count]) AS [Avg CLR Time]


, qs.[total_elapsed_time] AS [Total Elapsed Time], (qs.[total_elapsed_time]


/ qs.[execution_count]) AS [Avg Elapsed Time]


, qs.[last_execution_time] AS [Last Exec Time], qs.[creation_time] AS


[Creation Time]


FROM sys.dm_exec_query_stats AS qs


CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t


— ORDER BY [Total Worker Time] DESC


— ORDER BY [Total Physical Reads] DESC


— ORDER BY [Total Logical Writes] DESC


— ORDER BY [Total Logical Reads] DESC


— ORDER BY [Total CLR Time] DESC


— ORDER BY [Total Elapsed Time] DESC


ORDER BY [Counts] DESC