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.