Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

SNAPSHOT Isolation with (NOLOCK) being used all over Expand / Collapse
Author
Message
Posted Wednesday, November 21, 2012 9:28 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 3:44 PM
Points: 479, Visits: 1,347
Recently, my applications architect decided that we needed to change the level of the database Isolation from the the default READ COMMITTED to SNAPSHOT ISOLATION (basically Read Committed with Snapshot Isolation) saying that it use to be in place, and that over the course of time (and multiple server leases coming to an end and being moved to newer systems) that it had been set back to the default and was causing issues where they had not seen issues before.

Now - the problem that I see with this is that for some reason, someone a while back told our DEVS to start using the WITH(NOLOCK) hint whenever they did a SELECT statement (yes - my eyes are rolling too).

If I have a query and in my FROM line I have applied the (NOLOCK) hint, does it only affect that table's column or will it affect the JOINS below it as well? I am unsure about how this cascades down, or if it even does, and more over - doesn't this bypass the database level Isolation all together (regardless to what it is set to?)

Thanks, and to those of you in the US - Happy Thanksgiving!

SQL_ME_RICH
Post #1387685
Posted Thursday, November 22, 2012 12:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:00 AM
Points: 42,774, Visits: 35,871
Nolock applies just to the table that it's specified on, and it will override the default isolation level as well as any requested with SET TRANSACTION ISOLATION LEVEL


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1387737
Posted Thursday, November 22, 2012 12:00 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 3:44 PM
Points: 479, Visits: 1,347
Thanks for the confirmation on this Gail. Is there any good reason you can think of to always be using the WITH(NOLOCK) hint in all queries? This is not a financial institute I am working with, but they do have some financial record keeping in the various databases, along with time critical items from auction bidding. Wouldn't the WITH(NOLOCK) defeat the whole purpose of discouraging against dirty reads? Or is it such a performance increase that it's worth it to be used indiscriminately? I've read that DEVS will use it to get around back database design on long running queries, but it just seems to me to be a lot like addressing symptoms instead of addressing the real issue of database design.
Post #1387966
Posted Thursday, November 22, 2012 2:27 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:00 AM
Points: 42,774, Visits: 35,871
SQL_ME_RICH (11/22/2012)
Is there any good reason you can think of to always be using the WITH(NOLOCK) hint in all queries?


None whatsoever

Wouldn't the WITH(NOLOCK) defeat the whole purpose of discouraging against dirty reads?


Yup

Or is it such a performance increase that it's worth it to be used indiscriminately?


Nolock is not a performance tuning technique. It's used when it does not matter if the data returned is slightly wrong.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1387986
Posted Friday, November 23, 2012 8:11 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:13 AM
Points: 1,629, Visits: 5,573
I'm no expert, but couldn't the issues your application architect is seeing be due to all the NOLOCKs rather than the isolation level?
Post #1388179
Posted Friday, November 23, 2012 9:50 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 3:44 PM
Points: 479, Visits: 1,347
Paul, that was my whole point to him as well. I'm still fairly new over here, so trying not to rock the boat too much.

Also - if either you or Gail might know this, I would appreciate it. Is there a query that I could run against some of the system objects that would retrieve a time/date stamp for me of when the Isolation Level was set or changed? There is this paranoid theory that someone did this without approval, and they are just wanting to see if there is a way to tell via a time date stamp. I have not found anything to date that will retrieve this from T-SQL, but if you might know - I would appreciate it (I asked this in another thread but to date have no response to it).
Post #1388205
Posted Friday, November 23, 2012 12:03 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:00 AM
Points: 42,774, Visits: 35,871
SQL_ME_RICH (11/23/2012)
Is there a query that I could run against some of the system objects that would retrieve a time/date stamp for me of when the Isolation Level was set or changed?


Nope, because isolation level is a connection-specific setting.

If you mean when READ_COMMITTED_SNAPSHOT was enabled, that should have gone into the error log, but if you don't keep a lot of those that may not help.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1388226
Posted Friday, November 23, 2012 12:07 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 3:44 PM
Points: 479, Visits: 1,347
Thank you Gail!

:)

Just one last question to you...I am being told that the WITH(NOLOCK) hint is used because it is speeding up response time to queries that have it in there. I know this is not true based on what you shared earlier, but is there a good way for me to prove this to them? They simply run a query with it, and judge it based on the time stamp the query ran for in the result set windows - not through an execution plan or anything like that.
Post #1388228
Posted Friday, November 23, 2012 12:15 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:00 AM
Points: 42,774, Visits: 35,871
SET STATISTICS TIME ON.

And send them this http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1388230
Posted Friday, November 23, 2012 12:25 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 3:44 PM
Points: 479, Visits: 1,347
Gail, this is perfect! Thank you again!
Post #1388233
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse