The DMV for Day 19 is sys.dm_os_wait_stats, which is described by BOL as:
Returns information about all the waits encountered by threads that executed. You can use this aggregated view to diagnose performance issues with SQL Server and also with specific queries and batches.
There are several very useful queries that use sys.dm_os_wait_stats, but I will only be talking about one today. This DMV works with SQL Server 2005, 2008, and 2008 R2. It requires VIEW SERVER STATE permission.
-- Isolate top waits for server instance since last restart or statistics clear 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', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE' ,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT' ,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT' ,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN')) 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
This query is used to help determine what type of resource that SQL Server is spending the most time waiting on. This can help you figure out what the biggest bottleneck is at the instance level, which will then guide your efforts to focus on a particular type of problem. For example, if the top cumulative wait types are disk I/O related, then you would want to start looking at disk related DMV queries and PerfMon counters to narrow down the issue. You should also remember that these wait stats are cumulative since SQL Server was last restarted or since the wait statistics were cleared with this command:
-- Clear Wait Stats DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
If your SQL Server instance has been running for quite a while, and you make a significant change (such as adding an important new index), you should think about clearing the old wait stats with the DBCC SQLPERF command shown above. Otherwise, the old cumulative wait stats will mask what is currently going on since your change.