As If
Replication is one of my least favorite things, and I go out of my way not to deal with it. I have had a few clients now that have run into problems with deadlocks arising from it doing …
Category: Deadlock
Locking Hints Make Everything Confusing
King Of The DMV
Many people will go their entire lives without using or seeing a lock hint other than NOLOCK.
Thankfully, NOLOCK only ever leads to weird errors and incorrect results. You’ll probably never have to deal with the …
Lookups, Deadlocks, and You
You Come And Go
I go back and forth when it comes to Lookups.
On the one hand, I don’t think the optimizer uses them enough. There are times when hinting a nonclustered index, or re-writing a query to get …
Which Parallel Deadlocks Produce Deadlock Graphs?
Sadness
There are two types of parallel deadlocks. The kind that end in an error message, and the kind that are resolved by exchanges spilling buffers.
It used to be that both kinds would produce deadlock graphs. Microsoft even added …
Options For Fixing Parallel Deadlocks
Recreating
If you have a copy of the StackOverflow2013 database, this query should produce a parallel deadlock.
SELECT c.Id, c.UserId, c.CreationDate, c.PostId, c.Score INTO #ohno FROM dbo.Comments AS c WITH (TABLOCKX);
If you want an easy way to track down …
Deadlock Graph Frustrations
Money In The Bank
Capturing deadlock graphs has come a long way since… 2008? You used to need clunky trace flags, that for some reason would dump information line by line into the error log.
Nowadays, you can get a …