Tag Archives: Extended Events

Extended Event Security Permissions

Posted on January 20, 2015 by | Leave a comment

Recently, I gave my first presentation of the year at Austin .NET User Group on Writing Faster Queries and I got a great question about Extended Events. The question was, “What security permissions are required for creating and modifying Extended Event Sessions?”  I thought it would make a great blog post as I am sure others might be wondering the same thing.

In SQL Server 2008 to create an Extended Event Session you are going to need CONTROL SERVER  and  ALTER ANY EVENT SESSION permission on the instance that you need Extended Events created on. With SQL Server 2012 and 2014 If you want to create and modify Extended Events you are only going to need ALTER ANY EVENT SESSION.

If you like this post check out these other related blog posts:

Leave a comment

Posted in Uncategorized

Tagged , ,

SQL Server Performance Root Cause Analysis in 10 Minutes

Posted on October 15, 2013 by | 6 comments

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

6 Comments

Posted in SQLServerPedia Syndication

Tagged , , ,

What Queries are Causing My Waits?

Posted on October 10, 2013 by | 6 comments

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

 

6 Comments

Posted in SQLServerPedia Syndication

Tagged , ,

Learn SQL Server Performance Tuning Basics in a Single Day

Posted on August 28, 2013 by | Leave a comment

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.

Leave a comment

Posted in Performance Tuning, Professional Development, SQLServerPedia Syndication

Tagged , , , , ,

Workload Tuning: Finding Top Offenders in SQL Server 2012

Posted on August 7, 2013 by | 1 comment

I have to give credit where credit is due. Microsoft has definitely made performance tuning easier in SQL Server 2012. Performance tuning usually starts with finding your top offenders so you can get the biggest bang for your tuning bucks. For the first time, you can capture a workload using extended events and find top offenders inside of Management Studio without having to write a single line of T-SQL or use any third party tools.

Below is a five minute demo. It was recorded in 1080p HD so I strongly recommend changing the quality of the video to that setting. I hope you enjoy watching it as much as I did making it. If for any reason the video doesn’t show below you can also watch it here.

1 Comment

Posted in Performance Tuning, SQLServerPedia Syndication

Tagged , ,