Tag Archives: DMV

Benchmark Azure SQL Database Wait Stats

Posted on August 30, 2016 by johnsterrett | 3 comments

Today, I want to focus on how we can monitor wait statistics in an Azure SQL Database. In the past, I blogged about how you should benchmark wait stats with the box product. This process will give you misleading data in Azure SQL Database. You will want to focus on wait stats that are specific to your database as you are using shared resources in Azure SQL Databases.

Finding Database Waits Statistics

Query we are talking to you!

With an instance of SQL Server regardless of using IaaS or on-premise, you would want to focus on all the waits that are occurring in your instance because the resources are dedicated to you.

In database as a service (DaaS), Microsoft gives you a special DMV that makes troubleshooting performance in Azure easier than any other competitor. This feature is the dm_db_wait_stats DMV. This DMV allows us specifically to get the details behind why our queries are waiting within our database and not the shared environment. Once again it is worth repeating, wait statistics for our database in a shared environment.

The following is the script is a stored procedure I use to collect wait statistics for my Azure SQL Databases. I hope it is a helpful benchmarking tool for you when you need to troubleshoot performance in Azure SQL Database.

The Good Stuff

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

    File:     AzureSQLDB_WaitStats.sql

    Summary:  The following code creates a stored procedure that can be used
                 to collect wait statistics for an Azure SQL Database.
                        
    Date:     August 2016

    Version:  Azure SQL Database V12 
  
  ---------------------------------------------------------------------------
  
  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.
************************************************************************/

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


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

Special Wait Statistics Types

The following are wait statistics you will want to focus on specifically in Azure SQL Database. If you made it this far, I strongly encourage you to read how DTU is measured. That blog post will help you understand exactly why these waits can be signs of DTU pressure.

IO_QUEUE_LIMIT : Occurs when the asynchronous IO queue for the Azure SQL Database has too many IOs pending. Tasks trying to issue another IO are blocked on this wait type until the number of pending IOs drop below the threshold. The threshold is proportional to the DTUs assigned to the database.

LOG_RATE_GOVERNOR : Occurs when DB is waiting for quota to write to the log. Yes, Azure SQL Database is capping your transactional log writes to adhere to DTU.

SOS_SCHEDULER_YIELD: This occurs when a task voluntarily yields the scheduler for other tasks to execute. During this wait, the task is waiting in the runnable queue to get a scheduler to run. If your DTU calculation is based on CPU usage you will typically see these waits.

Want More Azure Articles?

If you enjoyed this blog post I think you will also enjoy the following related blog posts.

John Sterrett is a Microsoft Data Platform MVP and a Group Principal for Procure SQL. If you need any help with your on-premise or cloud SQL Server databases, John would love to chat with you. You can contact him directly at john AT ProcureSQL dot com or here.

Photo Credit:

3 Comments

Posted in Azure, SQLServerPedia Syndication

Tagged Azure SQL Database, DMV, performance tuning

SQL Server Performance Root Cause Analysis in 10 Minutes

Posted on October 15, 2013 by johnsterrett | 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 DMV, Extended Events, performance tuning, wait statistics

Finding Top Offenders From Cache

Posted on October 14, 2013 by johnsterrett | Leave a comment

When I start a SQL Server Performance Root Cause Analysis I like to find the top waits and then find the queries causing the top waits. Next, I like to understand what is running and monitor disk latency. Finally, I would like to probe the cache to see what are my top offenders since the plans were cached.

** DOWNLOAD SCRIPTS **

Today, in this blog post were going to focus on the last remaining item, probing the cache to get top offenders. I do this because I would like to know if my current problem is also a long term problem. If you have stored procedures that get accessed frequently there is a good chance they will stay in cache. This allows you to take advantage of sys.dm_exec_query_stats to get aggregated information about cpu, reads, writes, duration for those plans. My favorite tw0 columns in sys.dm_exec_query_stats is query_hash and query_plan_hash.

QUERY_HASH and QUERY_PLAN_HASH

In the field I see a lot of people pulling data from sys.dm_exec_query_stats without grouping by query_hash and/or query_plan_hash. I strongly recommend you group by the hash columns because they can identify statements that are only different by literal values and statements with similar execution plans. For example, in the real world I have seen stored procedures with duplicate code. Basically, someone did a copy and paste when they created the a new stored procedure. Therefore, these stored procedures would have the same query_hash and query_plan_hash even thought the code belongs to different stored procedures.

How Many Executions?

