SNAPSHOT Isolation with (NOLOCK) being used all over

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » SNAPSHOT Isolation with (NOLOCK) being used...

25 posts, Page 1 of 3

1 2 3 »»»

SNAPSHOT Isolation with (NOLOCK) being used all over

Rate Topic

Display Mode

Topic Options

Author

Message

SQL_ME_RICH

Posted Wednesday, November 21, 2012 9:28 PM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, May 13, 2013 6:37 PM
Points: 434, Visits: 1,137

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

GilaMonster

Posted Thursday, November 22, 2012 12:13 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 37,687, Visits: 29,945

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

SQL_ME_RICH

Posted Thursday, November 22, 2012 12:00 PM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, May 13, 2013 6:37 PM
Points: 434, Visits: 1,137

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

GilaMonster

Posted Thursday, November 22, 2012 2:27 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 37,687, Visits: 29,945

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

paul.knibbs

Posted Friday, November 23, 2012 8:11 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:43 AM
Points: 1,257, Visits: 4,255

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

SQL_ME_RICH

Posted Friday, November 23, 2012 9:50 AM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, May 13, 2013 6:37 PM
Points: 434, Visits: 1,137

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

GilaMonster

Posted Friday, November 23, 2012 12:03 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 37,687, Visits: 29,945

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

SQL_ME_RICH

Posted Friday, November 23, 2012 12:07 PM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, May 13, 2013 6:37 PM
Points: 434, Visits: 1,137

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

GilaMonster

Posted Friday, November 23, 2012 12:15 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 37,687, Visits: 29,945

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

SQL_ME_RICH

Posted Friday, November 23, 2012 12:25 PM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, May 13, 2013 6:37 PM
Points: 434, Visits: 1,137

Gail, this is perfect! Thank you again!

Post #1388233

« Prev Topic | Next Topic »

25 posts, Page 1 of 3

1 2 3 »»»

Permissions