Get Slow Running Queries

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Tested

Credits:

Author: Ian Stirk 2008

Date: 13 Jan 2008

Description

Identifies queries that are running slow, or slower than usual. Utilizes historical IO and Duration figures to list the top 100 poor performers.

Code

/*---------------------------------------------------------------------- Purpose: Identify queries that are running slower than normal , when taking into account IO volumes. ------------------------------------------------------------------------ Parameters: None. Revision History: 13/01/2008 Ian_Stirk@yahoo.com Initial version ----------------------------------------------------------------------*/-- Do not lock anything, and do not get held up by any locks. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED-- Identify queries running slower than normal. SELECT --TOP 100 [Runs] = qs.execution_count--, [Total time] = qs.total_worker_time - qs.last_worker_time , [Avg time] = (qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1), [Last time] = qs.last_worker_time, [Time Deviation] = (qs.last_worker_time - ((qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1))), [% Time Deviation] =CASE WHEN qs.last_worker_time = 0THEN 100ELSE (qs.last_worker_time - ((qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1))) * 100END/ CASE WHEN (((qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1))) = 0 THEN 1 ELSE (((qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1))) END, [Last IO] = last_logical_reads + last_logical_writes + last_physical_reads, [Avg IO] = ((total_logical_reads + total_logical_writes + total_physical_reads)- (last_logical_reads + last_logical_writes + last_physical_reads))/ (qs.execution_count - 1), [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,(CASE WHEN qs.statement_end_offset = -1THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2ELSE qs.statement_end_offset END - qs.statement_start_offset)/2), [Parent Query] = qt.text, [DatabaseName] = DB_NAME(qt.dbid)INTO #SlowQueriesFROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) qtWHERE qs.execution_count > 1--ORDER BY [% Time Deviation] DESC ORDER BY [Last time] DESC-- Calculate the [IO Deviation] and [% IO Deviation]. -- Negative values means we did less I/O than average. SELECT-- TOP 100 [Runs], [Avg time], [Last time], [Time Deviation], [% Time Deviation], [Last IO], [Avg IO], [IO Deviation] = [Last IO] - [Avg IO], [% IO Deviation] =CASE WHEN [Avg IO] = 0THEN 0ELSE ([Last IO]- [Avg IO]) * 100 / [Avg IO]END, [Individual Query], [Parent Query], [DatabaseName]INTO #SlowQueriesByIOFROM #SlowQueriesORDER BY [% Time Deviation] DESC-- Extract items where [% Time deviation] less [% IO deviation] is 'large' -- These queries are slow running, even when we take into account IO deviation. SELECT-- TOP 100 [Runs], [Avg time], [Last time], [Time Deviation], [% Time Deviation], [Last IO], [Avg IO], [IO Deviation], [% IO Deviation], [Impedance] = [% Time Deviation] - [% IO Deviation], [Individual Query], [Parent Query], [DatabaseName]FROM #SlowQueriesByIOWHERE [% Time Deviation] - [% IO Deviation] > 20--and [individual query] like '%d001%' ORDER BY [Impedance] DESC-- Tidy up. DROP TABLE #SlowQueriesDROP TABLE #SlowQueriesByIO