Blog Post

Finding Top Offenders From Cache

,

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.

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Share

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Related content

Technical Article

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2009-02-23

1,567 reads

Technical Article

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2009-02-17

1,530 reads

Technical Article

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2009-02-13

360 reads