Personally, I also want to know my top offenders for a few different cases. For example, if my top I/O statement only executed once it might not be as important as another statement that is the 3rd highest offender with I/O but executed 100,000 times. Therefore, I added a parameter into my stored procedures so I can filter by execution count. This allows me to find my sweet spot for top offenders for a resource vs execution counts. I also added another parameter so I can filter how many statements are returned. This quickly allows me to do TOP 10 or TOP 5 or TOP 20 on the fly.

Now, lets take a look at the code.

Total I/O

/****** Object:  StoredProcedure [dbo].[GetTopStatements_TotalIO]    Script Date: 10/14/2013 10:16:35 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		John Sterrett (@JohnSterrett)
-- Create date: 6/4/2014
-- Description:	Gets Top IO statements based on execution count
-- Example: exec dbo.GetTopStatements_TotalIO @NumOfStatements = 5, @Executions = 100
-- =============================================
CREATE PROCEDURE [dbo].[GetTopStatements_TotalIO]
	-- Add the parameters for the stored procedure here
	@NumOfStatements int = 25,
	@Executions int = 5
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	--- top 25 statements by IO
	IF OBJECT_ID('tempdb..#TopOffenders') IS NOT NULL
			DROP TABLE #TopOffenders
	IF OBJECT_ID('tempdb..#QueryText') IS NOT NULL
			DROP TABLE #QueryText
	CREATE TABLE #TopOffenders (AvgIO bigint, TotalIO bigint, TotalCPU bigint, AvgCPU bigint, TotalDuration bigint, AvgDuration bigint, [dbid] int, objectid bigint, execution_count bigint, query_hash varbinary(8))
	CREATE TABLE #QueryText (query_hash varbinary(8), query_text varchar(max))

	INSERT INTO #TopOffenders (AvgIO, TotalIO, TotalCPU, AvgCPU, TotalDuration, AvgDuration, [dbid], objectid, execution_count, query_hash)
	SELECT TOP (@NumOfStatements)
			SUM((qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count) as [Avg IO],
			SUM((qs.total_logical_reads + qs.total_logical_writes)) AS [TotalIO],
			SUM(qs.total_worker_time) AS Total_Worker_Time,
			SUM((qs.total_worker_time) / qs.execution_count) AS [AvgCPU],
			SUM(qs.total_elapsed_time) AS TotalDuration,
			SUM((qs.total_elapsed_time)/ qs.execution_count) AS AvgDuration,
		qt.dbid,
		qt.objectid,
		SUM(qs.execution_count) AS Execution_Count,
		qs.query_hash
	FROM sys.dm_exec_query_stats qs
	cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt
	GROUP BY qs.query_hash, qs.query_plan_hash, qt.dbid, qt.objectid
	HAVING SUM(qs.execution_count) > @Executions
	ORDER BY [TotalIO] DESC

--select * From #TopOffenders
--ORDER BY TotalIO desc

/* Create cursor to get query text */
DECLARE @QueryHash varbinary(8)

DECLARE QueryCursor CURSOR FAST_FORWARD FOR
select query_hash
FROM #TopOffenders

OPEN QueryCursor
FETCH NEXT FROM QueryCursor INTO @QueryHash

WHILE (@@FETCH_STATUS = 0)
BEGIN

		INSERT INTO #QueryText (query_text, query_hash)
		select MIN(substring (qt.text,qs.statement_start_offset/2, 
				 (case when qs.statement_end_offset = -1 
				then len(convert(nvarchar(max), qt.text)) * 2 
				else qs.statement_end_offset end -    qs.statement_start_offset)/2)) 
				as query_text, qs.query_hash
		from sys.dm_exec_query_stats qs
		cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt
		where qs.query_hash = @QueryHash
		GROUP BY qs.query_hash;

		FETCH NEXT FROM QueryCursor INTO @QueryHash
   END
   CLOSE QueryCursor
   DEALLOCATE QueryCursor

		select distinct DB_NAME(dbid) DBName, OBJECT_NAME(objectid, dbid) ObjectName, qt.query_text, o.*
		INTO #Results
		from #TopOffenders o
		join #QueryText qt on (o.query_hash = qt.query_hash)

		SELECT TOP (@NumOfStatements) *
		FROM #Results
		ORDER BY TotalIO desc  

		DROP TABLE #Results
		DROP TABLE #TopOffenders
		DROP TABLE #QueryText
	END

GO

Total CPU

