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.
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
- Benchmark Wait Types to find the top wait types for your workload
- 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; - Get Statements Causing Waits
exec [Waits].[GetStatementsCausingWaits]
- Stop Extended Event Capture
exec Waits.CaptureStatementsCausingWaits @StopXevent = 1
Pingback: Benchmark SQL Server Wait Statistics | JohnSterrett.com
Pingback: Finding Top Offenders From Cache - SQL Server - SQL Server - Toad World
Pingback: SQL Server Performance Root Cause Analysis in 10 Minutes - SQL Server - SQL Server - Toad World
Pingback: SQL SERVER – Root Cause to Performance Problems – Notes from the Field #002 | Journey to SQL Authority with Pinal Dave
Pingback: T-SQL Tuesday #50: Automation for LazyDBAs! | JohnSterrett.com
Pingback: ArcticDBA | Finding the culprit