I made a resolution that I was going to learn and use 2 new Dynamic Management Objects (DMOs aka Dynamic Management Views and Dynamic Management Functions aka DMVs and DMFs) each month in 2010. Well, here it is the end of the first week in February so I guess I should report in. The first DMV I’ll be covering is one that is getting a great deal of attention as of late: sys.dm_os_wait_stats.
SQL Server has some things in common with ex-girlfriends of mine. It keeps track of every time it needs to wait for something – anything. Parallelism causing delays? Record a CX_PACKET wait. The application doing row-by-row processing of a batch result returned from SQL Server and not releasing? Write down “ASYNC_NETWORK_IO”. The DBA lose track of what he was doing? That’s a SOMETHING_SHINY wait folks. Point is that unlike my exes, this is good for me – and for you.
By analyzing what waits are being recorded you can drill-down into specific resource areas to determine what is your bottleneck/performance issue on the SQL Server instance. Specific waits can point you to CPU, Memory, or I/O issues to resolve. By further consulting additional DMVs (which we will eventually cover here in subsequent months) you can resolve your performance issues much more quickly than the days when you would stare at Perfmon and Profiler traces.
What does the output of a call from sys.dm_os_wait_stats look like? Here is a sampling:
SELECT * FROM sys.dm_os_wait_stats;
Glenn Berry, in his chapter on DMVs in the MVP Deep Dive Book had a great query that utilizes sys.dm_os_wait_stats. I’ve taken that query and altered it slightly for my use to get a snapshot of waits in percentage form at the current point in time. By creating a common table expression to build a Waits table you can filter out irrelevant wait types and then look at a listing of just those waits that comprise the top N% (in this case 95%) of the waits on the SQL Server instance.
WITH Waits AS
(
SELECT
wait_type,
wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type
NOT IN
('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE',
'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR',
'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT')
) -- filter out additional irrelevant waits
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2 ON W2.rn <= W1.rn
GROUP BY W1.rn,
W1.wait_type,
W1.wait_time_s,
W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold;
The important thing to remember is that the metrics for the columns returned when querying sys.dm_os_wait_stats is cummulative. Like all Dynamic Management Object information these values accumulate over time and are wiped-clean at a SQL Server service restart. This is why I advocate persisting the information in a static table on a dedicated administrative database located on each managed SQL Server instance. You can then query the information just for the period between the last two stats collections. I use a SQL Agent job that runs hourly to persist the information to the static table. You could either run the code manually when desired or schedule as works best in your organization. The query is lightweight, not really too noticeable on a decently-sized server. The templated script below will allow you to do just that. Note that the remaining scripts in this post use template notation. CNTL+SHIFT+M in SQL Server Management Studio will provide a form to enter parameter values that are correct for your environment.
USE [<database_name,,Foo>]; --Create table to persist wait stats information: CREATE TABLE <table_schema_name,,dbo>.<table_name,,dm_os_wait_stats> ( [wait_type] [nvarchar](60) NOT NULL, [waiting_tasks_count] [bigint] NOT NULL, [wait_time_ms] [bigint] NOT NULL, [max_wait_time_ms] [bigint] NOT NULL, [signal_wait_time_ms] [bigint] NOT NULL, [capture_time] [datetime] NOT NULL, [increment_id] [int] NOT NULL ); ALTER TABLE <table_schema_name,,dbo>.<table_name,,dm_os_wait_stats> ADD DEFAULT (GETDATE()) FOR [capture_time]; --Insert wait stats info in a datestamped format for later querying: DECLARE @DT DATETIME ; SET @DT = GETDATE() ; DECLARE @increment_id INT; SELECT @increment_id = MAX(increment_id) + 1 FROM <table_schema_name,,dbo>.<table_name,,dm_os_wait_stats>; SELECT @increment_id = ISNULL(@increment_id) INSERT INTO <database_name,,Foo>.<table_schema_name,,dbo>.<table_name,,dm_os_wait_stats> ([wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms], [capture_time], [increment_id]) SELECT [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms], @DT, @increment_id FROM sys.dm_os_wait_stats;
The following information would be persisted to the physical table of your choosing. If using the defaults in the template it would be Foo.dbo.dm_os_wait_stats. Note how it creates and increment_id and datestamp for the data collected:
Then you can run a query similar to this and only view count resulting from recent activity – not activity over days, months, weeks, etc (trailing columns removed from screen shot for space concerns.):
--Return persisted information from table
USE [<database_name,,Foo>];
DECLARE @max_increment_id INT
------------------------------------------------------------------
--Determine most-recent increment_id
------------------------------------------------------------------
SELECT @max_increment_id = MAX(increment_id)
FROM <table_schema_name,,dbo>.<table_name,,dm_os_wait_stats>
------------------------------------------------------------------
--Present Waits results for period
------------------------------------------------------------------
SELECT DOWS1.wait_type,
(DOWS1.waiting_tasks_count - DOWS2.waiting_tasks_count) AS [waiting_tasks_count],
(DOWS1.wait_time_ms - DOWS2.wait_time_ms) AS [wait_time_ms],
DOWS1.max_wait_time_ms,
(DOWS1.signal_wait_time_ms - DOWS2.signal_wait_time_ms) AS [signal_wait_time_ms],
DATEDIFF(ms, DOWS2.capture_time, DOWS1.capture_time) AS [elapsed_time_ms],
DOWS1.capture_time AS [last_time_stamp], DOWS2.capture_time AS [previous_time_stamp]
FROM
(
SELECT wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms,
signal_wait_time_ms, capture_time, increment_id
FROM <table_schema_name,,dbo>.<table_name,,dm_os_wait_stats>
WHERE increment_id = @max_increment_id
)AS DOWS1
INNER JOIN
(
SELECT wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms,
signal_wait_time_ms, capture_time, increment_id
FROM <table_schema_name,,dbo>.<table_name,,dm_os_wait_stats>
WHERE increment_id = (@max_increment_id - 1)
)AS DOWS2 ON DOWS1.wait_type = DOWS2.wait_type
WHERE (DOWS1.wait_time_ms - DOWS2.wait_time_ms) > 0
/*
This can technically be eliminated because we're not persisting these waits:
AND DOWS1.wait_type NOT IN
('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE',
'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR',
'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT')
*/
ORDER BY (DOWS1.wait_time_ms - DOWS2.wait_time_ms) DESC;
Finally, you could re-tool the earlier top N% waits query to examine just the most recent wait stats activity from the persisted table as demonstrated below. You can utilize this process (building a CTE comparing deltas for just the activity collected since the last two polling periods) to retrofit any query you find online for analyzing results from sys.dm_os_wait_stats.
USE [<database_name,,Foo>]; DECLARE @max_increment_id INT ------------------------------------------------------------------ --Determine most-recent increment_id ------------------------------------------------------------------ SELECT @max_increment_id = MAX(increment_id) FROM <table_schema_name,,dbo>.<table_name,,dm_os_wait_stats>; ------------------------------------------------------------------ --Present Waits results for period ------------------------------------------------------------------ WITH Waits AS ( SELECT DOWS1.wait_type, ((DOWS1.wait_time_ms - DOWS2.wait_time_ms)/1000) AS [wait_time_s], 100. * (DOWS1.wait_time_ms - DOWS2.wait_time_ms) / SUM(DOWS1.wait_time_ms - DOWS2.wait_time_ms) OVER() AS pct, ROW_NUMBER() OVER(ORDER BY (DOWS1.wait_time_ms - DOWS2.wait_time_ms) DESC) AS rn FROM ( SELECT wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms, capture_time, increment_id FROM <table_schema_name,,dbo>.<table_name,,dm_os_wait_stats> WHERE increment_id = @max_increment_id )AS DOWS1 INNER JOIN ( SELECT wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms, capture_time, increment_id FROM <table_schema_name,,dbo>.<table_name,,dm_os_wait_stats> WHERE increment_id = (@max_increment_id - 1) )AS DOWS2 ON DOWS1.wait_type = DOWS2.wait_type WHERE (DOWS1.wait_time_ms - DOWS2.wait_time_ms) > 0 ) SELECT W1.wait_type, CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s, CAST(W1.pct AS DECIMAL(12, 2)) AS pct, CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct FROM Waits AS W1 INNER JOIN Waits AS W2 ON W2.rn <= W1.rn GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold;
