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.
Additional Articles on SQL Server Pro for Wait Statistics and Locks:
Index Operational Stats and Drilling into Latch and Lock Waits
Comprehensive Wait Stats Query for SQL Server 2005 – 2016
New Dynamic Management Views in SQL Server 2016 Part 2: dm_exec_session_waits
Query to Identify Cumulative Locking Waits
In order to return results for waits resulting from locking I’ll take a basic query against sys.dm_os_wait_stats and filter for just waits related to locks. I’m going to eliminate any results where the waiting_tasks_count greater than 0 to avoid any possible division by 0 errors when calculating the metric I’m most interested in: average wait time. This should not be an issue to begin with: records should only get collected into the underlying data source for this DMV when there has been at least one task that has had to wait for a specific wait type.
SELECT WS.wait_type , WS.waiting_tasks_count , WS.wait_time_ms , WS.max_wait_time_ms , WS.wait_time_ms / WS.waiting_tasks_count AS avg_wait_time_msFROM sys.dm_os_wait_stats AS WSWHERE WS.waiting_tasks_count > 0 AND WS.wait_type LIKE 'LCK_%'ORDER BY WS.waiting_tasks_count DESC;
I also want to sort the results to return the lock types that occur frequently over those that seldom occur. I see those infrequent locks as outliers that may be of interest, but are definitely not the first area of analysis I want to spend valuable time analyzing. I would also expect the results to show those outlier lock types as having a high average wait time in milliseconds since the accumulated wait times are spread across quite few occurrences. This assumption plays out below in the results against an actual production environment I’ve executed this query against:
What do the results tell me? I’m able to determine at a quick glance that my most frequent lock waits are update and exclusive in nature. This is quite common with environments where there are a lot of data modifications going on. LCK_M_U waits are indicative of a wait to obtain an update lock on a given resource while waiting for an incompatible lock to release on the resource first. By an order of magnitude this is our most common lock wait. While I can’t determine which resources are the source of such locks presented here I can drill into this from another angle using sys.dm_db_index_operational_stats to identify indexes and heaps with the most locking/blocking which I wrote about here.
Depending on where your thresholds are for latency in overall user transactions a 15 ms wait can be acceptable or not. When analyzing the results on your environments you’ll need to take your standards into consideration. Keep in mind that the lock wait may be just one of multiple resource waits that a single session has to contend with when getting a request fulfilled from the time the query is submitted until the time it’s committed.
Finally, while I provided links above for articles I’ve written for SQL Server Pro on the subject of locks and waits you may crave more information on specific lock types and lock compatibility. Microsoft documentation has extensive information on the individual lock types and compatibility. I recommend following up on this subject there when analyzing the specific lock types in your results:
Locking Modes in Microsoft SQL Server
