TSQLScripts.com: Script Details

TSQLScripts.com.

skip to the main content area of this page

Transact SQL scripts written by
DBAs and Developers for
DBAs and Developers.

To submit a comment about this script click here here or to submit your own script click here.

Identifying Queries Running Slower Than Normal
Description:
This script was written by Ian Stirk. You can read the full article here: http://www.sqlservercentral.com/articles/Performance+Tuning/63638/. The utility described in this script allows you to quickly identify which queries are running slower than expected on SQL Server 2005. Recognising these queries will allow you to take corrective targeted action to improve their performance. Various possible solutions are proposed.
Submitted By: 2008DBA

CREATE PROC [dbo].[dba_QueryTimeDelta]
AS
/*----------------------------------------------------------------------
 
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

Example Usage:
	1. exec YourServerName.master.dbo.dba_QueryTimeDelta			
 
----------------------------------------------------------------------*/

BEGIN

	-- 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 
		/ (((qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1)))
	, [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

	-- 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
	ORDER BY [Impedance] DESC

	-- Tidy up.
	DROP TABLE #SlowQueries
	DROP TABLE #SlowQueriesByIO
END