/****** Object:  StoredProcedure [dbo].[GetTopStatements_TotalCPU]    Script Date: 10/14/2013 10:21:32 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		John Sterrett (@JohnSterrett)
-- Create date: 6/4/2013
-- Description:	Gets statements causing most CPU from cache based on executions.
-- Example: exec dbo.GetTopStatements_TotalCPU @Executions = 5, @NumOfStatements = 25
-- =============================================
CREATE PROCEDURE [dbo].[GetTopStatements_TotalCPU]
	-- Add the parameters for the stored procedure here
	@NumOfStatements int = 25,
	@Executions int = 5
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	--- top 25 statements by IO
IF OBJECT_ID('tempdb..#TopOffenders') IS NOT NULL
		DROP TABLE #TopOffenders
IF OBJECT_ID('tempdb..#QueryText') IS NOT NULL
		DROP TABLE #QueryText
CREATE TABLE #TopOffenders (AvgIO bigint, TotalIO bigint, TotalCPU bigint, AvgCPU bigint, TotalDuration bigint, AvgDuration bigint, [dbid] int, objectid bigint, execution_count bigint, query_hash varbinary(8))
CREATE TABLE #QueryText (query_hash varbinary(8), query_text varchar(max))

INSERT INTO #TopOffenders (AvgIO, TotalIO, TotalCPU, AvgCPU, TotalDuration, AvgDuration, [dbid], objectid, execution_count, query_hash)
SELECT TOP (@NumOfStatements)
        SUM((qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count) as [Avg IO],
        SUM((qs.total_logical_reads + qs.total_logical_writes)) AS [TotalIO],
        SUM(qs.total_worker_time) AS [TotalCPU],
        SUM((qs.total_worker_time) / qs.execution_count) AS [AvgCPU],
        SUM(qs.total_elapsed_time) AS TotalDuration,
		SUM((qs.total_elapsed_time)/ qs.execution_count) AS AvgDuration,
    qt.dbid,
    qt.objectid,
    SUM(qs.execution_count) AS Execution_Count,
    qs.query_hash
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt
GROUP BY qs.query_hash, qs.query_plan_hash, qt.dbid, qt.objectid
HAVING SUM(qs.execution_count) > @Executions
ORDER BY [TotalCPU] DESC

--select * From #TopOffenders
--ORDER BY TotalIO desc

/* Create cursor to get query text */
DECLARE @QueryHash varbinary(8)

DECLARE QueryCursor CURSOR FAST_FORWARD FOR
select query_hash
FROM #TopOffenders

OPEN QueryCursor
FETCH NEXT FROM QueryCursor INTO @QueryHash

WHILE (@@FETCH_STATUS = 0)
BEGIN

		INSERT INTO #QueryText (query_text, query_hash)
		select MIN(substring (qt.text,qs.statement_start_offset/2, 
				 (case when qs.statement_end_offset = -1 
				then len(convert(nvarchar(max), qt.text)) * 2 
				else qs.statement_end_offset end -    qs.statement_start_offset)/2)) 
				as query_text, qs.query_hash
		from sys.dm_exec_query_stats qs
		cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt
		where qs.query_hash = @QueryHash
		GROUP BY qs.query_hash;

		FETCH NEXT FROM QueryCursor INTO @QueryHash
   END
   CLOSE QueryCursor
   DEALLOCATE QueryCursor

		select distinct DB_NAME(dbid) DBName, OBJECT_NAME(objectid, dbid) ObjectName, qt.query_text, o.*
		INTO #Results
		from #TopOffenders o
		join #QueryText qt on (o.query_hash = qt.query_hash)

		SELECT TOP (@NumOfStatements) *
		FROM #Results
		ORDER BY TotalCPU desc  

		DROP TABLE #Results
		DROP TABLE #TopOffenders
		DROP TABLE #QueryText
	END

GO

Total Duration

