Press "Enter" to skip to content

Category: Locks, Blocks, and Deadlocks

Querying Deadlocks in Azure SQL DB

Josephine Bush wants to find the deadlocks:

A couple of weeks ago, a developer came to me and wanted to know how to figure out what was causing a deadlock. I honestly didn’t know where to look or if this was even being captured in Azure SQL DB already. It turns out that Microsoft has you covered with deadlock tracking. At least for a period of time. It looks like you can go back about a month, maybe.

Read on to see how you can find this information in Azure SQL DB. If you’re working in on-prem SQL Server and you don’t have any tooling set up, you can find some deadlocks in the system health extended event.

Leave a Comment

Optimized Locking in Azure SQL Database

Bob Pusateri locks the door:

One of the newer features introduced in Azure SQL Database is “Optimized Locking”. But what does that mean, and how does it compare to the apparently “un-optimized” locking the SQL relational engine has been using up to this point? Let’s dive in and take a look.

Read on to learn more about optimized locking and how it compares to the traditional form of locking available in SQL Server today.

Comments closed

Dealing with Page Latch Weights

Jared Poche talks latches:

Reducing waits is a great way to improve the performance of your SQL Servers. Minimizing PAGELATCH_EX and PAGELATCH_SH wait types are more involved than most. There are generally two causes; one of which is largely solved in recent versions, and one which requires real thought and planning to resolve.

Read on for both of those causes, as well as several tips you can use to minimize the risk of waiting.

Comments closed

Troubleshooting sp_getapplock Blocking

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.

Comments closed

Troubleshooting Performance around a Data Purge Process

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

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

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