- Just as the text returned from the sql_handle is the text for the entire batch, the compiled plans that are returned are for the entire batch.
- For optimum troubleshooting, we can use sys.dm_exec_query_stats to return performance information for individual queries within a batch.
- This view returns performance statistics for queries, aggregated across all executions of the same query. This view also returns both a sql_handle and a plan_handle, as well as the start and end offsets like we saw in sys.dm_exec_requests.
The following query returns the top 10 queries by total CPU time, to help you identify the most expensive queries on your SQL Server instance:-
SELECT TOP 10 SUBSTRING(qt.TEXT, (qst.statement_start_offset/2)+1, ((CASE qst.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ELSE qst.statement_end_offset END - qst.statement_start_offset)/2)+1) AS query_text , qst.total_logical_reads, qst.last_logical_reads, qst.total_logical_writes, qst.last_logical_writes, qst.execution_count, qst.total_worker_time, qst.last_worker_time, qst.last_execution_time, qp.query_plan FROM sys.dm_exec_query_stats qst CROSS APPLY sys.dm_exec_sql_text(qst.sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(qst.plan_handle) qp ORDER BY qst.total_worker_time DESC
Output:-