/****** Object:  StoredProcedure [dbo].[GetTopStatements_TotalDuration]    Script Date: 10/14/2013 10:18:49 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		John Sterrett (@JohnSterrett)
-- Create date: 6/4/2013
-- Description:	Get total duration from cache based on executions.
-- Example: exec dbo.GetTopStatements_TotalDuration @NumOfStatements = 25, @Executions = 5
-- =============================================
CREATE PROCEDURE [dbo].[GetTopStatements_TotalDuration]
	-- Add the parameters for the stored procedure here
	@NumOfStatements int = 25,
	@Executions int = 5
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	--- top 25 statements by IO
IF OBJECT_ID('tempdb..#TopOffenders') IS NOT NULL
		DROP TABLE #TopOffenders
IF OBJECT_ID('tempdb..#QueryText') IS NOT NULL
		DROP TABLE #QueryText
CREATE TABLE #TopOffenders (AvgIO bigint, TotalIO bigint, TotalCPU bigint, AvgCPU bigint, TotalDuration bigint, AvgDuration bigint, [dbid] int, objectid bigint, execution_count bigint, query_hash varbinary(8))
CREATE TABLE #QueryText (query_hash varbinary(8), query_text varchar(max))

INSERT INTO #TopOffenders (AvgIO, TotalIO, TotalCPU, AvgCPU, TotalDuration, AvgDuration, [dbid], objectid, execution_count, query_hash)
SELECT TOP (@NumOfStatements)
        SUM((qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count) as [Avg IO],
        SUM((qs.total_logical_reads + qs.total_logical_writes)) AS [TotalIO],
        SUM(qs.total_worker_time) AS Total_Worker_Time,
        SUM((qs.total_worker_time) / qs.execution_count) AS [AvgCPU],
        SUM(qs.total_elapsed_time) AS TotalDuration,
		SUM((qs.total_elapsed_time)/ qs.execution_count) AS AvgDuration,
    qt.dbid,
    qt.objectid,
    SUM(qs.execution_count) AS Execution_Count,
    qs.query_hash
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt
GROUP BY qs.query_hash, qs.query_plan_hash, qt.dbid, qt.objectid
HAVING SUM(qs.execution_count) > @Executions
ORDER BY [TotalDuration] DESC

--select * From #TopOffenders
--ORDER BY TotalIO desc

/* Create cursor to get query text */
DECLARE @QueryHash varbinary(8)

DECLARE QueryCursor CURSOR FAST_FORWARD FOR
select query_hash
FROM #TopOffenders

OPEN QueryCursor
FETCH NEXT FROM QueryCursor INTO @QueryHash

WHILE (@@FETCH_STATUS = 0)
BEGIN

		INSERT INTO #QueryText (query_text, query_hash)
		select MIN(substring (qt.text,qs.statement_start_offset/2, 
				 (case when qs.statement_end_offset = -1 
				then len(convert(nvarchar(max), qt.text)) * 2 
				else qs.statement_end_offset end -    qs.statement_start_offset)/2)) 
				as query_text, qs.query_hash
		from sys.dm_exec_query_stats qs
		cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt
		where qs.query_hash = @QueryHash
		GROUP BY qs.query_hash;

		FETCH NEXT FROM QueryCursor INTO @QueryHash
   END
   CLOSE QueryCursor
   DEALLOCATE QueryCursor

		select distinct DB_NAME(dbid) DBName, OBJECT_NAME(objectid, dbid) ObjectName, qt.query_text, o.*
		INTO #Results
		from #TopOffenders o
		join #QueryText qt on (o.query_hash = qt.query_hash)

		SELECT TOP (@NumOfStatements) *
		FROM #Results
		ORDER BY TotalDuration desc  

		DROP TABLE #Results
		DROP TABLE #TopOffenders
		DROP TABLE #QueryText
	END

GO

Average I/O

