Actual Emails: Is "NOLOCK is the epitome of evil?"

Sent: Friday, January 20, 2012 10:30 AM
To: William Assaf
Subject: dba has a question

William,

Can you send me references to support my contention that NOLOCK is the epitome of evil?

That is, that it can return invalid and/or unrelated data.

He is still suggesting that folks use NOLOCK (rather than READPAST).

We have to work around other poorly written queries, so some of these are inevitable.

Thanks,

[my colleague's name redacted to protect the innocent]

View execution plan for a sproc in the wild

Got stored procedure problems?  I feel sorry for you son.

Here's a quick query to pull the cached execution plan out of memory.

I italicized cached because it's not going to stay there forever.

I used this query to find the execution plan of a stored proc that had been running in a SQL job.  Instead of pulling down the sproc and trying to estimate the query plan, I chose to see what the cached plan looked like.

Open_transaction_count in sys.dm_exec_sessions

This news may be a few months old, but it is worth noting that there is now a column called open_transaction_count in the sys.dm_exec_sessions in SQL Server 2012.

This should put away any need for you to use sys.sysprocesses for session information.