patternsqlMinor
SQL-Server performance metrics: What exactly is Lock Waits/Sec (or: Why is it that unrealistically high?)
Viewed 0 times
whywhatwaitssqlunrealisticallyhighmetricssecperformancethat
Problem
I have a production SQL-Server, which seems to have increasing numbers of request taking extremely long (median response time is low, average response time is growing steadily). Therefore I started monitoring internal stats like the "lock waits per second".
The production instance returns nearly 2.000.000 Lock Waits/Sec, the test instance only ~3.000 Lock Waits/Sec. (This discrepancy is of course not unexpected, since the production instance has all the load, the test instance only a few requests of devs testing the new version of the application.)
Lock Waits/Sec is defined by Microsoft as "Number of lock requests per second that required the caller to wait."
This definition doesn't help me at all though, how could there be 2 million lock waits per second?? There are no even 2 million DB-requests at all, by far? Also this number fluctuates only by single digits over periods of time with very different load; if it is per second, it should move much, much more, shouldn't it?
Therefore my question: What exactly does Lock Waits/Sec stand for, and how do I have to interpret the numbers above?
The production instance returns nearly 2.000.000 Lock Waits/Sec, the test instance only ~3.000 Lock Waits/Sec. (This discrepancy is of course not unexpected, since the production instance has all the load, the test instance only a few requests of devs testing the new version of the application.)
Lock Waits/Sec is defined by Microsoft as "Number of lock requests per second that required the caller to wait."
This definition doesn't help me at all though, how could there be 2 million lock waits per second?? There are no even 2 million DB-requests at all, by far? Also this number fluctuates only by single digits over periods of time with very different load; if it is per second, it should move much, much more, shouldn't it?
Therefore my question: What exactly does Lock Waits/Sec stand for, and how do I have to interpret the numbers above?
Solution
The name "Lock Waits/Sec" is terribly misleading.
When you read this counter from
If you view it in Perfmon.exe, though, that application does the work of taking diffs and showing a "per second" view of the value.
In your case, 2 million lock waits over the entire uptime of the server might be pretty innocuous. Of course, a lot of that probably depends on how long things waited on the locks, so this metric won't help you much in isolation.
To your broader issue, if you want to get a sense of what's going on with this server, running
When you read this counter from
sys.dm_os_performance_counters, it is a cumulative value that is reset when the instance is restarted. Note that this is true of many (or maybe all?) of the "per second" counters here.If you view it in Perfmon.exe, though, that application does the work of taking diffs and showing a "per second" view of the value.
In your case, 2 million lock waits over the entire uptime of the server might be pretty innocuous. Of course, a lot of that probably depends on how long things waited on the locks, so this metric won't help you much in isolation.
To your broader issue, if you want to get a sense of what's going on with this server, running
sp_BlitzFirst is a good place to start. It will take a 5 second sample of a number of performance-related metrics (including perfmon counters), and give you a summary of the good, the bad, and the ugly.Context
StackExchange Database Administrators Q#269797, answer score: 9
Revisions (0)
No revisions yet.