When I start a SQL Server Performance Root Cause Analysis I like to find the top waits and then find the queries causing the top waits. Next, I like to understand what is running and monitor disk latency. Finally, I would like to probe the cache to see what are my top offenders since the plans were cached.
** DOWNLOAD SCRIPTS **
Today, in this blog post were going to focus on the last remaining item, probing the cache to get top offenders. I do this because I would like to know if my current problem is also a long term problem. If you have stored procedures that get accessed frequently there is a good chance they will stay in cache. This allows you to take advantage of sys.dm_exec_query_stats to get aggregated information about cpu, reads, writes, duration for those plans. My favorite tw0 columns in sys.dm_exec_query_stats is query_hash and query_plan_hash.
QUERY_HASH and QUERY_PLAN_HASH
In the field I see a lot of people pulling data from sys.dm_exec_query_stats without grouping by query_hash and/or query_plan_hash. I strongly recommend you group by the hash columns because they can identify statements that are only different by literal values and statements with similar execution plans. For example, in the real world I have seen stored procedures with duplicate code. Basically, someone did a copy and paste when they created the a new stored procedure. Therefore, these stored procedures would have the same query_hash and query_plan_hash even thought the code belongs to different stored procedures.
How Many Executions?
Personally, I also want to know my top offenders for a few different cases. For example, if my top I/O statement only executed once it might not be as important as another statement that is the 3rd highest offender with I/O but executed 100,000 times. Therefore, I added a parameter into my stored procedures so I can filter by execution count. This allows me to find my sweet spot for top offenders for a resource vs execution counts. I also added another parameter so I can filter how many statements are returned. This quickly allows me to do TOP 10 or TOP 5 or TOP 20 on the fly.
Now, lets take a look at the code.
Total I/O
/****** Object: StoredProcedure [dbo].[GetTopStatements_TotalIO] Script Date: 10/14/2013 10:16:35 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: John Sterrett (@JohnSterrett) -- Create date: 6/4/2014 -- Description: Gets Top IO statements based on execution count -- Example: exec dbo.GetTopStatements_TotalIO @NumOfStatements = 5, @Executions = 100 -- ============================================= CREATE PROCEDURE [dbo].[GetTopStatements_TotalIO] -- Add the parameters for the stored procedure here @NumOfStatements int = 25, @Executions int = 5 AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here --- top 25 statements by IO IF OBJECT_ID('tempdb..#TopOffenders') IS NOT NULL DROP TABLE #TopOffenders IF OBJECT_ID('tempdb..#QueryText') IS NOT NULL DROP TABLE #QueryText CREATE TABLE #TopOffenders (AvgIO bigint, TotalIO bigint, TotalCPU bigint, AvgCPU bigint, TotalDuration bigint, AvgDuration bigint, [dbid] int, objectid bigint, execution_count bigint, query_hash varbinary(8)) CREATE TABLE #QueryText (query_hash varbinary(8), query_text varchar(max)) INSERT INTO #TopOffenders (AvgIO, TotalIO, TotalCPU, AvgCPU, TotalDuration, AvgDuration, [dbid], objectid, execution_count, query_hash) SELECT TOP (@NumOfStatements) SUM((qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count) as [Avg IO], SUM((qs.total_logical_reads + qs.total_logical_writes)) AS [TotalIO], SUM(qs.total_worker_time) AS Total_Worker_Time, SUM((qs.total_worker_time) / qs.execution_count) AS [AvgCPU], SUM(qs.total_elapsed_time) AS TotalDuration, SUM((qs.total_elapsed_time)/ qs.execution_count) AS AvgDuration, qt.dbid, qt.objectid, SUM(qs.execution_count) AS Execution_Count, qs.query_hash FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt GROUP BY qs.query_hash, qs.query_plan_hash, qt.dbid, qt.objectid HAVING SUM(qs.execution_count) > @Executions ORDER BY [TotalIO] DESC --select * From #TopOffenders --ORDER BY TotalIO desc /* Create cursor to get query text */ DECLARE @QueryHash varbinary(8) DECLARE QueryCursor CURSOR FAST_FORWARD FOR select query_hash FROM #TopOffenders OPEN QueryCursor FETCH NEXT FROM QueryCursor INTO @QueryHash WHILE (@@FETCH_STATUS = 0) BEGIN INSERT INTO #QueryText (query_text, query_hash) select MIN(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)) as query_text, qs.query_hash from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt where qs.query_hash = @QueryHash GROUP BY qs.query_hash; FETCH NEXT FROM QueryCursor INTO @QueryHash END CLOSE QueryCursor DEALLOCATE QueryCursor select distinct DB_NAME(dbid) DBName, OBJECT_NAME(objectid, dbid) ObjectName, qt.query_text, o.* INTO #Results from #TopOffenders o join #QueryText qt on (o.query_hash = qt.query_hash) SELECT TOP (@NumOfStatements) * FROM #Results ORDER BY TotalIO desc DROP TABLE #Results DROP TABLE #TopOffenders DROP TABLE #QueryText END GO
Total CPU
/****** Object: StoredProcedure [dbo].[GetTopStatements_TotalCPU] Script Date: 10/14/2013 10:21:32 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: John Sterrett (@JohnSterrett) -- Create date: 6/4/2013 -- Description: Gets statements causing most CPU from cache based on executions. -- Example: exec dbo.GetTopStatements_TotalCPU @Executions = 5, @NumOfStatements = 25 -- ============================================= CREATE PROCEDURE [dbo].[GetTopStatements_TotalCPU] -- Add the parameters for the stored procedure here @NumOfStatements int = 25, @Executions int = 5 AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here --- top 25 statements by IO IF OBJECT_ID('tempdb..#TopOffenders') IS NOT NULL DROP TABLE #TopOffenders IF OBJECT_ID('tempdb..#QueryText') IS NOT NULL DROP TABLE #QueryText CREATE TABLE #TopOffenders (AvgIO bigint, TotalIO bigint, TotalCPU bigint, AvgCPU bigint, TotalDuration bigint, AvgDuration bigint, [dbid] int, objectid bigint, execution_count bigint, query_hash varbinary(8)) CREATE TABLE #QueryText (query_hash varbinary(8), query_text varchar(max)) INSERT INTO #TopOffenders (AvgIO, TotalIO, TotalCPU, AvgCPU, TotalDuration, AvgDuration, [dbid], objectid, execution_count, query_hash) SELECT TOP (@NumOfStatements) SUM((qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count) as [Avg IO], SUM((qs.total_logical_reads + qs.total_logical_writes)) AS [TotalIO], SUM(qs.total_worker_time) AS [TotalCPU], SUM((qs.total_worker_time) / qs.execution_count) AS [AvgCPU], SUM(qs.total_elapsed_time) AS TotalDuration, SUM((qs.total_elapsed_time)/ qs.execution_count) AS AvgDuration, qt.dbid, qt.objectid, SUM(qs.execution_count) AS Execution_Count, qs.query_hash FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt GROUP BY qs.query_hash, qs.query_plan_hash, qt.dbid, qt.objectid HAVING SUM(qs.execution_count) > @Executions ORDER BY [TotalCPU] DESC --select * From #TopOffenders --ORDER BY TotalIO desc /* Create cursor to get query text */ DECLARE @QueryHash varbinary(8) DECLARE QueryCursor CURSOR FAST_FORWARD FOR select query_hash FROM #TopOffenders OPEN QueryCursor FETCH NEXT FROM QueryCursor INTO @QueryHash WHILE (@@FETCH_STATUS = 0) BEGIN INSERT INTO #QueryText (query_text, query_hash) select MIN(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)) as query_text, qs.query_hash from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt where qs.query_hash = @QueryHash GROUP BY qs.query_hash; FETCH NEXT FROM QueryCursor INTO @QueryHash END CLOSE QueryCursor DEALLOCATE QueryCursor select distinct DB_NAME(dbid) DBName, OBJECT_NAME(objectid, dbid) ObjectName, qt.query_text, o.* INTO #Results from #TopOffenders o join #QueryText qt on (o.query_hash = qt.query_hash) SELECT TOP (@NumOfStatements) * FROM #Results ORDER BY TotalCPU desc DROP TABLE #Results DROP TABLE #TopOffenders DROP TABLE #QueryText END GO
Total Duration
/****** Object: StoredProcedure [dbo].[GetTopStatements_TotalDuration] Script Date: 10/14/2013 10:18:49 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: John Sterrett (@JohnSterrett) -- Create date: 6/4/2013 -- Description: Get total duration from cache based on executions. -- Example: exec dbo.GetTopStatements_TotalDuration @NumOfStatements = 25, @Executions = 5 -- ============================================= CREATE PROCEDURE [dbo].[GetTopStatements_TotalDuration] -- Add the parameters for the stored procedure here @NumOfStatements int = 25, @Executions int = 5 AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here --- top 25 statements by IO IF OBJECT_ID('tempdb..#TopOffenders') IS NOT NULL DROP TABLE #TopOffenders IF OBJECT_ID('tempdb..#QueryText') IS NOT NULL DROP TABLE #QueryText CREATE TABLE #TopOffenders (AvgIO bigint, TotalIO bigint, TotalCPU bigint, AvgCPU bigint, TotalDuration bigint, AvgDuration bigint, [dbid] int, objectid bigint, execution_count bigint, query_hash varbinary(8)) CREATE TABLE #QueryText (query_hash varbinary(8), query_text varchar(max)) INSERT INTO #TopOffenders (AvgIO, TotalIO, TotalCPU, AvgCPU, TotalDuration, AvgDuration, [dbid], objectid, execution_count, query_hash) SELECT TOP (@NumOfStatements) SUM((qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count) as [Avg IO], SUM((qs.total_logical_reads + qs.total_logical_writes)) AS [TotalIO], SUM(qs.total_worker_time) AS Total_Worker_Time, SUM((qs.total_worker_time) / qs.execution_count) AS [AvgCPU], SUM(qs.total_elapsed_time) AS TotalDuration, SUM((qs.total_elapsed_time)/ qs.execution_count) AS AvgDuration, qt.dbid, qt.objectid, SUM(qs.execution_count) AS Execution_Count, qs.query_hash FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt GROUP BY qs.query_hash, qs.query_plan_hash, qt.dbid, qt.objectid HAVING SUM(qs.execution_count) > @Executions ORDER BY [TotalDuration] DESC --select * From #TopOffenders --ORDER BY TotalIO desc /* Create cursor to get query text */ DECLARE @QueryHash varbinary(8) DECLARE QueryCursor CURSOR FAST_FORWARD FOR select query_hash FROM #TopOffenders OPEN QueryCursor FETCH NEXT FROM QueryCursor INTO @QueryHash WHILE (@@FETCH_STATUS = 0) BEGIN INSERT INTO #QueryText (query_text, query_hash) select MIN(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)) as query_text, qs.query_hash from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt where qs.query_hash = @QueryHash GROUP BY qs.query_hash; FETCH NEXT FROM QueryCursor INTO @QueryHash END CLOSE QueryCursor DEALLOCATE QueryCursor select distinct DB_NAME(dbid) DBName, OBJECT_NAME(objectid, dbid) ObjectName, qt.query_text, o.* INTO #Results from #TopOffenders o join #QueryText qt on (o.query_hash = qt.query_hash) SELECT TOP (@NumOfStatements) * FROM #Results ORDER BY TotalDuration desc DROP TABLE #Results DROP TABLE #TopOffenders DROP TABLE #QueryText END GO
Average I/O
/****** Object: StoredProcedure [dbo].[GetTopStatements_AvgIO] Script Date: 10/14/2013 10:23:01 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: John Sterrett (@JohnSterrett) -- Create date: 6/4/2013 -- Description: Get statements from cache causing most average IO based on executions. -- Example: exec dbo.GetTopStatements_AvgIO @Executions = 5, @NumOfStatements = 25 -- ============================================= CREATE PROCEDURE [dbo].[GetTopStatements_AvgIO] -- Add the parameters for the stored procedure here @NumOfStatements int = 25, @Executions int = 5 AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here IF OBJECT_ID('tempdb..#TopOffenders') IS NOT NULL DROP TABLE #TopOffenders IF OBJECT_ID('tempdb..#QueryText') IS NOT NULL DROP TABLE #QueryText CREATE TABLE #TopOffenders (AvgIO bigint, TotalIO bigint, TotalCPU bigint, AvgCPU bigint, TotalDuration bigint, AvgDuration bigint, [dbid] int, objectid bigint, execution_count bigint, query_hash varbinary(8)) CREATE TABLE #QueryText (query_hash varbinary(8), query_text varchar(max)) INSERT INTO #TopOffenders (AvgIO, TotalIO, TotalCPU, AvgCPU, TotalDuration, AvgDuration, [dbid], objectid, execution_count, query_hash) SELECT TOP (@NumOfStatements) SUM((qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count) as [Avg IO], SUM((qs.total_logical_reads + qs.total_logical_writes)) AS [TotalIO], SUM(qs.total_worker_time) AS [TotalCPU], SUM((qs.total_worker_time) / qs.execution_count) AS [AvgCPU], SUM(qs.total_elapsed_time) AS TotalDuration, SUM((qs.total_elapsed_time)/ qs.execution_count) AS AvgDuration, qt.dbid, qt.objectid, SUM(qs.execution_count) AS Execution_Count, qs.query_hash FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt GROUP BY qs.query_hash, qs.query_plan_hash, qt.dbid, qt.objectid HAVING SUM(qs.execution_count) > @Executions ORDER BY [Avg IO] DESC --select * From #TopOffenders --ORDER BY AvgIO desc /* Create cursor to get query text */ DECLARE @QueryHash varbinary(8) DECLARE QueryCursor CURSOR FAST_FORWARD FOR select query_hash FROM #TopOffenders OPEN QueryCursor FETCH NEXT FROM QueryCursor INTO @QueryHash WHILE (@@FETCH_STATUS = 0) BEGIN INSERT INTO #QueryText (query_text, query_hash) select MIN(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)) as query_text, qs.query_hash from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt where qs.query_hash = @QueryHash GROUP BY qs.query_hash; FETCH NEXT FROM QueryCursor INTO @QueryHash END CLOSE QueryCursor DEALLOCATE QueryCursor select distinct DB_NAME(dbid) DBName, OBJECT_NAME(objectid, dbid) ObjectName, qt.query_text, o.* INTO #Results from #TopOffenders o join #QueryText qt on (o.query_hash = qt.query_hash) SELECT TOP (@NumOfStatements) * FROM #Results ORDER BY AvgIO desc DROP TABLE #Results DROP TABLE #TopOffenders DROP TABLE #QueryText END GO
Average CPU
/****** Object: StoredProcedure [dbo].[GetTopStatements_AvgCPU] Script Date: 10/14/2013 10:31:47 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: John Sterrett (@JohnSterrett) -- Create date: 6/4/2013 -- Description: Get Statements from cache that have highest average cpu utilization by executions -- Example: exec dbo.GetTopStatements_AvgCPU @Executions = 5, @NumbOfStatements = 25 -- ============================================= CREATE PROCEDURE [dbo].[GetTopStatements_AvgCPU] -- Add the parameters for the stored procedure here @NumOfStatements int = 25, @Executions int = 5 AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here --- top 25 statements by IO IF OBJECT_ID('tempdb..#TopOffenders') IS NOT NULL DROP TABLE #TopOffenders IF OBJECT_ID('tempdb..#QueryText') IS NOT NULL DROP TABLE #QueryText CREATE TABLE #TopOffenders (AvgIO bigint, TotalIO bigint, TotalCPU bigint, AvgCPU bigint, TotalDuration bigint, AvgDuration bigint, [dbid] int, objectid bigint, execution_count bigint, query_hash varbinary(8)) CREATE TABLE #QueryText (query_hash varbinary(8), query_text varchar(max)) INSERT INTO #TopOffenders (AvgIO, TotalIO, TotalCPU, AvgCPU, TotalDuration, AvgDuration, [dbid], objectid, execution_count, query_hash) SELECT TOP (@NumOfStatements) SUM((qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count) as [Avg IO], SUM((qs.total_logical_reads + qs.total_logical_writes)) AS [TotalIO], SUM(qs.total_worker_time) AS Total_Worker_Time, SUM((qs.total_worker_time) / qs.execution_count) AS [AvgCPU], SUM(qs.total_elapsed_time) AS TotalDuration, SUM((qs.total_elapsed_time)/ qs.execution_count) AS AvgDuration, qt.dbid, qt.objectid, SUM(qs.execution_count) AS Execution_Count, qs.query_hash FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt GROUP BY qs.query_hash, qs.query_plan_hash, qt.dbid, qt.objectid HAVING SUM(qs.execution_count) > @Executions ORDER BY [AvgCPU] DESC --select * From #TopOffenders --ORDER BY TotalIO desc /* Create cursor to get query text */ DECLARE @QueryHash varbinary(8) DECLARE QueryCursor CURSOR FAST_FORWARD FOR select query_hash FROM #TopOffenders OPEN QueryCursor FETCH NEXT FROM QueryCursor INTO @QueryHash WHILE (@@FETCH_STATUS = 0) BEGIN INSERT INTO #QueryText (query_text, query_hash) select MIN(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)) as query_text, qs.query_hash from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt where qs.query_hash = @QueryHash GROUP BY qs.query_hash; FETCH NEXT FROM QueryCursor INTO @QueryHash END CLOSE QueryCursor DEALLOCATE QueryCursor select distinct DB_NAME(dbid) DBName, OBJECT_NAME(objectid, dbid) ObjectName, qt.query_text, o.* INTO #Results from #TopOffenders o join #QueryText qt on (o.query_hash = qt.query_hash) SELECT TOP (@NumOfStatements) * FROM #Results ORDER BY AvgCPU desc DROP TABLE #Results DROP TABLE #TopOffenders DROP TABLE #QueryText END GO
Average Duration
/****** Object: StoredProcedure [dbo].[GetTopStatements_AvgDuration] Script Date: 10/14/2013 10:30:17 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: John Sterrett (@JohnSterrett) -- Create date: 6/4/2013 -- Description: Get statements from cache causing most average duration by executions -- Example: exec dbo.GetTopStatements_AvgDuration @NumOfStatements = 25, @Executions = 5 -- ============================================= CREATE PROCEDURE [dbo].[GetTopStatements_AvgDuration] -- Add the parameters for the stored procedure here @NumOfStatements int = 25, @Executions int = 5 AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here --- top 25 statements by IO IF OBJECT_ID('tempdb..#TopOffenders') IS NOT NULL DROP TABLE #TopOffenders IF OBJECT_ID('tempdb..#QueryText') IS NOT NULL DROP TABLE #QueryText CREATE TABLE #TopOffenders (AvgIO bigint, TotalIO bigint, TotalCPU bigint, AvgCPU bigint, TotalDuration bigint, AvgDuration bigint, [dbid] int, objectid bigint, execution_count bigint, query_hash varbinary(8)) CREATE TABLE #QueryText (query_hash varbinary(8), query_text varchar(max)) INSERT INTO #TopOffenders (AvgIO, TotalIO, TotalCPU, AvgCPU, TotalDuration, AvgDuration, [dbid], objectid, execution_count, query_hash) SELECT TOP (@NumOfStatements) SUM((qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count) as [Avg IO], SUM((qs.total_logical_reads + qs.total_logical_writes)) AS [TotalIO], SUM(qs.total_worker_time) AS Total_Worker_Time, SUM((qs.total_worker_time) / qs.execution_count) AS [AvgCPU], SUM(qs.total_elapsed_time) AS TotalDuration, SUM((qs.total_elapsed_time)/ qs.execution_count) AS AvgDuration, qt.dbid, qt.objectid, SUM(qs.execution_count) AS Execution_Count, qs.query_hash FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt GROUP BY qs.query_hash, qs.query_plan_hash, qt.dbid, qt.objectid HAVING SUM(qs.execution_count) > @Executions ORDER BY [AvgDuration] DESC --select * From #TopOffenders --ORDER BY TotalIO desc /* Create cursor to get query text */ DECLARE @QueryHash varbinary(8) DECLARE QueryCursor CURSOR FAST_FORWARD FOR select query_hash FROM #TopOffenders OPEN QueryCursor FETCH NEXT FROM QueryCursor INTO @QueryHash WHILE (@@FETCH_STATUS = 0) BEGIN INSERT INTO #QueryText (query_text, query_hash) select MIN(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)) as query_text, qs.query_hash from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt where qs.query_hash = @QueryHash GROUP BY qs.query_hash; FETCH NEXT FROM QueryCursor INTO @QueryHash END CLOSE QueryCursor DEALLOCATE QueryCursor select distinct DB_NAME(dbid) DBName, OBJECT_NAME(objectid, dbid) ObjectName, qt.query_text, o.* INTO #Results from #TopOffenders o join #QueryText qt on (o.query_hash = qt.query_hash) SELECT TOP (@NumOfStatements) * FROM #Results ORDER BY AvgDuration desc DROP TABLE #Results DROP TABLE #TopOffenders DROP TABLE #QueryText END GO
If you enjoyed this blog post please check out my related blog posts like benchmarking your top waits and finding queries that cause your top waits or what is running and benchmarking disk latency.