Press "Enter" to skip to content

Category: Locks, Blocks, and Deadlocks

The Costs and Benefits of Dirty Reads

Published 2021-08-03 by Kevin Feasel

Chad Callihan explains what a dirty read is and does a cost-benefit analysis on it:

When you are not careful with your transaction isolation levels or you get sneaky with the NOLOCK hint, one problem you can encounter is a dirty read. Let’s look at a short example to demonstrate a dirty read.

In a vacuum, I’m not necessarily opposed to the idea of dirty reads because you can find legitimate cases in which they can be useful. In practice, I’m generally very much in opposition because of two reasons: first, Read Committed Snapshot Isolation eliminates the majority of those reasons; and second, because the misuse is almost always in the direction of over-use of NOLOCK hints.

Comments closed

When DBCC_OBJECT_METADATA becomes a Bottleneck

Published 2021-07-28 by Kevin Feasel

Paul Randal takes us through another latch:

Continuing my series of articles on latches, this time I’m going to discuss the DBCC_OBJECT_METADATA latch and show how it can be a major bottleneck for consistency checks prior to SQL Server 2016 under certain circumstances. The issue affects DBCC CHECKDB, DBCC CHECKTABLE, and DBCC CHECKFILEGROUP, but for clarity I’ll just reference DBCC CHECKDB for the rest of this post.

You might wonder why I’m writing about an issue that affects older versions, but there are still a huge number of SQL Server 2014 and older instances out there, so it’s a valid topic for my series.

Read on to understand what DBCC_OBJECT_METADATA does and how it can become a bottleneck on those older versions of SQL Server.

Comments closed

FGCB_ADD_REMOVE Latch

Published 2021-06-22 by Kevin Feasel

Paul Randal looks at a particular latch:

Most latch class names are tied directly to the data structure that they protect. The FGCB_ADD_REMOVE latch protects a data structure called an FGCB, or File Group Control Block, and there will be one of these latches for each online filegroup of each online database in a SQL Server instance. Whenever a file in a filegroup is added, dropped, grown, or shrunk, the latch must be acquired in EX mode, and when figuring out the next file to allocate from, the latch must be acquired in SH mode to prevent any filegroup changes. (Remember that extent allocations for a filegroup are performed on a round-robin basis through the files in the filegroup, and also take into account proportional fill, which I explain here.)

Read on to understand what can cause this particular latch to become a bottleneck in your system.

Comments closed

Foreign Key Constraints and Blocking

Published 2021-06-18 by Kevin Feasel

Paul White takes a look at blocking due to foreign key checks:

This article covers one such consideration that does not receive much publicity: To minimize blocking, you should think carefully about the indexes used to enforce uniqueness on the parent side of those foreign key relationships.

This applies whether you are using locking read committed or the versioning-based read committed snapshot isolation (RCSI). Both can experience blocking when foreign key relationships are checked by the SQL Server engine.

Under snapshot isolation (SI), there is an extra caveat. The same essential issue can lead to unexpected (and arguably illogical) transaction failures due to apparent update conflicts.

This article is in two parts. The first part looks at foreign key blocking under locking read committed and read committed snapshot isolation. The second part covers related update conflicts under snapshot isolation.

Definitely worth reading the whole thing.

Comments closed

The Basics of Finding Blocking

Published 2021-05-17 by Kevin Feasel

Alex Stuart has a way to find blocked processes:

So we need monitoring and alerting on it. Enterprise monitoring tools can do this, and do it well – but if you don’t have one, or don’t have enough licenses for your entire estate, you’ll need to roll your own. (OK, or copy someone else’s if you don’t need the learnin’). This post will demonstrate a basic method for detecting blocking and alerting based on a given threshold.

Read on for the process.

Comments closed

When RCSI Is Not Enough

Published 2021-05-11 by Kevin Feasel

David Klee diagnoses an issue:

Basically, the use of the WITH (NOLOCK) query hint performs a dirty read, of which I’m sure you can find oodles of examples on the Internet about. Microsoft introduced RCSI in SQL Server 2005 to help you reduce the amount of blocks, and with RCSI exclusively in use, database readers no longer block other readers or writers. I love enabling RCSI wherever appropriate, as long as the TempDB database is monitored to make sure that RCSI’s version store is not causing any issues (and it usually is just fine). It also goes without saying that with RCSI enabled, you should work to remove the WITH (NOLOCK) query hints from your code, as RCSI is superseded by NOLOCK and can still invoke dirty reads.

But… this Client is still seeing serializable connections that will occasionally cause issues. What else can we check?

Read on for a few tips.

Comments closed

An Introduction to Latches

Published 2021-05-03 by Kevin Feasel

Paul Randal starts a series on latches:

In some of my previous articles here on performance tuning, I’ve discussed multiple wait types and how they are indicative of various resource bottlenecks. I’m starting a new series on scenarios where a synchronization mechanism called a latch is a performance bottleneck, and specifically non-page latches. In this initial post I’m going to explain why latches are required, what they actually are, and how they can be a bottleneck.

Read on to learn what a latch is, why it is useful, and how latches work at a high level.

Comments closed

Capturing Deadlocks with the system_health Extended Event

Published 2021-04-19 by Kevin Feasel

Jack Vamvas is hunting deadlocks:

An application using SQL Server as the database backend was experiencing some application rollbacks. I decided to investigate the SQL Server to identify any errors which could be correlated to the application timeouts experienced by the users. 

I started reviewing the errors in the Extended Events system health logs, which are normally running by default on a SQL Server. They have a ton of useful information . I noticed a steady stream of deadlocks . This is the code used to create a permanent table to store the deadlock details , for review by the application team. 

Click through for the script.

Comments closed

Schema Modification Lock-Driven Deadlocks

Published 2021-04-13 by Kevin Feasel

Taryn Pratt diagnoses a deadlocking issue:

As expected, with the announcement that Stack Overflow for Teams is free for up to 50 users, we saw an incredible spike in sign-ups. Along with the spike in sign-ups, I started to receive a huge increase in alerts about deadlocks on the primary SQL Server for Teams. In the two weeks it took us to resolve the deadlocks, we hit at least 200 deadlocks.

Click through for the low-down on how they discovered and corrected the issue.

Comments closed