/****** Object:  StoredProcedure [dbo].[GetTopStatements_AvgIO]    Script Date: 10/14/2013 10:23:01 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		John Sterrett (@JohnSterrett)
-- Create date: 6/4/2013
-- Description:	Get statements from cache causing most average IO based on executions.
-- Example: exec dbo.GetTopStatements_AvgIO @Executions = 5, @NumOfStatements = 25
-- =============================================
CREATE PROCEDURE [dbo].[GetTopStatements_AvgIO]
	-- Add the parameters for the stored procedure here
	@NumOfStatements int = 25,
	@Executions int = 5
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	IF OBJECT_ID('tempdb..#TopOffenders') IS NOT NULL
			DROP TABLE #TopOffenders
	IF OBJECT_ID('tempdb..#QueryText') IS NOT NULL
			DROP TABLE #QueryText
	CREATE TABLE #TopOffenders (AvgIO bigint, TotalIO bigint, TotalCPU bigint, AvgCPU bigint, TotalDuration bigint, AvgDuration bigint, [dbid] int, objectid bigint, execution_count bigint, query_hash varbinary(8))
	CREATE TABLE #QueryText (query_hash varbinary(8), query_text varchar(max))

	INSERT INTO #TopOffenders (AvgIO, TotalIO, TotalCPU, AvgCPU, TotalDuration, AvgDuration, [dbid], objectid, execution_count, query_hash)
	SELECT TOP (@NumOfStatements)
			SUM((qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count) as [Avg IO],
			SUM((qs.total_logical_reads + qs.total_logical_writes)) AS [TotalIO],
			SUM(qs.total_worker_time) AS [TotalCPU],
			SUM((qs.total_worker_time) / qs.execution_count) AS [AvgCPU],
			SUM(qs.total_elapsed_time) AS TotalDuration,
			SUM((qs.total_elapsed_time)/ qs.execution_count) AS AvgDuration,
		qt.dbid,
		qt.objectid,
		SUM(qs.execution_count) AS Execution_Count,
		qs.query_hash
	FROM sys.dm_exec_query_stats qs
	cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt
	GROUP BY qs.query_hash, qs.query_plan_hash, qt.dbid, qt.objectid
	HAVING SUM(qs.execution_count) > @Executions		
	ORDER BY [Avg IO] DESC

		--select * From #TopOffenders
		--ORDER BY AvgIO desc

		/* Create cursor to get query text */
		DECLARE @QueryHash varbinary(8)

		DECLARE QueryCursor CURSOR FAST_FORWARD FOR
		select query_hash
		FROM #TopOffenders

		OPEN QueryCursor
		FETCH NEXT FROM QueryCursor INTO @QueryHash

		WHILE (@@FETCH_STATUS = 0)
		BEGIN

				INSERT INTO #QueryText (query_text, query_hash)
				select MIN(substring (qt.text,qs.statement_start_offset/2, 
						 (case when qs.statement_end_offset = -1 
						then len(convert(nvarchar(max), qt.text)) * 2 
						else qs.statement_end_offset end -    qs.statement_start_offset)/2)) 
						as query_text, qs.query_hash
				from sys.dm_exec_query_stats qs
				cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt
				where qs.query_hash = @QueryHash
				GROUP BY qs.query_hash;

				FETCH NEXT FROM QueryCursor INTO @QueryHash
		   END
		   CLOSE QueryCursor
		   DEALLOCATE QueryCursor

		select distinct DB_NAME(dbid) DBName, OBJECT_NAME(objectid, dbid) ObjectName, qt.query_text, o.*
		INTO #Results
		from #TopOffenders o
		join #QueryText qt on (o.query_hash = qt.query_hash)

		SELECT TOP (@NumOfStatements) *
		FROM #Results
		ORDER BY AvgIO desc  

		DROP TABLE #Results
		DROP TABLE #TopOffenders
		DROP TABLE #QueryText
	END

GO

Average CPU

/****** Object:  StoredProcedure [dbo].[GetTopStatements_AvgCPU]    Script Date: 10/14/2013 10:31:47 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		John Sterrett (@JohnSterrett)
-- Create date: 6/4/2013
-- Description:	Get Statements from cache that have highest average cpu utilization by executions
-- Example: exec dbo.GetTopStatements_AvgCPU @Executions = 5, @NumbOfStatements = 25
-- =============================================
CREATE PROCEDURE [dbo].[GetTopStatements_AvgCPU]
	-- Add the parameters for the stored procedure here
	@NumOfStatements int = 25,
	@Executions int = 5
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	--- top 25 statements by IO
IF OBJECT_ID('tempdb..#TopOffenders') IS NOT NULL
		DROP TABLE #TopOffenders
IF OBJECT_ID('tempdb..#QueryText') IS NOT NULL
		DROP TABLE #QueryText
CREATE TABLE #TopOffenders (AvgIO bigint, TotalIO bigint, TotalCPU bigint, AvgCPU bigint, TotalDuration bigint, AvgDuration bigint, [dbid] int, objectid bigint, execution_count bigint, query_hash varbinary(8))
CREATE TABLE #QueryText (query_hash varbinary(8), query_text varchar(max))

INSERT INTO #TopOffenders (AvgIO, TotalIO, TotalCPU, AvgCPU, TotalDuration, AvgDuration, [dbid], objectid, execution_count, query_hash)
SELECT TOP (@NumOfStatements)
        SUM((qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count) as [Avg IO],
        SUM((qs.total_logical_reads + qs.total_logical_writes)) AS [TotalIO],
        SUM(qs.total_worker_time) AS Total_Worker_Time,
        SUM((qs.total_worker_time) / qs.execution_count) AS [AvgCPU],
        SUM(qs.total_elapsed_time) AS TotalDuration,
		SUM((qs.total_elapsed_time)/ qs.execution_count) AS AvgDuration,
    qt.dbid,
    qt.objectid,
    SUM(qs.execution_count) AS Execution_Count,
    qs.query_hash
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt
GROUP BY qs.query_hash, qs.query_plan_hash, qt.dbid, qt.objectid
HAVING SUM(qs.execution_count) > @Executions
ORDER BY [AvgCPU] DESC

--select * From #TopOffenders
--ORDER BY TotalIO desc

/* Create cursor to get query text */
DECLARE @QueryHash varbinary(8)

