November 21, 2012 at 9:28 pm
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
November 22, 2012 at 12:13 am
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
November 22, 2012 at 12:00 pm
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.
November 22, 2012 at 2:27 pm
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
November 23, 2012 at 8:11 am
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?
November 23, 2012 at 9:50 am
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).
November 23, 2012 at 12:03 pm
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
November 23, 2012 at 12:07 pm
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.
November 23, 2012 at 12:15 pm
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
November 23, 2012 at 12:25 pm
Gail, this is perfect! Thank you again!
November 23, 2012 at 12:39 pm
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
November 26, 2012 at 5:13 am
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" 😉
November 26, 2012 at 11:18 am
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...
November 26, 2012 at 11:21 am
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
November 26, 2012 at 11:24 am
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