Analyze Your Waits with SQL Dynamic Management View

I’ve never been shy about expressing my love of metadata and in particular wait stats in Microsoft SQL Server. So I want to demonstrate how you can analyze specific waits related to locking in your various SQL Server instances through a query against the sys.dm_os_wait_stats Dynamic Management View.

A Brief on sys.dm_os_wait_stats

SQL Server's Dynamic Management View provides insight into wait types that have accumulated since the last time that the SQL Server services were restarted – by either a reboot of the server or through a restart of the SQL Server services – or through a manual reset of wait statistics through running the following command:

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);

Waits – and wait statistics – are a metric that provides insight into reasons for why a SQL Server instance couldn’t process a request immediately, due to needing one or more resources. In short: it’s the excuse that SQL Server had for not being able to perform a task.

This specific DMV collects these “reasons” and the amount of time SQL Server could not execute because of the resource constraint so that you can gain insight into where your resources may be constrained. Furthermore these specific resource constraints can lead you to make performance tuning decisions because you’re able to identify quickly where your problems lay. When you’re confronted with a standard “my database is slow” statement, you can drill into the wait statistics to see what the largest issue is regarding why SQL Server is “waiting,” then navigate down a particular path of inquiry rather than attack the unknown slowness from multiple angles.

Instead of looking at memory, CPU, syntax, networking all in concert you may be able to eliminate one, or even all but one issue and resolve a performance issue quicker than otherwise would be possible. I’ve written a few articles on the subject of wait statistics, locking, and latching here in the past. I invite you to review previous articles below for more on this particular DMV and waits in general, but at this point you have enough background to understand the remainder of this article without further research into waits and sys.dm_os_wait_stats.