DECLARE QueryCursor CURSOR FAST_FORWARD FOR
select query_hash
FROM #TopOffenders

OPEN QueryCursor
FETCH NEXT FROM QueryCursor INTO @QueryHash

WHILE (@@FETCH_STATUS = 0)
BEGIN

		INSERT INTO #QueryText (query_text, query_hash)
		select MIN(substring (qt.text,qs.statement_start_offset/2, 
				 (case when qs.statement_end_offset = -1 
				then len(convert(nvarchar(max), qt.text)) * 2 
				else qs.statement_end_offset end -    qs.statement_start_offset)/2)) 
				as query_text, qs.query_hash
		from sys.dm_exec_query_stats qs
		cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt
		where qs.query_hash = @QueryHash
		GROUP BY qs.query_hash;

		FETCH NEXT FROM QueryCursor INTO @QueryHash
   END
   CLOSE QueryCursor
   DEALLOCATE QueryCursor

		select distinct DB_NAME(dbid) DBName, OBJECT_NAME(objectid, dbid) ObjectName, qt.query_text, o.*
		INTO #Results
		from #TopOffenders o
		join #QueryText qt on (o.query_hash = qt.query_hash)

		SELECT TOP (@NumOfStatements) *
		FROM #Results
		ORDER BY AvgCPU desc  

		DROP TABLE #Results
		DROP TABLE #TopOffenders
		DROP TABLE #QueryText
	END

GO

Average Duration

/****** Object:  StoredProcedure [dbo].[GetTopStatements_AvgDuration]    Script Date: 10/14/2013 10:30:17 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		John Sterrett (@JohnSterrett)
-- Create date: 6/4/2013
-- Description:	Get statements from cache causing most average duration by executions
-- Example: exec dbo.GetTopStatements_AvgDuration @NumOfStatements = 25, @Executions = 5
-- =============================================
CREATE PROCEDURE [dbo].[GetTopStatements_AvgDuration]
	-- Add the parameters for the stored procedure here
	@NumOfStatements int = 25,
	@Executions int = 5
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	--- top 25 statements by IO
IF OBJECT_ID('tempdb..#TopOffenders') IS NOT NULL
		DROP TABLE #TopOffenders
IF OBJECT_ID('tempdb..#QueryText') IS NOT NULL
		DROP TABLE #QueryText
CREATE TABLE #TopOffenders (AvgIO bigint, TotalIO bigint, TotalCPU bigint, AvgCPU bigint, TotalDuration bigint, AvgDuration bigint, [dbid] int, objectid bigint, execution_count bigint, query_hash varbinary(8))
CREATE TABLE #QueryText (query_hash varbinary(8), query_text varchar(max))

INSERT INTO #TopOffenders (AvgIO, TotalIO, TotalCPU, AvgCPU, TotalDuration, AvgDuration, [dbid], objectid, execution_count, query_hash)
SELECT TOP (@NumOfStatements)
        SUM((qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count) as [Avg IO],
        SUM((qs.total_logical_reads + qs.total_logical_writes)) AS [TotalIO],
        SUM(qs.total_worker_time) AS Total_Worker_Time,
        SUM((qs.total_worker_time) / qs.execution_count) AS [AvgCPU],
        SUM(qs.total_elapsed_time) AS TotalDuration,
		SUM((qs.total_elapsed_time)/ qs.execution_count) AS AvgDuration,
    qt.dbid,
    qt.objectid,
    SUM(qs.execution_count) AS Execution_Count,
    qs.query_hash
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt
GROUP BY qs.query_hash, qs.query_plan_hash, qt.dbid, qt.objectid
HAVING SUM(qs.execution_count) > @Executions
ORDER BY [AvgDuration] DESC

--select * From #TopOffenders
--ORDER BY TotalIO desc

/* Create cursor to get query text */
DECLARE @QueryHash varbinary(8)

