Press "Enter" to skip to content

Category: Locks, Blocks, and Deadlocks

Troubleshooting sp_getapplock Blocking

Published 2024-04-25 by Kevin Feasel

Brent Ozar has an epiphany:

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 = ‘duration’ showed a couple queries with the “Long Running, Low CPU” warning, and they did indeed have blocking involved, but … pretty minor stuff. Plus, their plan cache was nearly useless due to a ton of unparameterized queries pouring through constantly, overwhelming Azure SQL DB’s limited plan cache.
  • sp_Blitz wasn’t reporting any deadlocks, either. (sp_BlitzLock doesn’t work in Azure SQL DB at the moment because Microsoft’s no longer running the default system health XE session up there. They turned that off in order to save money on hosting costs, and passed the savings on to… wait… hmm)
  • As a last-ditch hail-Mary, I ran sp_BlitzWho repeatedly, trying to catch the blocking happening in action. No dice – the odds that I’d catch it live weren’t great anyway.

Click through for the story and how all the pieces ultimately fit together.

Leave a Comment

Troubleshooting Performance around a Data Purge Process

Published 2024-02-14 by Kevin Feasel

Andy Mallon troubleshoots an issue:

In January, one of our Staff Engineers sent the following message to the DBRE help channel in Slack:

Morning folks, we had a pretty significant wait spike on the [database]. Circuit breakers closed and reopened quickly. Is anyone immediately aware of a reason why this could’ve happened?

Read on for Andy’s quick analysis and then the root cause and solution.

Comments closed

Latches vs Locks

Published 2024-02-08 by Kevin Feasel

Chad Callihan disambiguates two terms:

You may hear or read about locks and latches and assume these terms are being used interchangeably. After all, if you search for synonyms for the word “lock”, you’ll find “latch” as a potential substitute:

In database terms, lock and latch are similar but have unique purposes of their own. It’s not as simple as “you say toMAYto, I say toMAHto.”

Click through for the difference and a few details on latches.

Comments closed

When Online Index Rebuilds Aren’t

Published 2024-02-08 by Kevin Feasel

Kendra Little finds a bug:

I found a nasty bug in SQL Server and Azure SQL Managed Instance recently: sometimes an “online” index rebuild of a disk-based rowstore clustered index (basically a normal, everyday table) isn’t actually “online”. In fact, it’s very OFFLINE, and it blocks both read and write queries against the table for long periods.

If you manage to make it through a rebuild successfully, the problem goes away for future rebuilds of that clustered index – likely leaving you bruised and bewildered.

Click through for the details, including repo scripts and explanation of what should happen in this case.

Comments closed

Using Application Locking to Prevent Deadlocking

Published 2024-01-23 by Kevin Feasel

Andy Brownsword shares a tip:

Deadlocks are an enduring feature of SQL Server. They’ve been a source of pain for many over the years and there are various ways to diagnose, mitigate or resolve them. Here I want to demonstrate approach I haven’t seen discussed – using an application lock to segregate processes.

An example where I’ve used this effectively was for a queue table where work would be placed and a number of processes would nibble away at it throughout the day. Separate to this was a maintenance routine which ran each evening to manage partitions on the table. The maintenance job would kick in and would deadlock all those processes.

This was solved with an Application Lock.

Read on to learn more about application locks, how they work, and why they fixed the problem in this scenario. Generally, this happens when two operators access two resources in different orders. For example stored procedure A has a transaction which locks table 1 and then locks table 2 and then commits. Meanwhile, stored procedure B has a transaction which locks table 2 and then locks table 1 and then commits. The classical solution is to fix the ordering such that both are consistent. But not all deadlocking behavior is that straightforward or that simple, and so other solutions like app locks can be quite helpful to know about.

Comments closed

Blocking Trees in sp_HumanEventsBlockViewer

Published 2023-08-04 by Kevin Feasel

Erik Darling talks up a famous Canadian:

I know there’s been a lot of blog content about the creation of and updates to my stored procedures lately. I try to consolidate as much as possible, but this one comes from Valued Contributor©.

The one, the only, the Canadian: Michael J Swart (b|t) recently contributed a really cool piece of code that I’ve been dying to have since I created sp_HumanEventsBlockViewer: the entire blocking chain is visualized in the results.

Click through to see what it looks like, and next time you see Michael, give him a thumbs up.

Comments closed

Fending off Sessions while in Single-User Mode

Published 2023-04-03 by Kevin Feasel

Eitan Blumin just wants to switch the database type:

Today we had an interesting use case where a customer reported that one of the databases they just restored from a backup got stuck in “Single-User” mode in one of their environments.

To resolve it, I first tried running the following command:

ALTER DATABASE MyDB SET MULTI_USER WITH ROLLBACK IMMEDIATE;

In response, I got deadlocked with the dreaded error 1205:

There were a few different attempts with no success until Eitan came up with the final script. Eitan’s analogy was to curling, though the first thing I thought of was Odysseus fighting off his wife’s suitors as he came back to claim his home.

1 Comment

Finding Blockers in Azure SQL DB + MI

Published 2022-12-28 by Kevin Feasel

Jose Manuel Jurado Diaz writes a program:

Today, I worked on a service request that our customer is looking for all blocking issues that is happening in their database. We have many articles about it Lesson Learned #22: How to identify blocking issues? – Microsoft Community Hub and in Diagnostics Settings and QDS we can collect this information but all points to that we cannot see the TSQL that is blocking and TSQL command that is blocked in an easy way. In the following script that I share as a script example we could take this one. 

First of all, please, remember that a blocking issue is normal and fundamental for any RDBMS. This script is basically when you need to understand what is happening to improve or reduce this.

I was a little surprised the answer wasn’t to use Extended Events, though this does work if you simply need something to run in ad hoc scenarios.

Comments closed

RCSI and Blocking

Published 2022-11-28 by Kevin Feasel

Michael J. Swart says don’t worry, be happy:

What’s the best way to avoid most blocking issues in SQL Server? Turn on Read Committed Snapshot Isolation (RCSI). That’s it.

Do check out Erik Darling’s comment as well for one thing to keep in mind if you turn on RCSI.

The other thing to keep in mind is that, if you have WITH(NOLOCK) hanging around everywhere in your code, you won’t get as much of a benefit with RCSI until you remove them.

Comments closed