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 = 0 THEN 100 ELSE (qs.last_worker_time - ((qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1))) * 100 END / 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 = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) , [Parent Query] = qt.text , [DatabaseName] = DB_NAME(qt.dbid) INTO #SlowQueries FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) qt WHERE 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] = 0 THEN 0 ELSE ([Last IO]- [Avg IO]) * 100 / [Avg IO] END , [Individual Query] , [Parent Query] , [DatabaseName] INTO #SlowQueriesByIO FROM #SlowQueries ORDER 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 #SlowQueriesByIO WHERE [% Time Deviation] - [% IO Deviation] > 20 --and [individual query] like '%d001%' ORDER BY [Impedance] DESC -- Tidy up. DROP TABLE #SlowQueries DROP TABLE #SlowQueriesByIO


ċ
Andy Hughes,
Jun 8, 2020, 12:15 PM
Comments