My favourite indicator to Monitor SQL

Introduction

There are thousands of indicators (Perfmon Counters, system views, …) one can track to monitor SQL Server, but not all of them are relevant. If I need to pick one, that would be “Processes Blocked”.

Why “Processes Blocked” is ideal indicator?

• No false positives. When it rises, you DO have an issue.
• Threshold is trivial: zero! Zero means OK, anything else is not ok. You do not have to think what the right threshold would be, it is always the same – zero.
• It is immediate. You do not have to wait one or few minutes to be certain that you have issue. If issue occurs, this indicator immediately rises, or max a second or two later.

Sessions lock resources they are accessing. If other session tries to access same resource with incompatible lock, it waits for the first session to release the lock. That is called “blocking” and that is what this counter shows very nicely.

Watch the video:

Graph from your production might look like this. It shows when you have blocking issues. If in regular intervals, that indicates something scheduled (a job) is involved:

How to set it up

Via Performance Monitor, under “SqlServer:General Statistics”

You can track it through SQL query:

It is also part of counters tracked by my “performance tracking” package you can find in this page: Collect Performance Data – highly recommended to install on all your SQL servers.

Why it is NOT enough?

Because we cannot monitor SQL Server with just one indicator to be certain that all is in order. This indicator monitors only (b)locking issues, and it does that perfectly. But there are issues with other things that are tracked with other indicators.

Conclusion

Please, include “Processes Blocked” in your monitoring tool. It will make a world of difference. You will be amazed how often someone does a change in transaction and forgets to COMMIT. Or someone fires lengthy DELETE making locks escalate to entire table, blocking everyone for one hour. All these will be very fast detected with this counter. Your service will become better and DB users will thank you.

Call to action

What is your most useful indicator for monitoring SQL? Please write in comments below.