Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

John Sterrett

John Sterrett is very active within the SQL Server community. He is a PASS Regional Mentor for the South Central Region, co-founded the West Virginia SQL Server User Group and hosted two PASS SQL Saturday events in West Virginia. John has presented at several SQL Server events including PASS SQLRally, PASS Member Summit, many PASS SQL Saturday events, code camps, and, user groups. John is a syndicated blogger at http://johnsterrett.com.

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

 

Comments

Leave a comment on the original post [johnsterrett.com, opens in a new window]

Loading comments...