Blog Post

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

 

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Share

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Related content

Technical Article

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

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

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2009-02-23

1,567 reads

Technical Article

Networking - Part 4

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

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2009-02-17

1,530 reads

Technical Article

Speaking at Community Events - More Thoughts

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

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2009-02-13

360 reads