Big Ideas
The more I used third party monitoring tools, the more annoyed I get. So much is missing from the details, configurability, and user experience.
I often find myself insisting on also having Query Store enabled. As much as …
Category: Deadlock
Unkillable Sessions and Undetected Deadlocks
I recently experienced a blocking issue in a production environment which had been going on for hours. As a responsible DBA, I tried to kill the head of the blocking chain. Unexpectedly, killing the session seemed to have no effect. …
How To Tell You Need An Optimistic Isolation Level In SQL Server
Into The Yonder
When you create a database in SQL Server (everything except Azure SQL DB), you get this garbage isolation level called Read Committed.
That isolation level, along with others like Repeatable Read and Serializable, are considered pessimistic. …
Replication Deadlocks In SQL Server, And How To Fix Them
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 …
Locking Hints Make Troubleshooting Blocking And Deadlocks Confusing In SQL Server
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 …
Why Read Queries Deadlock With Write Queries In SQL Server
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 In SQL Server?
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 Query Deadlocks In SQL Server
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 …
Using Lock Timeouts To Avoid Deadlocks In SQL Server
Try And Retry
I’ll sometimes see people implement retry logic to catch deadlocks, which isn’t a terrible idea by itself. The problem that may arise is when the deadlock monitor takes a full 5 seconds to catch a query, which …
Deadlock Graph Frustrations In SQL Server
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 …