Tag Archives: performance tuning

SQL Server Performance Root Cause Analysis in 10 Minutes

This year I was honored to be selected by Dell Software to present a ten minute session in their booth (#200) at the 2013 SQL PASS Member Summit. I decided to share how I do a SQL Server Performance Root Cause Analysis in 10 minutes with the SQL Community.

The following is my blog series and it includes all the sample code:

If you are attending the 2013 SQL PASS Member Summit lets connect. You can catch my presentation in the Dell Software Theater at Booth #200 at the times listed below.

Dates and Times:

  • Wednesday – October 16th @ 11:45am
  • Thursday – October 17th @ 1:45pm
  • Thursday – October 17th @ 3:15pm
  • Friday – October 18th @ 12:45pm

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.

What Queries are Causing My Waits?

In my last blog post, I showed you how I go about baselining wait statistics. Typically my next step once I have found my top wait types is to use extended events to figure out the SQL statements causing the majority of the waits for those wait types. This is actually my favorite example of using extended events as it clearly shows you something that can be done that wasn’t possible with a SQL Server server side trace.

** Download Scripts Here **

So, looking at the results of benchmarking my wait stats I noticed that my workload had several waits for SOS_SCHEDULER_YIELD and PAGEIOLATCH_EX. I could then run the following code below to figure out which statement(s) caused the majority of these waits as shown below and tune them to reduce my waits.

QueryCauseWaits

NOTE: Waits schema is required. It is created in the Baseline Wait Statistics sample code.

Capture Data with Extended Events

Regardless if you are using SQL 2008 or SQL Server 2012 the statement used create your extended event session to capture your SQL statements causing your top wait types is the same. In this extended event we will capture data into memory using 20 MB. This and the max_dispatch_latency can be configured with specifying the parameters into the stored procedure.

IF NOT EXISTS (SELECT 1 FROM sys.table_types where name like 'WaitType')
BEGIN
	CREATE TYPE WaitType AS TABLE 
	( Name VARCHAR(200))
END

/****** Object:  StoredProcedure [Waits].[CaptureStatementsCausingWaits]    Script Date: 10/7/2013 10:28:24 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		@JohnSterrett
-- Create date: 9/23/2013 
-- Description:	Gets SQL Statements causing top wait types.
-- 
-- =============================================
CREATE PROCEDURE [Waits].[CaptureStatementsCausingWaits] 
	-- Add the parameters for the stored procedure here
	@TVP WaitType READONLY, -- Table Value Parameter with Wait Types
	@Duration int = 10, 
	@StartXEvent bit = 1,
	@CreateXEvent bit = 1,
	@StopXevent bit = 0,
	@max_memory bigint = 20480,
	@max_dispatch_latency int =5

-- Sample Executions :

--**** What wait types are causing PAGEIOLATCH_EX, SOS_SCHEDULER_YIELD, 'PAGEIOLATCH_SH waits ****
/*
  DECLARE @WaitTypeTVP AS WaitType;
  INSERT INTO @WaitTypeTVP (Name)
  VALUES ('PAGEIOLATCH_EX'), ('SOS_SCHEDULER_YIELD'),('PAGEIOLATCH_SH')
  EXEC Waits.CaptureStatementsCausingWaits @TVP = @WaitTypeTVP;
  GO
*/

--*** Get wait details. XEvent is in memory so must still be running ****
--exec  [Waits].[GetStatementsCausingWaits]

--*** Stop Xevent from capturing (NOTE: Once this is done you will loose collected data) ***
--exec Waits.CaptureStatementsCausingWaits @StopXevent = 1

--/********* TODO: ***********************
--	Add checks for valid parameters
--	Verify that WaitTypes exist */
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
		SET NOCOUNT ON;
		IF @StopXevent = 1
		BEGIN
			IF EXISTS ( SELECT  *
						FROM    sys.server_event_sessions
						WHERE   name = 'TrackResourceWaits' ) 
					ALTER EVENT SESSION TrackResourceWaits ON SERVER STATE = STOP;
		END
		ELSE BEGIN
		-- Insert statements for procedure here
			DECLARE @SQLStmt nvarchar(max)

			IF OBJECT_ID('tempdb..##TmpWaitTypes') IS NOT NULL
				DROP TABLE ##TmpWaitTypes 

			SELECT map_key, map_value
			INTO ##TmpWaitTypes 
			FROM sys.dm_xe_map_values xmv
			JOIN @TVP tvp ON (tvp.name = xmv.map_value)
			WHERE xmv.name = 'wait_types'

			SELECT * FROM ##TmpWaitTypes 

			/* Step 3: Create XEvent to capture queries causing waits. Must update from step 2
			Use script to find top waits and then use this script to get statements causing those waits */
			IF @CreateXEvent = 1
			BEGIN
					DECLARE curWaitTypes CURSOR LOCAL FAST_FORWARD FOR
					SELECT map_key FROM ##TmpWaitTypes

					OPEN curWaitTypes
					DECLARE @map_key bigint, @SmallSQL nvarchar(max)
					SET @SmallSQL = ''

					FETCH NEXT FROM curWaitTypes
					INTO @map_key

					WHILE @@FETCH_STATUS = 0
					BEGIN
						SET @SmallSQL += 'wait_type = '+CAST(@map_key AS VARCHAR(50)) +' OR '

						FETCH NEXT FROM curWaitTypes
						INTO @map_key
					END
					CLOSE curWaitTypes;
					DEALLOCATE curWaitTypes;

					/* Remove the last comma */
					SET @SmallSQL = LEFT(@SmallSQL, LEN(@SmallSQL) - 3)
					PRINT @SmallSQL
					--AND (wait_type IN (50, 51, 124)
					IF EXISTS ( SELECT  *
								FROM    sys.server_event_sessions
								WHERE   name = 'TrackResourceWaits' ) 
						DROP EVENT SESSION TrackResourceWaits ON SERVER

					SET @SQLStmt = 'CREATE EVENT SESSION [TrackResourceWaits] ON SERVER 
					ADD EVENT  sqlos.wait_info
					(    -- Capture the database_id, session_id, plan_handle, and sql_text
						ACTION(sqlserver.database_id,sqlserver.username, sqlserver.session_id,sqlserver.sql_text,sqlserver.plan_handle, sqlserver.tsql_stack)
						WHERE
							(opcode = 1 --End Events Only
								AND duration > ' +CAST(@Duration AS VARCHAR(10)) +'
								AND (' +@SmallSQL+ ')

							)
					)
					ADD TARGET package0.ring_buffer(SET max_memory= '+CAST(@max_memory AS varchar(200))+')
					WITH (EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
						  MAX_DISPATCH_LATENCY= '+CAST(@max_dispatch_latency as varchar(10))+' SECONDS)'

					PRINT @SQLStmt
					EXEC(@SQLStmt)

					/* Cleanup tasks */
					IF OBJECT_ID('tempdb..##TmpWaitTypes') IS NOT NULL
						DROP TABLE ##TmpWaitTypes 
				END

			IF @StartXEvent = 1
				ALTER EVENT SESSION TrackResourceWaits ON SERVER STATE = START;
			/* Step 4a: Start workload and wait */
		END
END

Reading Captured Data with SQL Server 2008

Now that we have our extended event running into memory we will need complete our analysis before we stop the extended event.  The following stored procedure can be used to pull the statements causing your top waits.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [Waits].[GetStatementsCausingWaits_2008]
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
			/* Step 4b: Query the waits */
		IF OBJECT_ID('tempdb..#XWaits') IS NOT NULL
			DROP TABLE #XWaits

		SELECT 
			event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
			DATEADD(hh, 
				DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), 
				event_data.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp],
			COALESCE(event_data.value('(event/data[@name="database_id"]/value)[1]', 'int'), 
				event_data.value('(event/action[@name="database_id"]/value)[1]', 'int')) AS database_id,
			event_data.value('(event/action[@name="session_id"]/value)[1]', 'int') AS [session_id],
			event_data.value('(event/data[@name="wait_type"]/text)[1]', 'nvarchar(4000)') AS [wait_type],
			event_data.value('(event/data[@name="opcode"]/text)[1]', 'nvarchar(4000)') AS [opcode],
			event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') AS [duration],
			event_data.value('(event/data[@name="max_duration"]/value)[1]', 'bigint') AS [max_duration],
			event_data.value('(event/data[@name="total_duration"]/value)[1]', 'bigint') AS [total_duration],
			event_data.value('(event/data[@name="signal_duration"]/value)[1]', 'bigint') AS [signal_duration],
			event_data.value('(event/data[@name="completed_count"]/value)[1]', 'bigint') AS [completed_count],
			event_data.value('(event/action[@name="plan_handle"]/value)[1]', 'nvarchar(4000)') AS [plan_handle],
			event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(4000)') AS [sql_text],
			event_data.value('(event/action[@name="tsql_stack"]/value)[1]', 'nvarchar(4000)') AS [tsql_stack]
			INTO #XWaits
		FROM 
		(    SELECT XEvent.query('.') AS event_data 
			FROM 
			(    -- Cast the target_data to XML 
				SELECT CAST(target_data AS XML) AS TargetData 
				FROM sys.dm_xe_session_targets st 
				JOIN sys.dm_xe_sessions s 
					ON s.address = st.event_session_address 
				WHERE name = 'TrackResourceWaits' 
					AND target_name = 'ring_buffer'
			) AS Data 
			-- Split out the Event Nodes 
			CROSS APPLY TargetData.nodes ('RingBufferTarget/event') AS XEventData (XEvent)   
		) AS tab (event_data)

		-- Need to get tsql_stack as XML to manipulate --
		ALTER TABLE #XWaits ADD tsql_stack2 XML
		UPDATE #XWaits SET tsql_stack2 = '<Root>' + tsql_stack + '</Root>'

		-- Duration by Wait
		SELECT wait_Type, COUNT(*) AS NumOfWaitsGT10ms, SUM(duration) AS TotalDurationMS
		FROM #XWaits
		GROUP BY wait_type
		ORDER BY TotalDurationMS DESC

		-- Get wait's for block of t-sql code

		IF OBJECT_ID('tempdb..#TempData') IS NOT NULL
			DROP TABLE #TempData;

		WITH XWaitsCTE (database_id, session_id, wait_type, duration, plan_handle, tsql_stack, handle, offsetStart, offsetEnd)
		AS
		(
		SELECT database_id, session_id, wait_type, duration, plan_handle, tsql_stack --,tsql_stack2
			,tsql_stack2.value('(Root/frame/@handle)[1]', 'varchar(2000)') as handle
			--,CONVERT(tsql_stack2.value('(Root/frame/@handle)[1]', 'nvarchar(2000)') as handle2
			,tsql_stack2.value('(Root/frame/@offsetStart)[1]', 'varchar(4000)') as offsetStart
			,tsql_stack2.value('(Root/frame/@offsetEnd)[1]', 'varchar(4000)') as offsetEnd
		FROM	#XWaits 
		)
		SELECT 	wait_type, COUNT(*) AS NumOfWaitsGT10ms, SUM(duration) AS TotalDurationMS, handle, offsetStart, offsetEnd, database_id
		INTO #TempData
		FROM XWaitsCTE
		GROUP BY wait_type, handle, offsetStart, offsetEnd, database_id;

		--SELECT *, DB_NAME(database_id) 
		--FROM #TempData 
		--ORDER BY TotalDurationMS DESC;

		/* Top statements causing wait types 
		SELECT TOP 20 SUM(TotalDurationMS) TotalDurationMS, handle,  offsetStart, offsetEnd, DB_NAME(database_id) 
		FROM #TempData
		GROUP BY handle, offsetEnd, offsetStart, database_id
		ORDER BY 1 desc; */

		/* Look at statement causing wait times 
			Substitue handle, offsetStart and offsetEnd from query above 
		*/

		/*** Get statement causing waits ****/
		IF OBJECT_ID('tempdb..#SQLStatement') IS NOT NULL
			DROP TABLE #SQLStatement;

		CREATE TABLE #SQLStatement (handleText varchar(4000), offsetStart varchar(1000), offsetEnd varchar(1000), 
					sql_statement xml, ObjectName varchar(2000), objectid bigint, databaseid int, encrypted bit) 

		DECLARE WaitStatement CURSOR LOCAL FAST_FORWARD FOR
		SELECT TOP 20 SUM(TotalDurationMS) AS TotalDurationMS, handle,  offsetStart, offsetEnd, DB_NAME(database_id) 
		FROM #TempData
		GROUP BY handle, offsetEnd, offsetStart, database_id
		ORDER BY 1 desc;

		OPEN WaitStatement 
		DECLARE @TotalDurationMS bigint, @handle varchar(2000), @offsetStart varchar(100), @offsetEnd varchar(100), @databaseName varchar(4000), @SQLStmt nvarchar(max)

		FETCH NEXT FROM WaitStatement
		INTO @TotalDurationMS, @handle, @offsetStart, @offsetEnd, @databaseName

		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @SQLStmt = 'USE [' + @databaseName+'] '+CHAR(10)

			SET @SQLStmt = @SQLStmt + ' declare @offsetStart bigint, @offsetEnd bigint, @handle varbinary(64), @handleText varchar(4000) '
			SET @SQLStmt = @SQLStmt + ' select @offsetStart = '+@offsetStart +', @offsetEnd = '+case when @offsetEnd like '-1' then '2147483647' else @offsetEnd end+', @handle = '+@handle+', @handleText = '''+@handle+''''

			SET @SQLStmt = @SQLStmt + CHAR(10)+ ' INSERT INTO #SQLStatement (sql_statement, ObjectName, objectid, databaseid, encrypted, offsetStart, offsetEnd, handleText) '
			SET @SQLStmt = @SQLStmt + CHAR(10)+ ' select CAST(''<?query --''+CHAR(13)+SUBSTRING(qt.text, (@offsetStart/ 2)+1, 
			(( @offsetEnd - @offsetStart)/2) + 1)+CHAR(13)+''--?>'' AS xml) as sql_statement '
			SET @SQLStmt = @SQLStmt + CHAR(10)+ ' , OBJECT_NAME(qt.objectid) OBJNAME
			,qt.objectid,qt.dbid, qt.encrypted, @offsetStart as offsetStart, @offsetEnd as offsetEnd, @handleText as handleText '
			SET @SQLStmt = @SQLStmt + CHAR(10)+ '     from sys.dm_exec_sql_text(@handle) qt '

			--PRINT @SQLStmt
			EXEC (@SQLStmt)

			FETCH NEXT FROM WaitStatement
			INTO @TotalDurationMS, @handle, @offsetStart, @offsetEnd, @databaseName
		END
		CLOSE WaitStatement;
		DEALLOCATE WaitStatement;

		/*** GET THE SQL GOOD STUFF *******************************************/

		WITH cte_SQLStatement (TotalDurationMS, handle, offsetStart, offsetEnd, databaseName)
		AS
		(	
			SELECT TOP 20 SUM(TotalDurationMS) TotalDurationMS, handle,  offsetStart, offsetEnd, DB_NAME(database_id) 
			FROM #TempData
			GROUP BY handle, offsetEnd, offsetStart, database_id
			ORDER BY 1 desc
		)
		SELECT TotalDurationMS, ts.sql_statement, ts.ObjectName, td.handle,  td.offsetStart, td.offsetEnd, td.databaseName 
		FROM cte_SQLStatement td
		LEFT JOIN #SQLStatement ts ON (td.handle = ts.handleText AND td.offsetStart = ts.offsetStart)
		ORDER BY TotalDurationMS desc;
END

GO

Reading Captured Data with SQL Server 2012

The following is a similar stored procedure customized to work with SQL Server 2012.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [Waits].[GetStatementsCausingWaits_2012]
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
			/* Step 4b: Query the waits */
		IF OBJECT_ID('tempdb..#XWaits') IS NOT NULL
			DROP TABLE #XWaits

		SELECT 
			event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
			DATEADD(hh, 
				DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), 
				event_data.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp],
			COALESCE(event_data.value('(event/data[@name="database_id"]/value)[1]', 'int'), 
				event_data.value('(event/action[@name="database_id"]/value)[1]', 'int')) AS database_id,
			event_data.value('(event/action[@name="session_id"]/value)[1]', 'int') AS [session_id],
			event_data.value('(event/data[@name="wait_type"]/text)[1]', 'nvarchar(4000)') AS [wait_type],
			event_data.value('(event/data[@name="opcode"]/text)[1]', 'nvarchar(4000)') AS [opcode],
			event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') AS [duration],
			event_data.value('(event/data[@name="max_duration"]/value)[1]', 'bigint') AS [max_duration],
			event_data.value('(event/data[@name="total_duration"]/value)[1]', 'bigint') AS [total_duration],
			event_data.value('(event/data[@name="signal_duration"]/value)[1]', 'bigint') AS [signal_duration],
			event_data.value('(event/data[@name="completed_count"]/value)[1]', 'bigint') AS [completed_count],
			event_data.value('(event/action[@name="plan_handle"]/value)[1]', 'nvarchar(4000)') AS [plan_handle],
			event_data.query('(event/action[@name="tsql_stack"]/value)[1]') AS tsql_stack,
			event_data.query('(event/action[@name="tsql_frame"]/value)[1]') AS tsql_frame
			INTO #XWaits
		FROM 
		(    SELECT XEvent.query('.') AS event_data 
			FROM 
			(    -- Cast the target_data to XML 
				SELECT CAST(target_data AS XML) AS TargetData 
				FROM sys.dm_xe_session_targets st 
				JOIN sys.dm_xe_sessions s 
					ON s.address = st.event_session_address 
				WHERE name = 'TrackResourceWaits' 
					AND target_name = 'ring_buffer'
			) AS Data 
			-- Split out the Event Nodes 
			CROSS APPLY TargetData.nodes ('RingBufferTarget/event') AS XEventData (XEvent)   
		) AS tab (event_data)

		-- Need to get tsql_stack as XML to manipulate --
		/**** Required for SQL 2008 ****
		ALTER TABLE #XWaits ADD tsql_stack2 XML
		UPDATE #XWaits SET tsql_stack2 = '<Root>' + tsql_stack + '</Root>' */

		-- Duration by Wait
		SELECT wait_Type, COUNT(*) AS NumOfWaitsGT10ms, SUM(duration) AS TotalDurationMS
		FROM #XWaits
		GROUP BY wait_type
		ORDER BY TotalDurationMS DESC

		-- Get wait's for block of t-sql code

		IF OBJECT_ID('tempdb..#TempData') IS NOT NULL
			DROP TABLE #TempData;

		WITH XWaitsCTE (database_id, session_id, wait_type, duration, plan_handle, tsql_stack, handle, offsetStart, offsetEnd)
		AS
		(
		SELECT database_id, session_id, wait_type, duration, plan_handle, tsql_stack --,tsql_stack2
			,pref.value('(/value/frames/frame/@handle)[1]', 'varchar(2000)') as handle
			,pref.value('(/value/frames/frame/@offsetStart)[1]', 'varchar(4000)') as offsetStart
			,pref.value('(/value/frames/frame/@offsetEnd)[1]', 'varchar(200)') as offsetEnd
	--,SQLTEXT.text
		FROM	#XWaits CROSS APPLY 
				tsql_stack.nodes('/value/frames') AS People(pref)
		)
		SELECT 	wait_type, COUNT(*) AS NumOfWaitsGT10ms, SUM(duration) AS TotalDurationMS, handle, offsetStart, offsetEnd, database_id
		INTO #TempData
		FROM XWaitsCTE
		GROUP BY wait_type, handle, offsetStart, offsetEnd, database_id;

		--SELECT *, DB_NAME(database_id) 
		--FROM #TempData 
		--ORDER BY TotalDurationMS DESC;

		/* Top statements causing wait types 
		SELECT TOP 20 SUM(TotalDurationMS) TotalDurationMS, handle,  offsetStart, offsetEnd, DB_NAME(database_id) 
		FROM #TempData
		GROUP BY handle, offsetEnd, offsetStart, database_id
		ORDER BY 1 desc; */

		/* Look at statement causing wait times 
			Substitue handle, offsetStart and offsetEnd from query above 
		*/

		/*** Get statement causing waits ****/
		IF OBJECT_ID('tempdb..#SQLStatement') IS NOT NULL
			DROP TABLE #SQLStatement;

		CREATE TABLE #SQLStatement (handleText varchar(4000), offsetStart varchar(1000), offsetEnd varchar(1000), 
					sql_statement xml, ObjectName varchar(2000), objectid bigint, databaseid int, encrypted bit) 

		DECLARE WaitStatement CURSOR LOCAL FAST_FORWARD FOR
		SELECT TOP 20 SUM(TotalDurationMS) AS TotalDurationMS, handle,  offsetStart, offsetEnd, DB_NAME(database_id) 
		FROM #TempData
		GROUP BY handle, offsetEnd, offsetStart, database_id
		ORDER BY 1 desc;

		OPEN WaitStatement 
		DECLARE @TotalDurationMS bigint, @handle varchar(2000), @offsetStart varchar(100), @offsetEnd varchar(100), @databaseName varchar(4000), @SQLStmt nvarchar(max)

		FETCH NEXT FROM WaitStatement
		INTO @TotalDurationMS, @handle, @offsetStart, @offsetEnd, @databaseName

		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @SQLStmt = 'USE [' + @databaseName+'] '+CHAR(10)

			SET @SQLStmt = @SQLStmt + ' declare @offsetStart bigint, @offsetEnd bigint, @handle varbinary(64), @handleText varchar(4000) '
			SET @SQLStmt = @SQLStmt + ' select @offsetStart = '+@offsetStart +', @offsetEnd = '+case when @offsetEnd like '-1' then '2147483647' else @offsetEnd end+', @handle = '+@handle+', @handleText = '''+@handle+''''

			SET @SQLStmt = @SQLStmt + CHAR(10)+ ' INSERT INTO #SQLStatement (sql_statement, ObjectName, objectid, databaseid, encrypted, offsetStart, offsetEnd, handleText) '
			SET @SQLStmt = @SQLStmt + CHAR(10)+ ' select CAST(''<?query --''+CHAR(13)+SUBSTRING(qt.text, (@offsetStart/ 2)+1, 
			(( @offsetEnd - @offsetStart)/2) + 1)+CHAR(13)+''--?>'' AS xml) as sql_statement '
			SET @SQLStmt = @SQLStmt + CHAR(10)+ ' , OBJECT_NAME(qt.objectid) OBJNAME
			,qt.objectid,qt.dbid, qt.encrypted, @offsetStart as offsetStart, @offsetEnd as offsetEnd, @handleText as handleText '
			SET @SQLStmt = @SQLStmt + CHAR(10)+ '     from sys.dm_exec_sql_text(@handle) qt '

			--PRINT @SQLStmt
			EXEC (@SQLStmt)

			FETCH NEXT FROM WaitStatement
			INTO @TotalDurationMS, @handle, @offsetStart, @offsetEnd, @databaseName
		END
		CLOSE WaitStatement;
		DEALLOCATE WaitStatement;

		/*** GET THE SQL GOOD STUFF *******************************************/

		WITH cte_SQLStatement (TotalDurationMS, handle, offsetStart, offsetEnd, databaseName)
		AS
		(	
			SELECT TOP 20 SUM(TotalDurationMS) TotalDurationMS, handle,  offsetStart, offsetEnd, DB_NAME(database_id) 
			FROM #TempData
			GROUP BY handle, offsetEnd, offsetStart, database_id
			ORDER BY 1 desc
		)
		SELECT TotalDurationMS, ts.sql_statement, ts.ObjectName, td.handle,  td.offsetStart, td.offsetEnd, td.databaseName 
		FROM cte_SQLStatement td
		LEFT JOIN #SQLStatement ts ON (td.handle = ts.handleText AND td.offsetStart = ts.offsetStart)
		ORDER BY TotalDurationMS desc;
END

Making Get Statements Causing Waits Easy

The following stored procedure makes getting our statements easy. It basically does a quick check to see if your using SQL 2012 or SQL 2008 and executes the correct stored procedure.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [Waits].[GetStatementsCausingWaits]
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @VersionNumber VARCHAR(100), @VersionInt INT
	SELECT @VersionNumber = CAST(SERVERPROPERTY(N'productversion') AS VARCHAR(100))
	SELECT @VersionInt = CAST(SUBSTRING(@VersionNumber, 1, CHARINDEX('.', @VersionNumber)-1) AS INT)

	IF @VersionInt = 11 
	BEGIN 
		EXEC Waits.GetStatementsCausingWaits_2012
	END
	ELSE IF @VersionInt = 10
	BEGIN
		EXEC Waits.GetStatementsCausingWaits_2008
	END --end ifs
END -- end proc
GO

Execution Example

  1.  Benchmark Wait Types to find the top wait types for your workload
  2. Start Extended Event Capture for your workload top wait types
    DECLARE @WaitTypeTVP AS WaitType;
      INSERT INTO @WaitTypeTVP (Name)
      VALUES ('PAGEIOLATCH_EX'), ('SOS_SCHEDULER_YIELD'),('PAGEIOLATCH_SH')
      EXEC Waits.CaptureStatementsCausingWaits @TVP = @WaitTypeTVP;
  3. Get Statements Causing Waits
    exec  [Waits].[GetStatementsCausingWaits]
  4. Stop Extended Event Capture
    exec Waits.CaptureStatementsCausingWaits @StopXevent = 1

 

Benchmark SQL Server Wait Statistics

One of the secret weapons in performance tuning with SQL Server is understanding wait statistics. Every time a process (spid) changes its status from running to suspended it is typically due to resource wait or a signal wait. For example, you are typically waiting on PAGEIOLATCH_SH when you run a query that needs to pull data from disk into memory for a select statement. Understanding wait statistics can be a very helpful tool in your tool-belt if you are brought in to troubleshoot a critical issue due to slow performance. Wait statistics can quickly eliminate several resources that aren’t causing your performance problem and allow you to focus on the root cause.

** Download all scripts here **

How Do We Get Wait Statistics?

Starting in SQL Server 2005 we were given access to dynamic management views and functions. One that is very helpful with collecting waits is sys.dm_os_wait_stats.  You can access your top waits by using the following query.


SELECT TOP 10 *
FROM sys.dm_os_wait_stats
where wait_time_ms > 0
order by wait_time_ms desc

This DMV collects waits from the time the instance starts unless its cleared with the following DBBC command below. I strongly recommend that you grab snapshots of this view and compare instead of purging the history. Once you run the DBCC SQLPERF command you lose all history of your waits. We will talk a little bit more about this in the next section.

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR)

Mistakes with Wait Statistics

Typically in the field I have seen two different mistakes with Wait Statistics. Both mistakes are fixable. The first is an easy fix, the second one takes some knowledge to prevent knee jerk reactions that can cause more problems.

The first mistake I see is people constantly running DBCC SQLPERF to clear out the sys.dm_os_wait_stats to get a starting point. Typically, one would clear out all the waits wait a little bit and use a great query like Paul Randal’s – tell me why it hurts script or Glen Berry’s DMV scripts  to see what waits occurred in the last few seconds. Logically, this seems okay but you just dumped your history of wait statistics. You cannot go back and see what were your top resource waits over time.  I strongly recommend capturing a snapshot of this view, waiting a few seconds and then capturing it again and then compare the two captures. This way you still have your history.

The second mistake is not understanding the wait types or making bad decisions based on the top wait types.  Are there good waits types? What reactions should I take due to the wait types? These are both very good questions and we cover them at a basic view in this post. First, thing I recommend doing before running any wait stats queries is get a good reference list of waits types. I personally recommend MS Whitepaper on Waits and Queues and The SQL Server Wait Type Repository by CSS SQL Server Engineers. These can be very helpful when you need to understand your top waits or build an action plan behind your waits.

Let’s take a look at the TOP 10 query result set from the query above.
Top WaitsIf you look at the top four waits in CSS SQL Server Engineers blog you will notice that for various reasons it is safe to skip these wait types. Therefore, when were collecting waits we would want to bypass waits that are not indication of resource pressure actionable.

Finally, you need to be careful about the advice on the internet. It can be like taking candy from strangers. There still quite a bit of misleading advice that can cause more harm than good. An easy example is CXPACKET waits. There are articles that recommend setting Max Degree of Parallelism to 1 for the instance. This would actually be the last thing I would want to do by just seeing CXPACKET as a top wait type.  I would want to find the queries causing  CXPACKET waits see if they are effecting the business  and tune them if needed. If that isn’t possible than I would consider this recommendation.

Capture Wait Statisitcs

So, now if you are still reading you have read about why understanding wait statistics is a good thing, basics of collecting wait statistics, and common mistakes with wait statistics. Lets jump to how I go about collecting wait statistics.

The following script will utilize three temp tables. One for a snapshot of wait statistics and another for comparing waits, and the last table for wait types we would want to skip due to the waits being background processes or waits that could safely be ignored. It will get a delta of your wait statistics for five minutes.


/* Create temp tables to capture wait stats to compare */
IF OBJECT_ID('tempdb..#WaitStatsBench') IS NOT NULL
DROP TABLE #WaitStatsBench
IF OBJECT_ID('tempdb..#WaitStatsFinal') IS NOT NULL
DROP TABLE #WaitStatsFinal
IF OBJECT_ID('tempdb..#BenignWaits') IS NOT NULL
DROP TABLE #BenignWaits

CREATE TABLE #WaitStatsBench (WaitType varchar(200), wait_S decimal(12,5), Resource_S decimal (12,5), Signal_S decimal (12,5), WaitCount bigint)
CREATE TABLE #WaitStatsFinal (WaitType varchar(200), wait_S decimal(12,5), Resource_S decimal (12,5), Signal_S decimal (12,5), WaitCount bigint)
CREATE TABLE #BenignWaits (WaitType varchar(200))

/* Insert Benign Waits */

INSERT INTO #BenignWaits (WaitType)
VALUES ('CLR_SEMAPHORE')
INSERT INTO #BenignWaits (WaitType)
VALUES ('LAZYWRITER_SLEEP')
INSERT INTO #BenignWaits (WaitType)
VALUES ('RESOURCE_QUEUE')
INSERT INTO #BenignWaits (WaitType)
VALUES ('SLEEP_TASK')
INSERT INTO #BenignWaits (WaitType)
VALUES ('SLEEP_SYSTEMTASK')
INSERT INTO #BenignWaits (WaitType)
VALUES ('SQLTRACE_BUFFER_FLUSH')
INSERT INTO #BenignWaits (WaitType)
VALUES ('WAITFOR')
INSERT INTO #BenignWaits (WaitType)
VALUES ('LOGMGR_QUEUE')
INSERT INTO #BenignWaits (WaitType)
VALUES ('CHECKPOINT_QUEUE')
INSERT INTO #BenignWaits (WaitType)
VALUES ('REQUEST_FOR_DEADLOCK_SEARCH')
INSERT INTO #BenignWaits (WaitType)
VALUES ('XE_TIMER_EVENT')
INSERT INTO #BenignWaits (WaitType)
VALUES ('BROKER_TO_FLUSH')
INSERT INTO #BenignWaits (WaitType)
VALUES ('BROKER_TASK_STOP')
INSERT INTO #BenignWaits (WaitType)
VALUES ('CLR_MANUAL_EVENT')
INSERT INTO #BenignWaits (WaitType)
VALUES ('CLR_AUTO_EVENT')
INSERT INTO #BenignWaits (WaitType)
VALUES ('DISPATCHER_QUEUE_SEMAPHORE')
INSERT INTO #BenignWaits (WaitType)
VALUES ('FT_IFTS_SCHEDULER_IDLE_WAIT')
INSERT INTO #BenignWaits (WaitType)
VALUES ('XE_DISPATCHER_WAIT')
INSERT INTO #BenignWaits (WaitType)
VALUES ('XE_DISPATCHER_JOIN')
INSERT INTO #BenignWaits (WaitType)
VALUES ('BROKER_EVENTHANDLER')
INSERT INTO #BenignWaits (WaitType)
VALUES ('TRACEWRITE')
INSERT INTO #BenignWaits (WaitType)
VALUES ('FT_IFTSHC_MUTEX')
INSERT INTO #BenignWaits (WaitType)
VALUES ('SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
INSERT INTO #BenignWaits (WaitType)
VALUES ('BROKER_RECEIVE_WAITFOR')
INSERT INTO #BenignWaits (WaitType)
VALUES ('ONDEMAND_TASK_QUEUE')
INSERT INTO #BenignWaits (WaitType)
VALUES ('DBMIRROR_EVENTS_QUEUE')
INSERT INTO #BenignWaits (WaitType)
VALUES ('DBMIRRORING_CMD')
INSERT INTO #BenignWaits (WaitType)
VALUES ('BROKER_TRANSMITTER')
INSERT INTO #BenignWaits (WaitType)
VALUES ('SQLTRACE_WAIT_ENTRIES')
INSERT INTO #BenignWaits (WaitType)
VALUES ('SLEEP_BPOOL_FLUSH')
INSERT INTO #BenignWaits (WaitType)
VALUES ('SQLTRACE_LOCK')
INSERT INTO #BenignWaits (WaitType)
VALUES ('DIRTY_PAGE_POLL')
INSERT INTO #BenignWaits (WaitType)
VALUES ('SP_SERVER_DIAGNOSTICS_SLEEP')
INSERT INTO #BenignWaits (WaitType)
VALUES ('HADR_FILESTREAM_IOMGR_IOCOMPLETION')
GO

/* Get baseline */
INSERT INTO #WaitStatsBench (WaitType, wait_S, Resource_S, Signal_S, WaitCount)
SELECT
wait_type,
wait_time_ms / 1000.0 AS WaitS,
(wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
signal_wait_time_ms / 1000.0 AS SignalS,
waiting_tasks_count AS WaitCount
FROM sys.dm_os_wait_stats
WHERE wait_type not in (select BW.WaitType from #BenignWaits BW)
AND wait_time_ms > 0

/**** Wait some time... ******/
DECLARE @WaitSeconds int = 300
WAITFOR DELAY @WaitSeconds
GO

INSERT INTO #WaitStatsFinal (WaitType, wait_S, Resource_S, Signal_S, WaitCount)
SELECT
wait_type,
wait_time_ms / 1000.0 AS WaitS,
(wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
signal_wait_time_ms / 1000.0 AS SignalS,
waiting_tasks_count AS WaitCount
FROM sys.dm_os_wait_stats
WHERE wait_type not in (select BW.WaitType from #BenignWaits BW)
AND wait_time_ms > 0
SELECT f.WaitType,
f.wait_S - b.wait_S as Wait_S,
f.Resource_S - b.Resource_S as Resource_S,
f.Signal_S - b.Signal_S as Signal_S,
f.WaitCount - b.WaitCount as WaitCounts,
CAST(CASE WHEN f.WaitCount - b.WaitCount = 0 THEN 0 ELSE (f.wait_S - b.wait_S) / (f.WaitCount - b.WaitCount) END AS numeric(10, 6))AS Avg_Wait_S,
CAST(CASE WHEN f.WaitCount - b.WaitCount = 0 THEN 0 ELSE (f.Resource_S - b.Resource_S) / (f.WaitCount - b.WaitCount) END AS numeric(10, 6))AS Avg_Resource_S,
CAST(CASE WHEN f.WaitCount - b.WaitCount = 0 THEN 0 ELSE (f.Signal_S - b.Signal_S) / (f.WaitCount - b.WaitCount) END AS numeric(10, 6))AS Avg_Signal_S
FROM #WaitStatsFinal f
LEFT JOIN #WaitStatsBench b ON (f.WaitType = b.WaitType)
WHERE (f.wait_S - b.wait_S) > 0
ORDER BY 2 desc, 4 desc

Benchmark Wait Statistics

Finally, I will include the code used for creating a stored procedure that will give you control on how long the wait statistics capture runs and how often we should collect during the capture process.


CREATE SCHEMA Waits
Go
CREATE TABLE Waits.WaitStats (CaptureDataID bigint, WaitType varchar(200), wait_S decimal(20,5), Resource_S decimal (20,5), Signal_S decimal (20,5), WaitCount bigint, Avg_Wait_S numeric(10, 6), Avg_Resource_S numeric(10, 6),Avg_Signal_S numeric(10, 6), CaptureDate datetime)
CREATE TABLE Waits.BenignWaits (WaitType varchar(200))
CREATE TABLE Waits.CaptureData (
ID bigint identity PRIMARY KEY,
StartTime datetime,
EndTime datetime,
ServerName varchar(500),
PullPeriod int
)

INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('CLR_SEMAPHORE')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('LAZYWRITER_SLEEP')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('RESOURCE_QUEUE')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('SLEEP_TASK')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('SLEEP_SYSTEMTASK')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('SQLTRACE_BUFFER_FLUSH')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('WAITFOR')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('LOGMGR_QUEUE')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('CHECKPOINT_QUEUE')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('REQUEST_FOR_DEADLOCK_SEARCH')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('XE_TIMER_EVENT')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('BROKER_TO_FLUSH')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('BROKER_TASK_STOP')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('CLR_MANUAL_EVENT')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('CLR_AUTO_EVENT')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('DISPATCHER_QUEUE_SEMAPHORE')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('FT_IFTS_SCHEDULER_IDLE_WAIT')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('XE_DISPATCHER_WAIT')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('XE_DISPATCHER_JOIN')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('BROKER_EVENTHANDLER')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('TRACEWRITE')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('FT_IFTSHC_MUTEX')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('BROKER_RECEIVE_WAITFOR')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('ONDEMAND_TASK_QUEUE')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('DBMIRROR_EVENTS_QUEUE')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('DBMIRRORING_CMD')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('BROKER_TRANSMITTER')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('SQLTRACE_WAIT_ENTRIES')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('SLEEP_BPOOL_FLUSH')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('SQLTRACE_LOCK')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('DIRTY_PAGE_POLL')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('SP_SERVER_DIAGNOSTICS_SLEEP')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('HADR_FILESTREAM_IOMGR_IOCOMPLETION')
INSERT INTO Waits.BenignWaits (WaitType)
VALUES ('HADR_WORK_QUEUE')

GO

--DROP PROCEDURE Waits.GetWaitStats
CREATE PROCEDURE Waits.GetWaitStats
@WaitTimeSec INT = 60,
@StopTime DATETIME = NULL
AS
BEGIN
DECLARE @CaptureDataID int
/* Create temp tables to capture wait stats to compare */
IF OBJECT_ID('tempdb..#WaitStatsBench') IS NOT NULL
DROP TABLE #WaitStatsBench
IF OBJECT_ID('tempdb..#WaitStatsFinal') IS NOT NULL
DROP TABLE #WaitStatsFinal

CREATE TABLE #WaitStatsBench (WaitType varchar(200), wait_S decimal(20,5), Resource_S decimal (20,5), Signal_S decimal (20,5), WaitCount bigint)
CREATE TABLE #WaitStatsFinal (WaitType varchar(200), wait_S decimal(20,5), Resource_S decimal (20,5), Signal_S decimal (20,5), WaitCount bigint)

DECLARE @ServerName varchar(300)
SELECT @ServerName = convert(nvarchar(128), serverproperty('servername'))

/* Insert master record for capture data */
INSERT INTO Waits.CaptureData (StartTime, EndTime, ServerName,PullPeriod)
VALUES (GETDATE(), NULL, @ServerName, @WaitTimeSec)

SELECT @CaptureDataID = SCOPE_IDENTITY()

/* Loop through until time expires */
IF @StopTime IS NULL
SET @StopTime = DATEADD(hh, 1, getdate())
WHILE GETDATE() < @StopTime
BEGIN

/* Get baseline */

INSERT INTO #WaitStatsBench (WaitType, wait_S, Resource_S, Signal_S, WaitCount)
SELECT
wait_type,
wait_time_ms / 1000.0 AS WaitS,
(wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
signal_wait_time_ms / 1000.0 AS SignalS,
waiting_tasks_count AS WaitCount
FROM sys.dm_os_wait_stats
WHERE wait_time_ms > 0
AND wait_type NOT IN ( SELECT WaitType FROM Waits.BenignWaits)

/* Wait a few minutes and get final snapshot */
WAITFOR DELAY @WaitTimeSec;

INSERT INTO #WaitStatsFinal (WaitType, wait_S, Resource_S, Signal_S, WaitCount)
SELECT
wait_type,
wait_time_ms / 1000.0 AS WaitS,
(wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
signal_wait_time_ms / 1000.0 AS SignalS,
waiting_tasks_count AS WaitCount
FROM sys.dm_os_wait_stats
WHERE wait_time_ms > 0
AND wait_type NOT IN ( SELECT WaitType FROM Waits.BenignWaits)

DECLARE @CaptureTime datetime
SET @CaptureTime = getdate()

INSERT INTO Waits.WaitStats (CaptureDataID, WaitType, Wait_S, Resource_S, Signal_S, WaitCount, Avg_Wait_S, Avg_Resource_S,Avg_Signal_S, CaptureDate)
SELECT @CaptureDataID,
f.WaitType,
f.wait_S - b.wait_S as Wait_S,
f.Resource_S - b.Resource_S as Resource_S,
f.Signal_S - b.Signal_S as Signal_S,
f.WaitCount - b.WaitCount as WaitCounts,
CAST(CASE WHEN f.WaitCount - b.WaitCount = 0 THEN 0 ELSE (f.wait_S - b.wait_S) / (f.WaitCount - b.WaitCount) END AS numeric(10, 6))AS Avg_Wait_S,
CAST(CASE WHEN f.WaitCount - b.WaitCount = 0 THEN 0 ELSE (f.Resource_S - b.Resource_S) / (f.WaitCount - b.WaitCount) END AS numeric(10, 6))AS Avg_Resource_S,
CAST(CASE WHEN f.WaitCount - b.WaitCount = 0 THEN 0 ELSE (f.Signal_S - b.Signal_S) / (f.WaitCount - b.WaitCount) END AS numeric(10, 6))AS Avg_Signal_S,
@CaptureTime
FROM #WaitStatsFinal f
LEFT JOIN #WaitStatsBench b ON (f.WaitType = b.WaitType)

TRUNCATE TABLE #WaitStatsBench
TRUNCATE TABLE #WaitStatsFinal
END -- END of WHILE

/* Update Capture Data meta-data to include end time */
UPDATE Waits.CaptureData
SET EndTime = GETDATE()
WHERE ID = @CaptureDataID
END

Now, you can run the following block of code to capture your wait statistics.


DECLARE @EndTime datetime, @WaitSeconds int
SELECT @EndTime = DATEADD(MINUTE, 30, getdate()),
@WaitSeconds = 30

EXEC Waits.GetWaitStats
@WaitTimeSec = @WaitSeconds,
@StopTime = @EndTime

If you liked this blog post check out my next one where I focus on finding the queries that caused my waits.

Learn SQL Server Performance Tuning Basics in a Single Day

Have you always wanted to dig into performance tuning but wasn’t sure where to start? If so, check out this Introduction to SQL Server Performance Tuning training opportunity for you in Pittsburgh on September 13th. It will be given as a PreCon for SQL Saturday #250 in Pittsburgh. Konstantin Melamud and I will be sharing our lessons learned, methodology and scripts so you can start finding the root causes to your performance issues when you go back to work on Monday. Through this all day training session you will learn how to implement and use a performance baseline to be proactive and keep yourself one step ahead of the game. We will cover bad developer practices that are constantly being repeated and show you how to fix them. We will also show you how to improve the performance of your troublesome queries when you cannot change the code. Finally, we will also cover best practices that can be applied to help performance on the database and instance settings level.

If you can make it down to Pittsburgh for the weekend of September 13th I would recommend attending the SQL Saturday and the Introduction to Performance Tuning PreCon.

Performance Tuning Texas Style!

If your in Texas and interested in learning some tips to help you do SQL Server Performance Tuning with free tools then I highly suggest that you attend one of these presentations coming to a city near you!

If you cannot make it out no worries, you can catch a recorded version of my presentation.

SQL Server Performance Tuning with Free Tools!

The following is a recording of my Performance Tuning for Pirates session recorded by UserGroup.TV at SQL Saturday 125 in Oklahoma City.  I recommend that you check out UserGroup.TV as they provide some great content for free.  It was an honor to be selected for this recording.  I hope you enjoy the video.  If you have any questions and need some help feel free to contact me.

If the video doesn’t render correctly above try watching the video here (UserGroup.TV) or here (SQL PASS Performance Virtual Chapter).

Links to all tools and scripts plus reference material can be found  at my SQL Server Performance Tuning with Free Tools page.

12 Steps to Workload Tuning – Winter 2012 Performance Palooza!

 

[UPDATE] Video recording from PASS Virtual Performance Chapter’s Winter 2012 Performance Palooza can be found here.

I am a huge fan of the PASS Virtual Performance Chapter and I am excited that they pinged me to speak at their Winter 2012 Performance Palooza event tomorrow. This event is similar to 24 Hours of PASS but it will focus on Performance.

I will be sharing my 12 Steps to Workload Tuning at 1PM Central Time (1900 GMT). We will focus on the methodology and we will  use RML Utilities which is a free tool provided by Microsoft CSS to help you replay, add additional stress and compare results.  If you want to improve your performance tuning skills I strongly recommend you checkout out the schedule and attend as many sessions as possible.

Stress Testing Stored Procedures with Free Tools

I currently have a presentation named Performance Tuning for Pirates. This presentation goes over using several free tools to help you with your performance tuning process. Today, I am happy to share my first video going over these tools.

Using SQLQueryStress to stress test Stored Procedures

SQLQueryStress by Adam Machanic (blog | twitter)  is one of my favorite tools for doing a quick stress test against a user object like a stored procedure. This tool can actually be used to do stress testing on T-SQL queries but I usually use it for compiled objects like stored procedures.  I love this tool because it allows you to save and load configurations to quickly redo tests. It also allows you to use a dataset for input parameters and allows you to add additional stress with configuring iterations and threads.

Note: It also comes in handy when you need to mock up some stress for demos.

I also written a tip for www.mssqltips.com that goes over a step by step guide on how to use SQLQueryStress.

Free Training: Performance Tuning with Free Tools!

This week I have two presentations on my schedule. I get to give my Performance Tuning for Pirates presentation twice this week.

Pittsburgh SQL User Group Recap

On Tuesday, I presented my Performance Tuning for Pirates presentation at the Pittsburgh SQL Server User Group. Actually, I had a little tweak that went well. One of my friends who does a lot of tuning has alway been interested in doing a presentation so I had him jump on stage and do the presentation with me. I think it was a great success and I think we will be seeing some really cool presentations from him in the future.

Performance Virtual Chapter

Today at 2PM (EST), I am also giving my Performance Tuning for Pirates presentation at the SQLPASS Virtual Performance Chapter. This will be done via LiveMeeting and is free for all PASS Members. If your not a member, I have good news for you. PASS Membership is free so signup and join in on the fun. Also, if you are not able to make it today make sure you come back to the virtual chapter as this session should be recorded for replay.

If you are looking for the resources, tools used in the Performance Tuning for Pirates presentation you can find them here.