Tag Archives: performance tuning

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 , ,

Benchmark SQL Server Wait Statistics

Posted on October 8, 2013 by | 12 comments

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

** Download all scripts here **

How Do We Get Wait Statistics?

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

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

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

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR)

Mistakes with Wait Statistics

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

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

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

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

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

Capture Wait Statisitcs

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

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

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

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

/* Insert Benign Waits */

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

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

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

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

Benchmark Wait Statistics

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

/***************************************************************************
    Author : John Sterrett, Procure SQL LLC

    File:     WaitStats.sql

    Summary:  This script leverages sys.dm_os_wait_stats and runs twice with 
              a wait specified by @WaitSeconds in between. These two captures 
              are then compaired and then saved.    

    Parameter: @WaitSeconds int - is used to wait between captures to compare.

    Date:      October 2013

    Version:  SQL 2005+ 
  

  ---------------------------------------------------------------------------
  
  For more scripts and sample code, check out 
    https://johnsterrett.com

  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.

  Other related links:
 http://technet.microsoft.com/library/Cc966413

    Further Reading (Not By Me):
http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
http://blogs.msdn.com/b/psssql/archive/2009/11/03/the-sql-server-wait-type-repository.aspx

A first look at SQL Server 2012 Availability Group Wait Statistics


************************************************************************/


     

*/
If NOT EXISTS (Select 1 from sys.schemas where name = N'Waits')
        execute sp_executesql @stmt = N'CREATE SCHEMA [Waits] AUTHORIZATION [dbo];'



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

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

insert Waits.BenignWaits (WaitType) VALUES ('QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP');
insert Waits.BenignWaits (WaitType) VALUES ('QDS_PERSIST_TASK_MAIN_LOOP_SLEEP');
GO

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

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

    DECLARE @ServerName varchar(300)
    SELECT @ServerName = convert(nvarchar(128), serverproperty('servername'))
    
    /* Insert master record for capture data */
    INSERT INTO Waits.CaptureData (StartTime, EndTime, ServerName,PullPeriod)
    VALUES (GETDATE(), NULL, @ServerName, @WaitTimeSec)
    
    SELECT @CaptureDataID = SCOPE_IDENTITY()
     
/* Loop through until time expires  */
    IF @StopTime IS NULL
        SET @StopTime = DATEADD(hh, 1, getdate())
    WHILE GETDATE() < @StopTime
    BEGIN

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

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

        INSERT INTO #WaitStatsFinal (WaitType, wait_S, Resource_S, Signal_S, WaitCount)
        SELECT
                wait_type,
                wait_time_ms / 1000.0 AS WaitS,
                (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
                signal_wait_time_ms / 1000.0 AS SignalS,
                waiting_tasks_count AS WaitCount
            FROM sys.dm_os_wait_stats
            WHERE wait_time_ms > 0.01
            AND wait_type NOT IN ( SELECT WaitType FROM Waits.BenignWaits)
        
        DECLARE @CaptureTime datetime 
        SET @CaptureTime = getdate()

        INSERT INTO Waits.WaitStats (CaptureDataID, WaitType, Wait_S, Resource_S, Signal_S, WaitCount, Avg_Wait_S, Avg_Resource_S,Avg_Signal_S, CaptureDate)
        SELECT  @CaptureDataID,
            f.WaitType,
            f.wait_S - b.wait_S as Wait_S,
            f.Resource_S - b.Resource_S as Resource_S,
            f.Signal_S - b.Signal_S as Signal_S,
            f.WaitCount - b.WaitCount as WaitCounts,
            CAST(CASE WHEN f.WaitCount - b.WaitCount = 0 THEN 0 ELSE (f.wait_S - b.wait_S) / (f.WaitCount - b.WaitCount) END AS numeric(10, 6))AS Avg_Wait_S,
            CAST(CASE WHEN f.WaitCount - b.WaitCount = 0 THEN 0 ELSE (f.Resource_S - b.Resource_S) / (f.WaitCount - b.WaitCount) END AS numeric(10, 6))AS Avg_Resource_S,
            CAST(CASE WHEN f.WaitCount - b.WaitCount = 0 THEN 0 ELSE (f.Signal_S - b.Signal_S) / (f.WaitCount - b.WaitCount) END AS numeric(10, 6))AS Avg_Signal_S,
            @CaptureTime
        FROM #WaitStatsFinal f
        LEFT JOIN #WaitStatsBench b ON (f.WaitType = b.WaitType)
        WHERE (f.wait_S - b.wait_S) > 0.0 -- Added to not record zero waits in a time interval.
        
        TRUNCATE TABLE #WaitStatsBench
        TRUNCATE TABLE #WaitStatsFinal
 END -- END of WHILE
 
 /* Update Capture Data meta-data to include end time */
 UPDATE Waits.CaptureData
 SET EndTime = GETDATE()
 WHERE ID = @CaptureDataID
END

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

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

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

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

12 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 , , , , ,

Performance Tuning Texas Style!

Posted on January 15, 2013 by | Leave a comment

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

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

Leave a comment

Posted in Uncategorized

Tagged , , ,

SQL Server Performance Tuning with Free Tools!

Posted on January 14, 2013 by | 4 comments

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

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

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

4 Comments

Posted in SQLServerPedia Syndication

Tagged , , , ,

12 Steps to Workload Tuning – Winter 2012 Performance Palooza!

Posted on December 5, 2012 by | Leave a comment

 

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

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

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

Leave a comment

Posted in SQLServerPedia Syndication

Tagged , , ,

Stress Testing Stored Procedures with Free Tools

Posted on July 12, 2012 by | 2 comments

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

Using SQLQueryStress to stress test Stored Procedures

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

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

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

2 Comments

Posted in SQL Server, SQLServerPedia Syndication

Tagged , ,

Free Training: Performance Tuning with Free Tools!

Posted on June 28, 2012 by | Leave a comment

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

Pittsburgh SQL User Group Recap

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

Performance Virtual Chapter

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

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

Leave a comment

Posted in Professional Development, SQL Server, SQLServerPedia Syndication

Tagged , , ,