But Why?
As a procedure, sp_WhoIsActive is pretty perfect. It’s not all things to all people, but it does what it’s supposed to do really well.
One thing I really like about it is that you can change a couple …
Category: Locking
This OVERPOWERED Hint Will Break NOLOCK Hints In SQL Server
G4M3R
CREATE TABLE
dbo.view_me
(
id int NOT NULL
);
GO
CREATE VIEW
dbo.viewed
AS
SELECT
vm.*
FROM dbo.view_me AS vm WITH(READCOMMITTED);
GO
SELECT
v.*
FROM dbo.viewed AS v WITH(NOLOCK);
GO
…Msg 4138, Level 16, State 1, Line 22
Conflicting
Should Query Store Also Capture Blocking And Deadlocks?
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 …
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 …