This being a proc makes it less usable than it could be. Here is a version that is a view. As a view it can be sorted as necessary and the deviation can be change at invocation as well. I think i moved it around and it is still accurate.
CREATE VIEW LastExecutionQueryStatsComparedToAvg
AS
SELECT [Runs]
, [Avg time]
, [Last time]
, [Time Deviation]
, [% Time Deviation]
, [Last IO]
, [Avg IO]
, [IO Deviation] = [Last IO] - [Avg IO]
, [Individual Query]
, [Parent Query]
, [DatabaseName]
, [Last Execution Time]
, [% IO Deviation] =
CASE WHEN ([Total IO] - [Last IO])/ (Runs - 1) = 0 THEN 0
ELSE ([Last IO]- (([Total IO] - [Last IO])
/ (Runs - 1))) * 100 / ([Total IO] - [Last IO])
/ (Runs - 1)
END
, [Impedance] = [% Time Deviation] -
CASE WHEN ([Total IO] - [Last IO])/ (Runs - 1) = 0 THEN 0
ELSE ([Last IO]- (([Total IO] - [Last IO])
/ (Runs - 1))) * 100 / ([Total IO] - [Last IO])
/ (Runs - 1)
END
FROM (
SELECT
qs.execution_count Runs
, [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)))
, [Total IO] = (total_logical_reads + total_logical_writes + total_physical_reads)
, [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)
, [Last Execution Time] = qs.last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) qt
WHERE qs.execution_count > 1
) SlowQueries