Deadlocks

You know what your deadlock graphs need? Animation.

February 21, 2019

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

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.”

October 11, 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

May 17, 2018

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?

April 24, 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…

Column Store Deadlocks: Missing Information

April 6, 2018

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

December 19, 2017

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…