SNAPSHOT Isolation with (NOLOCK) being used all over

  • 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

  • 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, MVP, M.Sc (Comp Sci)
    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
  • 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.

  • 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, MVP, M.Sc (Comp Sci)
    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
  • 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?

  • 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).

  • 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, MVP, M.Sc (Comp Sci)
    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
  • 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.

  • 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, MVP, M.Sc (Comp Sci)
    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
  • Gail, this is perfect! Thank you again!

  • You also might mention that getting the wrong result fast is usually not acceptable to users. Most users would rather have the correct results, even if it does take slightly longer.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    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
  • SQL_ME_RICH (11/21/2012)


    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

    This is controlled by a SET option in the database and is therefore transfered with the database. If it's switched off then someone issued a

    ALTER DATABASE mydb SET ALLOW_SNAPSHOT_ISOLATION OFF

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Perry - thank you for confirming this. I knew it was a DBCC command that needed to be issued. The problem is that we have no idea when it happened, and doubtful that they are going to go back in the logs to try and find out when it happened. The DA said he had it put in place over 3 years ago, so...

  • SQL_ME_RICH (11/26/2012)


    Perry - thank you for confirming this. I knew it was a DBCC command that needed to be issued.

    It's not a DBCC command. Just the ALTER DATABASE that Perry listed.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    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
  • This is what happens when I respond to posts before coffee - thanks for that Gail!

    :crazy:

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply