Thursday, September 29, 2011

Roll Your Own MDW, Sort Of

One of the reasons that I have this blog is to share things that I think are valuable to other SQL Server professionals.  Another reason is so that I can learn from the feedback that others provide, here and elsewhere.  Today is a perfect example.

A discussion broke out, as discussions are prone to do, on #sqlhelp this morning when the question was asked "How do I query sql server for long running/ intensive queries within time frame? (i.e This is after the fact, can't use profiler)".

Prior to SQL 2005, if you weren't actively capturing trace logs, the answer to this question would be "you can't".

SQL 2005 introduced us to DMV's (dynamic management views), system tables that are used by SQL Server to collect various metrics about server health and performance.  One of those DMV's, sys.dm_exec_query_stats, comes close to providing the answer, but doesn't quite go far enough.  I won't go into all of the reasons why, that's a lengthy topic of its own, and to be honest, I don't know all of the reasons.

During this conversation on Twitter, it was agreed that the DMV alone isn't enough, but the suggestion was made to make use of something called "MDW".  My first response was "What is MDW?".  That's when I learned about Maintenance Data Warehouse, a new feature of SQL 2008 that I had never heard of before.  It appears to be a mechanism for saving snapshots of the DMV data at various points in time, making that data more useful to people like the originator of the Twitter discussion. However, that's still not the point of this post.

The point of this post is to share a technique that I've been using for some time to make better use of the DMV data.  I've been logging, in 5-minute intervals, snapshots of the top 100 metrics from sys.dm_exec_query_stats, as ranked by reads, writes, duration, and CPU consumption.  It's not free, there is some server expense involved in collecting these statistics, but the value of having them far outweighs the cost.

First, you need a couple of tables, a view, and a trigger, to facilitate the long-term storage of this metrics data:

CREATE TABLE dbo.QueryStatsQueriesExecuted
	(
		QueryID				INT				NOT NULL	IDENTITY(1,1),
		QueryText			NVARCHAR(MAX)	NOT NULL,
		QueryTextChecksum	AS (CHECKSUM(QueryText)) PERSISTED,
		CONSTRAINT PK_QueryStatsQueriesExecuted PRIMARY KEY CLUSTERED (QueryID)
	)
GO

CREATE INDEX IX_QueryStatsQueriesExecuted_Checksum ON dbo.QueryStatsQueriesExecuted
	(QueryTextChecksum)
GO

CREATE TABLE dbo.QueryStatsExecutionStats
	(
		QueryStatsExecutionStatsID		INT			NOT NULL	IDENTITY(1,1),
		QueryID							INT			NOT NULL,
		ExecutionCount					BIGINT		NOT NULL,
		CPU								BIGINT		NOT NULL,
		Reads							BIGINT		NOT NULL,
		Writes							BIGINT		NOT NULL,
		Duration						BIGINT		NOT NULL,
		StatsDateTime					DATETIME	NOT NULL	DEFAULT (GETDATE()),
		CONSTRAINT PK_QueryStatsExecutionStats PRIMARY KEY CLUSTERED (QueryStatsExecutionStatsID),
		CONSTRAINT FK_QueryStatsExecutionStats_QueryStatsQueriesExecuted FOREIGN KEY(QueryID) REFERENCES dbo.QueryStatsQueriesExecuted(QueryID)
	)
GO

CREATE INDEX IX_QueryStatsExecutionStats_QueryID ON dbo.QueryStatsExecutionStats
	(QueryID);
	
CREATE INDEX IX_QueryStatsExecutionStats_StatsDateTime ON dbo.QueryStatsExecutionStats
	(StatsDateTime)
	INCLUDE (QueryID);
GO

CREATE VIEW dbo.QueryExecutionStats
AS
SELECT
	QueryStatsQueriesExecuted.QueryText,
	QueryStatsExecutionStats.ExecutionCount,
	QueryStatsExecutionStats.CPU,
	QueryStatsExecutionStats.Reads,
	QueryStatsExecutionStats.Writes,
	QueryStatsExecutionStats.Duration,
	QueryStatsExecutionStats.StatsDateTime
FROM dbo.QueryStatsExecutionStats
INNER JOIN dbo.QueryStatsQueriesExecuted
	ON QueryStatsExecutionStats.QueryID = QueryStatsQueriesExecuted.QueryID;
GO

