Live classes start in 4d 08h 03m — Save your seat

Category: Locking, Blocking, and Isolation Levels

Performance Tuning

How Computed Columns Can Cause Blocking

April 26, 2018

The short story: when you add a computed column that references another table, like with a scalar user-defined function, you can end up causing concurrency problems even when people didn't really want to go see that other table, and that table is locked by someone else.

Here's my query:
[crayon-6ae299b074fb4442920671/]
Here's what I occasionally see when the query runs, using sp_BlitzWho:

Read more about How Computed Columns Can Cause Blocking 5 comments — Join the discussion
Performance Tuning

The Many Levels Of Concurrency

February 19, 2018

"Concurrency is hard"

When designing for high concurrency, most people look to the hardware for answers. And while it's true that it plays an important role, there's a heck of a lot more to it.
Start Optimistic
Like, every other major database vendor is optimistic by default. Even the free ones. SQL Server is rather lonesome in that, out of the box, you get the Read Committed isolation level.

Read more about The Many Levels Of Concurrency 7 comments — Join the discussion
Performance Tuning

Query Tuning Week: What’s the Difference Between Locking and Blocking and Deadlocking?

August 18, 2016

If Erik takes out a lock on a row,
and no one else is around to hear it,
that's just locking.

There's nothing wrong with locking by itself. It's perfectly fine. If you're the only person running a query, and you have a lot of work to do, you probably want to take out the largest locks possible (the entire table) in order to get your work done quickly.

Read more about Query Tuning Week: What’s the Difference Between Locking and Blocking and Deadlocking? 15 comments — Join the discussion
Performance Tuning

Does Creating an Indexed View Require Exclusive Locks on an Underlying Table?

October 1, 2015

An interesting question came up in our SQL Server Performance Tuning course in Chicago: when creating an indexed view, does it require an exclusive lock on the underlying table or tables? Let's test it out with a simple indexed view run against a non-production environment. (AKA, a VM on my laptop running SQL Server 2014.)…

Read more about Does Creating an Indexed View Require Exclusive Locks on an Underlying Table? Be the first to comment
Performance Tuning

Staging Data: Locking Danger with ALTER SCHEMA TRANSFER

May 12, 2015

Developers have struggled with a problem for a long time: how do I load up a new table, then quickly switch it in and replace it, to make it visible to users?

There's a few different approaches to reloading data and switching it in, and unfortunately most of them have big problems involving locking. One method is this:

Read more about Staging Data: Locking Danger with ALTER SCHEMA TRANSFER 16 comments — Join the discussion
Performance Tuning

Can DBCC SHRINKFILE Cause Blocking in SQL Server?

April 10, 2015

It sure can.

The lock risks of shrinking data files in SQL Server aren't very well documented. Many people have written about shrinking files being a bad regular practice-- and that's totally true. But sometimes you may need to run a one-time operation if you've been able to clear out or archive a lot of data. And you might wonder what kind of pains shrinking could cause you.

Read more about Can DBCC SHRINKFILE Cause Blocking in SQL Server? 40 comments — Join the discussion
Performance Tuning

Testing ALTER INDEX REBUILD with WAIT_AT_LOW_PRIORITY in SQL Server 2014

January 14, 2015

One of the blocking scenarios I find most interesting is related to online index rebuilds. Index rebuilds are only mostly online. In order to complete they need a very high level of lock: a schema modification lock (SCH-M).

Here's one way this can become a big problem:

Read more about Testing ALTER INDEX REBUILD with WAIT_AT_LOW_PRIORITY in SQL Server 2014 14 comments — Join the discussion
Performance Tuning

Read Committed Snapshot Isolation: Writers Block Writers (RCSI)

December 22, 2014

When learning how Read Committed Snapshot Isolation works in SQL Server, it can be a little tricky to understand how writes behave. The basic way I remember this is "Readers don't block writers, writers don't block readers, but writers still block writers." But that's not so easy to understand. Let's take a look at a simple test…

Read more about Read Committed Snapshot Isolation: Writers Block Writers (RCSI) 20 comments — Join the discussion
Performance Tuning

Capturing Deadlocks in SQL Server

June 11, 2014

What's a deadlock? Well, let's say there's a fight going on between Wonder Woman and Cheetah, and, in the same room, a fight between Batman and Mr. Freeze. Wonder Woman decides to help Batman by also attempting to throw her lasso around Mr. Freeze; Batman tries to help Wonder Woman by unleashing a rope from the grappling gun at Cheetah. The problem is that Wonder Woman already has a lock on her opponent, and Batman has his. This would be a superhero (and super) deadlock.

Read more about Capturing Deadlocks in SQL Server 33 comments — Join the discussion
Performance Tuning

Finding Blocked Processes and Deadlocks using SQL Server Extended Events

March 12, 2014

A lot of folks would have you think that Extended Events need to be complicated and involve copious amounts of XML shredding and throwing things across the office. I'm here to tell you that it doesn't have to be so bad. Collecting Blocked Process Reports and Deadlocks Using Extended Events When you want to find…

Read more about Finding Blocked Processes and Deadlocks using SQL Server Extended Events 64 comments — Join the discussion
Performance Tuning

Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide

January 7, 2013

Your queries won’t tune themselves. My 2027 Live Class Season Pass is $995 (normally $4,395), with live Fundamentals and Mastering classes plus recordings. Register by October 31 to join Fundamentals Week, November 2–6. See the pass.

A client said the coolest thing to me the other day. He said, "We talked before about why we would want to start using optimistic locking in our code. How do we get there?"

Read more about Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide 155 comments — Join the discussion
Performance Tuning

Five Ways to Fight Blocking Video

August 17, 2011

What do *you* do when your database has a problem with blocking? Most people reach for the “NOLOCK” hint. Bad news: that may introduce other problems, and it doesn’t truly resolve the root problem in your application code. In this video Kendra Little summarizes five big-picture application changes that fight blocking long term.

This 300 level talk is designed for developers and DBAs who understand table structures, index structures, and common query patterns. We're going to keep things general: no demos, just concept discussions.

Read more about Five Ways to Fight Blocking Video 16 comments — Join the discussion