Troubleshooting Mysterious Blocking Caused By sp_getapplock

I’m kinda weird. I get excited when I’m troubleshooting a SQL Server problem, and I keep hitting walls.

I’ll give you an example. A client came to me because they were struggling with sporadic performance problems in Azure SQL DB, and nothing seemed to make sense:

sp_BlitzFirst @SinceStartup = 1 showed very clearly that their top wait, by a long shot, was blocking. Hundreds of hours of it in a week. sp_BlitzIndex showed the “Aggressive Indexes” warning on a single table, but… only tens of minutes of locking, nowhere near the level the database was seeing overall. sp_BlitzCache @SortOrder