I've added a NullIf function to prevent the divide by zero error:-------------------------------------------------------------------------- ----------------------------------------------------------------------------------
-- Purpose: Identify queries that are running slower than normal,
-- when taking into account IO volumes.
--------------------------------------------------------------------------
ALTER PROC [dbo].[dba_QueryTimeDelta]
AS
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 / NullIf((((qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1))), 0),
[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
GO