DECLARE QueryCursor CURSOR FAST_FORWARD FOR
select query_hash
FROM #TopOffenders

OPEN QueryCursor
FETCH NEXT FROM QueryCursor INTO @QueryHash

WHILE (@@FETCH_STATUS = 0)
BEGIN

		INSERT INTO #QueryText (query_text, query_hash)
		select MIN(substring (qt.text,qs.statement_start_offset/2, 
				 (case when qs.statement_end_offset = -1 
				then len(convert(nvarchar(max), qt.text)) * 2 
				else qs.statement_end_offset end -    qs.statement_start_offset)/2)) 
				as query_text, qs.query_hash
		from sys.dm_exec_query_stats qs
		cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt
		where qs.query_hash = @QueryHash
		GROUP BY qs.query_hash;

		FETCH NEXT FROM QueryCursor INTO @QueryHash
   END
   CLOSE QueryCursor
   DEALLOCATE QueryCursor

		select distinct DB_NAME(dbid) DBName, OBJECT_NAME(objectid, dbid) ObjectName, qt.query_text, o.*
		INTO #Results
		from #TopOffenders o
		join #QueryText qt on (o.query_hash = qt.query_hash)

		SELECT TOP (@NumOfStatements) *
		FROM #Results
		ORDER BY AvgDuration desc  

		DROP TABLE #Results
		DROP TABLE #TopOffenders
		DROP TABLE #QueryText
	END

GO

If you enjoyed this blog post please check out my related blog posts like benchmarking your top waits and finding queries that cause your top waits or what is running and benchmarking disk latency.

24 Hours of PASS – Get your free downloads!

Posted on March 21, 2012 by johnsterrett | Leave a comment

I am speaking at 24 Hours of PASS

Thank you to everyone who attended my #24HOP session on Performance Tuning for Pirates! You can now download my slide deck, t-sql queries, and view my reference links. If you have any questions about the tools presented feel free to contact me and I will try to help you out or point you in the right direction.

T-SQL Tuesday: What’s Currently Running?

Posted on December 13, 2011 by johnsterrett | 7 comments

My good friend Allen White is hosting this months installment of #TSQL2sDay so I am motivated to jump in. #TSQL2sDay is the creation of Adam Machanic. The concept is simple, about a week before the second Tuesday of the month a theme will be posted. Any blogger that wishes to participate is invited to write a post on the chosen topic and any post that is related to both SQL Server and the theme is fair game.The challenge for this month’s T-SQL Tuesday is: What T-SQL tricks do you use today to make your job easier?

What’s Currently Running?

One of my favorite tricks is actually just a little script I have in my toolbox to find out what queries are currently running right now. In fact I have had quite a few people ask me the for this script so I am glad to share it in this blog post. With SQL Server 2005 and above SQL Server provides Database Management Views that give you direct access to executing requests and running process. The following query uses sys.dm_exec_request, sys.sysprocesses. We will also use cross apply to get the query text from sys.dm_exec_sql_text and the execution plan from sys.dm_exec_query_plan.

The Good Stuff…

{UPDATE: 1/1/2012 – Replaced sysprocesses with sys.dm_exec_sessions as recommended by Phil in the comments

UPDATE: 6/5/2013 – Changed CROSS APPLY’s to OUTER APPLY’s so we capture statements without execution plan or SQL Text.}

-- Do not lock anything, and do not get held up by any locks.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
[Spid] = sp.session_Id
,er.request_id
,er.command
,[Database] = DB_NAME(er.database_id)
,[User] = login_name
,er.blocking_session_id
,[Status] = er.status
,[Wait] = wait_type
,CAST('<?query --'+CHAR(13)+SUBSTRING(qt.text,
(er.statement_start_offset / 2)+1,     ((CASE er.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)    ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1)+CHAR(13)+'--?>' AS xml) as sql_statement
,[Parent Query] = qt.text
,p.query_plan
,er.cpu_time
, er.reads
, er.writes
, er.Logical_reads
, er.row_count
, Program = program_name
,Host_name
,start_time
FROM sys.dm_exec_requests er INNER JOIN sys.dm_exec_sessions sp ON er.session_id = sp.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) p
WHERE sp.is_user_process = 1
/* sp.session_Id > 50
-- Ignore system spids. -- */
AND sp.session_Id NOT IN (@@SPID)
ORDER BY 1, 2

7 Comments

Posted in Scripts, SQLServerPedia Syndication

Tagged DMV, T-SQL

When was that object modified?

