sp_blitzlock returns blank data in SQL Managed instance

Question

I have a SQL Managed Instance in Azure with some blocking/deadlocking going on. This DB was on-premise and had all the scripts installed, so I uninstalled them and then installed the Azure specific scripts (Install-Azure.sql), downloaded fresh from Brent Ozar's website.

All the scripts seem to work ok, except sp_blitzlock. SpBlitz indeed confirms "94 average deadlocks per day. To find them, run sp_BlitzLock."

I am running it as a system administrator, and both main tables are blank. (when I ran it as my EntraID account, I got a permissions issue: Msg 50000, Level 11, State 1, Procedure sp_blitzlock, Line 335 [Batch Start Line 0] A session with the name system_health does not exist or is not currently active.

I do not get any error when running it as an instance sysadmin. The data is just blank. It's possible there's no data, but I doubt it given what we've observed elsewhere and what sp_blitz confirmed.

I downloaded the latest First Responder Kit, and am using sp_BlitzLock version 8.21 and sp_Blitz version Jul 1 2024 12:00AM. This is a SQL Managed Instance in Azure, instance version is 12.0.2000.8. I am just executing sp_blitzlock; in SSMS to run it.

It may be worth pointing out we recently upgraded the service tier from General Purpose to Business Critical. That overall improved performance and locking, but I'm wondering if it triggered a new instance behind the scene which is why no deadlocking data appears. (Although why would it show up in sp_blitz?)

Any thoughts?

Answer 1

microsoft hates you

Reading from event files in Azure SQLDB or Managed Instance is a colossal, monumental, and spiteful pain in the sitting region, otherwise known as Sparkling Derriere.

Because of that, sp_BlitzLock can only reliably get data from the ring_buffer portion of the system_health extended event. Since that's in-memory (ephemeral, as the kids say), it may clear out an inopportune times, and you may not be able to get the data you need from it.

You can attempt to set sp_BlitzLock up in an Agent job to run and regularly poll it for helpful contents, and push those to a table for proper digestion. It may still miss things if it runs after that memory is cleared out, though.