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