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: Locking
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. …
Is Read Uncommitted (NOLOCK) An Optimistic Or Pessimistic Isolation Level?
Poll Worker
I ran a Twitter poll recently, because as I was writing another blog post, I wasn’t quite sure how to group uncommitted/nolock between pessimistic or optimistic isolation levels.
…read uncommitted is what kind of isolation level:
— Erik
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. …
Blocking on Columnstore Indexes that RCSI and NOLOCK Don’t Resolve
I recently ran into a production issue where a SELECT query that referenced a NOLOCK-hinted table was hitting a 30 second query timeout. Query store wait stats suggested that the issue was blocking on a table with a nonclustered columnstore …
My SQL Server Query Ran For A Long Time But Didn’t Use A Lot Of CPU: What Happened?
Of Walls And Clocks
No one ever says a broken record is right twice a day, perhaps because DJs are far more replaceable than clock makers.
I say that only to acknowledge that I may sound like a broken record …
Steps For Getting Rid Of NOLOCK Hints In SQL Server Queries
Way Out
Whenever I see people using NOLOCK hints, I try to point out that they’re not a great idea, for various reasons explained in detail all across the internet.
At minimum, I want them to understand that the hint …
Implicit Transactions: Why Unrelated Queries Block Each Other In SQL Server
A Bit Sensational
I don’t really mean that unrelated queries block each other, but it sure does look like they do.
Implicit Transactions are really horrible surprises, but are unfortunately common to see in applications that use JDBC drivers to …
Normalizing SQL Server Tables To Reduce Query Blocking
Chalky
I see a lot of tables that look something like this:
CREATE TABLE dbo.orders
(
order_id int NOT NULL PRIMARY KEY
DEFAULT (NEXT VALUE FOR dbo.order_id),
order_date datetime NOT NULL,
order_ship_date datetime NOT NULL,
order_total money NOT NULL,
order_tax … 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 …