LinkedIn
YouTube
GitHub

Deadlocks

  1. Home
  3. Development
  5. Deadlocks
Brent getting caffeinated

How to Troubleshoot Blocking and Deadlocking with Scripts and Tools

Brent Ozar
Deadlocks, Monitoring, sp_BlitzLock
2 Comments
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 –…
Read More
sp_BlitzLock showing deadlocks

How to Create Deadlocks and Troubleshoot Them

Brent Ozar
Deadlocks
3 Comments
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…
Read More
SQL Server Training by Kalen Delaney

Concurrency Week: Can SELECTs Win Deadlocks?

Brent Ozar
Deadlocks
No Comments
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…
Read More

Column Store Deadlocks: Missing Information

Erik Darling
Deadlocks
6 Comments
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,…
Read More
Menu