Deadlocks

Free Video: Deadlocks: Let’s Do One, Understand It, and Fix It

Last Updated May 10, 2021

You keep getting warnings and emails about deadlocks, but let’s be honest: you’re not really sure how they happen or what to do about it. In this one-hour session from the SQLBits conference, I will show one, use sp_BlitzLock to analyze it, then fix it. If you like this video, check out the SQLBits YouTube…

You know what your deadlock graphs need? Animation.

Last Updated February 21, 2019

Brent Ozar

Deadlocks

In SQL Server Management Studio, plain ol’ deadlock graphs look like this: Two thumbs down, would not lock the dead again BOOOOO-RING. That’s why I prefer opening deadlock graphs in SentryOne Plan Explorer, which presents a much better visual in the form of a circle…a circle, let’s just stop there: Deadlock graph in SentryOne Plan…

How to Troubleshoot Blocking and Deadlocking with Scripts and Tools

Last Updated November 28, 2018

Brent Ozar

Deadlocks, Monitoring, sp_BlitzLock

When you need to find which queries are blocking other queries, your decision comes down to when the blocking happened. Is it happening now, recently, or coming up soon? During a live emergency, start with sp_WhoIsActive. Adam Machanic’s excellent free sp_WhoIsActive replaces Activity Monitor, sp_who, and sp_who2, and it’s way more powerful. The documentation is extensive –…

Index Tuning Week: Getting Blocking? Play “Hot or Not.”

Last Updated September 28, 2018

Brent Ozar

Deadlocks, Indexing

This week, we’re all about tuning indexes. So far, we’ve covered Brent’s 5 and 5 Rule and The D.E.A.T.H. Method. Today, let’s talk about reducing blocking and deadlocking. Normally, when we think about the causes of blocking or deadlocks, we use badly written queries that modify different tables in the wrong order, resulting in a Mexican…

How to Create Deadlocks and Troubleshoot Them

Last Updated February 18, 2020

Brent Ozar

Deadlocks

Watch in awe and wonder as I create a deadlock, then use sp_BlitzLock after the fact to show you which queries and tables were involved: Here are the scripts to run in the left hand window: Transact-SQL CREATE TABLE dbo.Lefty (Numbers INT PRIMARY KEY CLUSTERED); INSERT INTO dbo.Lefty VALUES (1), (2), (3); CREATE TABLE dbo.Righty…

Concurrency Week: Can SELECTs Win Deadlocks?

Last Updated April 23, 2018

Brent Ozar

Deadlocks

Yes, especially when they’re part of a larger transaction. In this example, I’m going to use a database in Read Committed Snapshot Isolation (RCSI), my favorite isolation level for building new applications from the ground up. Optimistic concurrency (aka MVCC) helps avoid a lot of blocking issues – but not all of them. In a…

Why sp_BlitzLock Can’t Show Complete Columnstore Deadlock Information

Last Updated June 25, 2021

Erik Darling

Deadlocks

People seem to care about deadlocks That’s why I wrote sp_BlitzLock. Why not sp_BlitzDeadlock? Well, I had this song stuck in my head. But enough about that. While poking around with things recently, I created a pretty typical deadlock on a table, but this one had a clustered column store index on it. Of course,…

Introducing sp_BlitzLock: For Troubleshooting SQL Server Deadlocks

Last Updated March 2, 2021

Erik Darling

Deadlocks, First Responder Kit, sp_BlitzLock

Deadlocks are hard Especially when there are a lot of them. When there are dozens or more, looking at each graph is a pain. I had a script written that parsed out the XML, but really, having a folder full of random scripts is for old people. Like Twitter and reverse mortgages. So I turned…