Monday, 9 November 2015

Get worst performing queries on your database/server with T-SQL

Finding worst performing queries 

The following query returns information about the worst queries ranked by average CPU time. it aggregates the queries according to their query hash so that logically equivalent queries are grouped by their cumulative resource consumption, so top in the list is the costlier by Avg CPU Time. you can edit the query if you want to get top 5 or 10 worst queries by top n clause.

SELECT 
MIN(query_text.statement_text) AS [Query Text], 
    SUM(query_text.total_worker_time) / SUM(query_text.execution_count) AS [Avg CPU Time],
SUM(query_text.total_elapsed_time)/SUM(query_text.execution_count) AS [AVG Execution Time],
SUM(total_physical_reads) [Total Physical Reads],
SUM(total_rows) [Total Rows Returned]
FROM 
    (SELECT 
EQS.*, 
SUBSTRING(ST.text, (EQS.statement_start_offset/2) + 1,
((CASE statement_end_offset 
WHEN -1 THEN DATALENGTH(ST.text)
ELSE EQS.statement_end_offset END 
            - EQS.statement_start_offset)/2) + 1) AS statement_text
     FROM sys.dm_exec_query_stats AS EQS
     CROSS APPLY sys.dm_exec_sql_text(EQS.sql_handle) as ST) as query_text
GROUP BY query_text.query_hash

ORDER BY [Avg CPU Time] DESC;


No comments:

Post a comment