The more I used third party monitoring tools, the more annoyed I get. So much is missing from the details, configurability, and user experience.
I often find myself insisting on also having Query Store enabled. As much as I’d love other improvements, I think it’s also important to have a centralized experience for SQL Server users to track down tricky issues.
There are so many views and metrics out there, it would be nice to have a one stop shop to see important things.
Among those important things are blocking and deadlocks.
Deadlocks are perhaps the more obvious choice, since they’re already logged to the system health extended event session.
Rather than leave folks with a bazillion scripts and stored procedures to track them down, Query Store should add a view to pull data from there.
If Microsoft is embarrassed by how slow it is to grab all that session data, and they should be, perhaps that’s a reasonable first step to having Query Store live up to its potential.
Most folks out there have no idea where to look for that stuff, and a lot of scripts that purport to get you detail are either wildly outdated, or are a small detail away from turning no results and leaving them frustrated as hell.
I know because I talk to them.
Blocking, by default, is not logged anywhere at all in SQL Server.
If you wanna get that, you have to be ready for it, and turn on the Blocked Process Report:
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'blocked process threshold', 10; GO RECONFIGURE; GO
Of course, from there you have to… do more to get the data.
Awful lot of prep work to catch blocking in a database with a pessimistic isolation level on by default, eh?
If you want to take this to the next level, it could also grab CPU from the ring buffer, file stats, and a whole lot more. Basically everything other than PLE.
Never look at PLE.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.