Posted on October 6, 2009 by johnsterrett | 1 comment

Yes, every once in a while when I am deploying an application I ask myself the following question. What database objects (tables, stored procedures, functions etc..) did I modify with this release?

Ideally this is documented in the release plan but I will admit I have been known to slip every once in a while. Therefore, I am showcasing a query that can provide help. This query was written by Gordon Bell and it can be found here. It uses the sys.objects DMV that are included in SQL 2005 & 2008.

I will defiantly throw this script into my bag of tricks.

select name, modify_date,
case when type_desc = 'USER_TABLE' then 'Table'
when type_desc = 'SQL_STORED_PROCEDURE' then 'Stored Procedure'
when type_desc in ('SQL_INLINE_TABLE_VALUED_FUNCTION', 'SQL_SCALAR_FUNCTION', 
'SQL_TABLE_VALUED_FUNCTION') then 'Function'
end as type_desc
from sys.objects
where type in ('U', 'P', 'FN', 'IF', 'TF')
and is_ms_shipped = 0
order by 2 desc

1 Comment

Posted in Scripts, SQLServerPedia Syndication

Tagged DMV, T-SQL

Get index fragmentation statistics

Posted on September 30, 2009 by johnsterrett | 3 comments

I recently attended a Pittsburgh SQL Server user group meeting where Brent Ozar gave a presentation on the silent performance killer. This motivated me to create a stored procedure that could leverage the DMVs in SQL 2005/2008 to gather index fragmentation statistics for all databases on a given server.

Goal

The goal is very simple. Build a query that could be scheduled to grab statistics that are helpful towards determining if an index needs to be defragged or reorganized. I would like to throw these results into a table so I could analyze them at a later date. I would also like to monitor the fill factor and padding to determine if I need to make changes and to analyze if the changes are really helpful.

Download Scripts

The following script uses the following DMV’s sys.dm_db_index_physical_stats, sys.objects and sys.indexes and this script is provided as is.

To download the script click here

To download the create table script for the table used click here

Table Definition

The following is an explanation of the columns. The following descriptions come from MSDN.

Column Name	Description
databaseName	Name of database, unique within an instance of SQL Server.
objectName	Object name.
indexName	Name of the index. name is unique only within the object. NULL = Heap
partitionNumber	1-based partition number within the owning object; a table, view, or index. 1 = Nonpartitioned index or heap.
fragmentation	Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit. The value is measured as a percentage and takes into account multiple files. For definitions of logical and extent fragmentation, see Remarks. 0 for LOB_DATA and ROW_OVERFLOW_DATA allocation units. NULL for heaps when mode = SAMPLED.
fill_factor	> 0 = FILLFACTOR percentage used when the index was created or rebuilt. 0 = Default value
is_padded	1 = PADINDEX is ON. 0 = PADINDEX is OFF.
type_desc	Description of index type: HEAP CLUSTERED NONCLUSTERED XML SPATIAL
page_count	Total number of index or data pages. For an index, the total number of index pages in the current level of the b-tree in the IN_ROW_DATA allocation unit. For a heap, the total number of data pages in the IN_ROW_DATA allocation unit. For LOB_DATA or ROW_OVERFLOW_DATA
date	this

Script

   1:    2:      3:    4:    5:    6:      7:      8:      9:   10:     11:     12:   13:     14:     15:     16:     17:     18:     19:   20:     21:     22:     23:   24:   25:     26:     27:   28:     29:     30:     31:   32:     33:     34:     35:     36:     37:     38:     39:     40:     41:     42:     43:     44:     45:     46:     47:     48:     49:     50:     51:     52:     53:   54:     55:     56:     57:   58:     59:     60:     61:   62:     63:     64:     65:

3 Comments

Posted in Scripts, SQL Server, SQLServerPedia Syndication

Tagged DMV, indexes

Tag Archives: DMV

Benchmark Azure SQL Database Wait Stats

Finding Database Waits Statistics

The Good Stuff

Special Wait Statistics Types

Want More Azure Articles?

SQL Server Performance Root Cause Analysis in 10 Minutes

Finding Top Offenders From Cache

QUERY_HASH and QUERY_PLAN_HASH

How Many Executions?

24 Hours of PASS – Get your free downloads!

T-SQL Tuesday: What’s Currently Running?

What’s Currently Running?

The Good Stuff…

When was that object modified?

Get index fragmentation statistics

Goal

Download Scripts

Table Definition

Script

Subscribe to our Newsletter!

Top Posts & Pages

Categories