Free SQL Server Performance Monitoring Blocking and Deadlocking

Posted on by Erik Darling

Free SQL Server Performance Monitoring Blocking and Deadlocking

Summary

In this video, I delve into the blocking and deadlock monitoring capabilities of FreeSQL Server Performance Monitoring, a tool I’ve developed and made available on GitHub. With a focus on practicality and ease-of-use, I explain how we leverage extended events for both blocking and deadlock scenarios, ensuring that you can identify and address performance issues efficiently. Whether you’re using system health or prefer to rely on the block process report, my monitoring tool streamlines the process by automatically setting up necessary configurations and providing easy-to-understand visualizations of your SQL Server’s performance trends over time.

Chapters

Full Transcript

Erik Darling here with Darling Data, and in this video I want to go over how, or rather, how and what we collect in my FreeSQL Server performance monitoring tool, available on GitHub for free, way better than all the crap you pay for, around blocking and deadlocks, and although this can help you identify when you had terrible, problems. So for blocking in deadlocks, we use extended events for both. The block process report will send data to an extended event, as well as the deadlock stuff in there. Now for the deadlocks, if you don’t have a dedicated event set up to capture deadlocks, the monitoring tool will set one up for you. But if you would prefer to go to system health, then we can, then we will fall back to system health. There are also some charts that trend deadlocking and blocking activity over time. You know, like it’ll tell you all the sort of, you know, normal stuff that you would expect to see from looking at, you know, blocked process report or deadlock reports. If you were, you know, using one of, like, you know, say a free community script, like mine, a human events block viewer or SP blitz lock, it’s a very commensurate experience because a lot of the stuff is powered by community tools. That is why I am rather that is part of the open source ethos that I am hoping to instill and hope also hoping to, you know, you know, call broader attention to with people. There’s a lot of great stuff out there. But some people are afraid to run it, don’t know how to use it. And this sort of just encapsulates it and makes a lot easier. So blocked process reports go into the extended event, they are all XML, which is a damn shame because XML is an awful pain in the butt to deal with. But I try to do try to make that as easy as possible for you by doing all the shredding and picking apart that you would that you would need done. I do set up you set that up with a five second SP configure where I can. The deadlock monitor stuff just runs automatically. You know, you don’t really need to set a threshold there. If SQL Server hits a deadlock and it can log it, it will. That’s pretty much it. There are some platform differences. In AWS RDS, SP configure is not directly available the way that it is in many other SQL Server platforms. It is available via an RDS parameter group.

So if you are using this to monitor RDS, you will need to set things there. In Azure SQL DB, it is fixed at 20 seconds. I don’t know what Microsoft’s scared of, but they set it up pretty high. So the block process report will sweep through every 20 seconds, look for blocking. And if it happens to catch any, it’ll show it in there. With both of these, though, wherever the reports are collected, you can download them and do whatever you want with them. You can send them to someone. You can, you know, put them in another tool that parses them out, whatever you want to do. If you want to check out this awesome free SQL Server monitoring tool, again, go to code.erikdarling.com. It is in the performance monitor repo.

And if you go to the releases section, you’ll find nightly releases and the latest sort of stable build release. So whichever one you’re feeling, you’re feeling cool with testing out, you can, you can grab and start monitoring your SQL Server’s performance for free. So let’s look at these tabs, just so you get sort of a sense of what you’re dealing with here.

So I set this back 30 days because apparently I haven’t done anything interesting on my server in a little bit. But if you look in the sort of locking trends tab, this will show you lock, just lock weights as they occurred. This will give you sort of the count of blocking and deadlocking events.

And this will, and these two graphs down here will sort of start to show you the durations of them. So where these durations get higher and higher, you had bigger and bigger problems. I’ll try to move out of the way of the deadlock one.

So you can see in here, this thing spikes up when we had a bunch of deadlocks. This current weights tab is sort of an interesting one. So I’m working on sort of how to get this across the best. Because like if you look at this, you know, you have some, like you have lock weights down here that are hitting, you know, about 45 seconds.

But that’s very low. And then over here, you have some absolutely tremendous lock weights, right? I mean, look at this, right?

That’s 974,000 milliseconds. And then this thing up here, LCKMIX for 2 million milliseconds. So some of this data is a little hard to get, like, good perspective on. So I’m working on how to best visualize that.

I need to figure out maybe a little bit better way of doing that. But for now, if you see lines go up very high and you see numbers into the millions here, that’s probably not a good sign, right? I think it’s a challenge for anyone designing charts and graphs is how to deal with these extreme outliers, like 975,000 milliseconds and 2.6 million milliseconds.

They are challenging to deal with from a visualization perspective. But I will try to figure something out. It’s like even if you look in here, where it’s like LCKMIS, 219,000 milliseconds, that’s a brave number of milliseconds.

But down in here, I break down blocked sessions by database, right? So, like, the primary ones in here that are – go away, Visual Studio. No one needs you.

So the primary ones in here are HammerDB. That’s in green, right? So HammerDB TPCC had a lot of blocking going on at some point. And then the HammerDB TPCH database also had a bunch of blocked sessions in it. So I do try to point out not only, like – I try to, like, give you better, more granular breakdown of which databases have the most blocking in them so you can make decisions about which ones to sort of troubleshoot.

Over here are the shredded block process and deadlock reports. So if we look under blocking here, you know, it’ll be all the sort of normal stuff that you would expect to see if you were to run SPHumanEventsBlockViewer. You know, you see the blocking chain in here.

You can figure – like, you know, it’s a very similar experience to running that tool, the query text. And if you want to download the block process report XML, you can do that very easily there. And then under deadlocks, you’ll see very, very similar to what you would get back from SPHumanEventsBlock because guess what?

I run SPHumanEventsBlock to do the parsing, right? Because it’s a lot easier than having to rewrite code that I wrote the first time again. So – oh, don’t run away from me.

Who do you think you are? Silly goose. Anyway, just a quick overview of the blocking and deadlocking stuff in my free SQL Server monitoring tool. If you want to get a hold of this and start monitoring your SQL Servers for free, again, that is all at code.erikdarling.com.

You can go in, start getting information, start troubleshooting SQL Server. And if you like this project, you appreciate this project, and you would like to sponsor it, you can absolutely do that. Or if you start looking at all this monitoring data and you say to yourself, hey, I’m in way over my head, you can always call me because I’m still – I still am doing a consult, and I can still help you with your SQL Server performance problems.

So thank you for watching. Hope you enjoyed yourselves. Hope you learned something, and I will see you in tomorrow’s video where we will dig a little bit more into some of the foundations and fundamentals in my free SQL Server performance monitoring tool.

Alright, thank you for watching.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Related Posts