CREATE TRIGGER dbo.QueryExecutionStats_Insert ON dbo.QueryExecutionStats
INSTEAD OF INSERT
AS
BEGIN

	MERGE dbo.QueryStatsQueriesExecuted
		USING 
			(
				SELECT DISTINCT QueryText
				FROM inserted
			) AS executedQueries
		ON QueryStatsQueriesExecuted.QueryTextChecksum = CHECKSUM(executedQueries.QueryText)
			AND QueryStatsQueriesExecuted.QueryText = executedQueries.QueryText
	WHEN NOT MATCHED THEN
		INSERT (QueryText)
		VALUES (executedQueries.QueryText);

	INSERT INTO dbo.QueryStatsExecutionStats
		(QueryID, ExecutionCount, CPU, Reads, Writes, Duration)
	SELECT
		QueryStatsQueriesExecuted.QueryID,
		inserted.ExecutionCount,
		inserted.CPU,
		inserted.Reads,
		inserted.Writes,
		inserted.Duration
	FROM inserted
	INNER JOIN dbo.QueryStatsQueriesExecuted
		ON QueryStatsQueriesExecuted.QueryTextChecksum = CHECKSUM(inserted.QueryText)
			AND QueryStatsQueriesExecuted.QueryText = inserted.QueryText;

END

Second, you need an Agent job, or some other mechanism, for running this query at regular intervals:

USE [VueSys]
GO
/****** Object:  StoredProcedure [dbo].[GetTop100QueryExecutionStats]    Script Date: 09/29/2011 09:22:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetTop100QueryExecutionStats]
AS
BEGIN

INSERT INTO dbo.QueryExecutionStats
	(QueryText, ExecutionCount, CPU, Reads, Writes, Duration)
SELECT
	QueryText,
	ExecutionCount,
	CPU,
	Reads,
	Writes,
	Duration
FROM
	(
		SELECT
			QueryText,
			ExecutionCount,
			CPU,
			Reads,
			Writes,
			Duration,
			Count_Ranking = RANK() OVER(ORDER BY ExecutionCount DESC),
			CPU_Ranking = RANK() OVER(ORDER BY CPU DESC),
			Reads_Ranking = RANK() OVER(ORDER BY Reads DESC),
			Writes_Ranking = RANK() OVER(ORDER BY Writes DESC),
			Duration_Ranking = RANK() OVER(ORDER BY Duration DESC)
		FROM
			(
				SELECT
					QueryText					= CASE WHEN LTRIM(querytext.text) LIKE 'CREATE %' THEN LTRIM(RTRIM(querytext.text)) ELSE dbo.vfnScrubTraceLine(querytext.text) END,
					ExecutionCount				= querystats.execution_count / (DATEDIFF(MINUTE, querystats.creation_time, GETDATE()) / 5),
					CPU							= querystats.total_worker_time / (DATEDIFF(MINUTE, querystats.creation_time, GETDATE()) / 5),
					Reads						= (querystats.total_physical_reads + querystats.total_logical_reads)  / (DATEDIFF(MINUTE, querystats.creation_time, GETDATE()) / 5),
					Writes						= querystats.total_logical_writes / (DATEDIFF(MINUTE, querystats.creation_time, GETDATE()) / 5),
					Duration					= querystats.total_elapsed_time / (DATEDIFF(MINUTE, querystats.creation_time, GETDATE()) / 5)
				FROM sys.dm_exec_query_stats AS querystats
				CROSS APPLY sys.dm_exec_sql_text(querystats.sql_handle) AS querytext
				WHERE querystats.last_execution_time >= DATEADD(MINUTE, -5, GETDATE())
					AND (DATEDIFF(MINUTE, querystats.creation_time, GETDATE()) / 5) > 0
					AND querytext.text NOT LIKE '% sys.%'
					AND querytext.text NOT LIKE '% sp_%'
			) AS summary
	) AS ranked
WHERE ranked.CPU_Ranking <= 100
	OR ranked.Count_Ranking <= 100
	OR ranked.Reads_Ranking <= 100
	OR ranked.Writes_Ranking <= 100
	OR ranked.Duration_Ranking <= 100

END

It's not a perfect system, but it does allow me to see, in relative terms, the heavy-hitters on my server.  It also allows me to confirm that the index I added yesterday to the table CorrespondenceEventParameters really DID make an improvement in the way "that query" performs.

SELECT TOP 500 *
FROM VueSys.dbo.QueryExecutionStats
WHERE QueryText LIKE '%CorrespondenceEventParameters%'
	AND StatsDateTime < '9/27/2011 10:35am'
ORDER BY StatsDateTime DESC


0 comments:

Post a Comment