Ugly Bug: SQL Server Online Index Rebuild Sometimes Happens Offline Without Warning

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.

Continue reading

Lost Updates Under Read Committed Snapshot Isolation (RCSI)

I shared an image on social media this week that describes how I feel about isolation levels in SQL Server (and its various flavors): the more concurrent sessions you have in a database reading and writing data at the same time, the more attractive it is to use version-based optimistic locking for scalability reasons.

There are two isolation levels in SQL Server that use optimistic locking for disk-based tables:

1. Read Committed Snapshot Isolation (RCSI), which changes the implementation of the default Read Committed Isolation level and enables statement-based consistency.
2. Snapshot Isolation, which provides high consistency for transactions (which often contain multiple statements). Snapshot Isolation also provides support for identifying update conflicts.

Many folks get pretty nervous about RCSI when they learn that certain timing effects can happen with data modifications that don’t happen under Read Committed. The irony is that RCSI does solve many OTHER timing risks in Read Committed, and overall is more consistent, so sticking with the pessimistic implementation of Read Committed is not a great solution, either.

Continue reading

PAGELATCH, PAGEIOLATCH, and LATCH Waits in SQL Server

I’ve long found it tricky to remember and explain the differences between three similar-sounding waits in SQL Server that all have “LATCH” in the name: PAGELATCH, LATCH, and PAGEIOLATCH waits.

Here’s an illustration that explains these waits, along with wait subtypes.

Continue reading

Free Session! DBA vs Deadlock: How to Out-Index a Deadly Blocking Scenario

DBA vs Deadlock: How to Out-Index a Deadly Blocking Scenario

June 14, 2018 8:30 AM – 9:30 AM PST <- This is past, but no worries, it was recorded

Deadlocks strike fear into the hearts of even seasoned DBAs — but they don’t have to!

In this session, you’ll get the code to cause a sample deadlock in SQL Server. You’ll see how to interpret the deadlock graph to find out where the conflict lies, and how to design an index to make the deadlock disappear.

Continue reading

Query Store Cleanup Can be Part of a Blocking Chain

Forgetfulness can lead to learning something new. This is a bit of a nightmare when it happens in production, but a treat when it happens in an isolated test system– and that’s how I learned this.

I left a bit of blocking open on my test VM, and forgot about it.

Continue reading

New SQLChallenge: Defuse the Deadlock

When I first created SQLWorkbooks, I distilled what it was all about down into one sentence: “Learn SQL Server by Solving Problems.”

This month, I introduce my first SQLChallenge, which distills that down into practice. The SQLChallenge features 23 minutes of video with scripts that you can use to reproduce and solve the problem yourself.

Continue reading

The Case of the Blocking Online Index Create- the Shared Lock that Would Not Quit

I recently got an interesting question from a reader about running a CREATE INDEX statement with DROP_EXISTING

Continue reading