How I Use sp_BlitzLock To Investigate SQL Server Deadlocks
Vložit
- čas přidán 2. 06. 2023
- GitHub: firstresponderkit.org/
If you like what you see here, you'll love my advanced performance tuning training:
training.erikdarling.com/?cou... - Věda a technologie
Great tool.used a lot!thank you,Erik!
Heck yeah.
I'd been using Jeremiah's extended event script that writes to a file with Michael Swart's parser to keep track of history. I'll have to give sp_blitzlock a shot with the external file from that. Some of the external files I'd gotten in the past were over 3GB for a few hours on some of the troublesome DBs.
Reading from those files can be real rough. I’m not sure which was you’d be better off.
@@ErikDarlingData The files are quite painful to read at certain sizes. They did capture detail I needed at the time. It was only intended for escalating issues in real time, not a long term historical review. I set it up to purge files older than a few days and restart the event daily. Nothing quite as elegant as your logging but it wasn't a permanent solution. I simply needed evidence that the implicit transactions in a "cart" that was left abandoned by users in a JDBC connection was at fault. Apparently they fixed it after many months but didn't bother explaining how. They wouldn't get into details.
That likely means they (surprise!) ran to the vendor or did something sketchy that hasn't bit them yet. (It's a recurring pattern)
As an aside, Andy Mallon had something usable for emailing alerts on deadlocks but I never got to the point of a production deployment. It is a neat little bit of code so I've saved it off for future issues.
Have you used this with Azure SQL Managed Instance? Anything needed to point at where the XEvent info is stored on Azure?
Azure MI is a disaster when it comes to XE. You can point sp_BlitzLock to the ring buffer element of the system health extended event, but it's not guaranteed to have anything useful in it. Querying XE files there is of course damn near impossible.