I came across a fun deadlock when writing demos for my session on the Read Committed isolation level this week. (It’s OK to call it “fun” when it’s not production code, right?)
I was playing around with a nonclustered columnstore index on a disk-based table. Here’s what I was doing:
The free sp_WhoIsActive procedure by Adam Machanic can be very useful for monitoring SQL Server: I’m a big fan.
But I’ve learned the hard way that it’s easy to collect too much information with sp_WhoIsActive, particularly if you set up jobs where you have the procedure log data to a table for later review. Collecting the text of SQL Server queries, their execution plans, and more, can take up a lot of room, and I’ve managed to fill up a server drive or two with a little over-eager collection. Whoops!
At the beginning of the “Troubleshooting Blocking and Deadlocks” course, I mention that it took me a long time to get into using the tools I show in the course.
The tools are all free, and many of them are built into SQL Server.
Update: the course itself is now free, too!
Copyright (c) 2025, Catalyze SQL, LLC; all rights reserved. Opinions expressed on this site are solely those of Kendra Little of Catalyze SQL, LLC. Content policy: Short excerpts of blog posts (3 sentences) may be republished, but longer excerpts and artwork cannot be shared without explicit permission.