May 20, 2009 at 9:01 am
Hi,
I have developing a database for web application. This application is basically used for displaying details from the database. Most of the stored procedures have mostly SELECT queries.The number of user accessing the WEB application could be approximately 100.
Will using NOLOCK in the Stored procedure for SELECT State would provide performance beneficit?
Setting the ISOLATION LEVEL TO READ UNCOMMITTED at the stored procedure level is same as NOLOCK at each table level and Can i use this in my scenario.
What is difference between "NOLOCK" and "ISOLATION LEVEL TO READ UNCOMMITTED"?? If they are same, which is the best one to use for performance.
May 20, 2009 at 9:13 am
If the number of users accessing the website is just 100, I dont see any reason why you should go for NO LOCK or Isolation level read uncommitted. You are taking a risk of showing wrong values. Acquiring a read lock will not cause that much difference to your application speed or DB performance.
When you use one of these Isolation level, you might get errors like Could not get data due to data movement.
If you really dont care much about the accuracy of your data shown on the web and you have to use one of the two options you stated, i would go for NO LOCK basically because you can choose which table you want to read with NO LOCK. In this way you get more control of pick and choose.
Just my 2 cents
-Roy
May 20, 2009 at 9:33 am
naveenreddy.84 (5/20/2009)
Will using NOLOCK in the Stored procedure for SELECT State would provide performance beneficit?
Nolock is not a performance tool. It controls the accuracy of the data read by that query. If you don't mind queries occationally returning incorrect data, then by all means, use it.
What is difference between "NOLOCK" and "ISOLATION LEVEL TO READ UNCOMMITTED"??
None.
If they are same, which is the best one to use for performance.
Neither. They are not performance tuning tools.
For performance, optimise your queries and tune your indexes. If you have major blocking afterwards, perhaps consider snapshot 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
May 21, 2009 at 4:37 am
naveenreddy.84 (5/20/2009)
Will using NOLOCK in the Stored procedure for SELECT State would provide performance beneficit?
It may. Not acquiring locks or reading row-versioned rows is always faster than acquiring locks or reading row-versioned rows.
naveenreddy.84 (5/20/2009)
Setting the ISOLATION LEVEL TO READ UNCOMMITTED at the stored procedure level is same as NOLOCK at each table level
Correct. Setting the ISOLATION LEVEL sets the default isolation level. This can be changed for individual tables in a statement by using table hints like WITH (READCOMMITTEDLOCK) or WITH (SERIALIZABLE). WITH(NOLOCK) is a synonym for WITH (READUNCOMMITTED).
naveenreddy.84 (5/20/2009)
Can i use this in my scenario.
You could, and many web applications do. It depends on whether you want to be able to see data changed by other concurrent transactions which has not yet been committed. READUNCOMMITTED is the minimum isolation level provided by SQL Server. It guarantees physical consistency of the data, and very little else.
naveenreddy.84 (5/20/2009)
What is difference between "NOLOCK" and "ISOLATION LEVEL TO READ UNCOMMITTED"?? If they are same, which is the best one to use for performance.
See above. If they are the same, which is best to use? Is that a trick question? They're the same! 😀
Note though that the main reason people use READUNCOMMITTED (aka NOLOCK) is because they believe all locks are bad. This is not the case.
See http://msdn.microsoft.com/en-us/library/ms173763(SQL.90).aspx for a fuller discussion.
Finally, I would just mention that it is quite possible to read 'inaccurate' data at any isolation level less than SERIALIZABLE. The best level for computing aggregates for example is READ_COMMITTED_SNAPSHOT or full SNAPSHOT.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 21, 2009 at 2:27 pm
[font="Verdana"]The pervasive use of NOLOCK as a "performance tool" is, I believe, a hang-over from the SQL 6.5 and prior days, when SQL Server was still page locking within tables. NOLOCK was risky, but reduced page contention and readers blocking writers.
Let's see, since then we have gone through SQL Server 7, 2000, 2005, 2008 and we still have people telling each other to use NOLOCK.
Don't bother. Follow Gail's advice and actually tune your database. If you get any performance increase out of NOLOCK (or by changing the isolation level) it will be so negligible that it won't be worth the risk of reading unstable data.
[/font]
May 21, 2009 at 3:49 pm
Hey Bruce,
I'm not usually one to disagree 😉 but:
Read uncommitted is just as valid as any other isolation level. It depends on the business requirements. Sure, from a purist's point of view, every query should be run at SERIALIZABLE. Running at default READ COMMITTED just isn't always optimal.
Consider the busy OLTP system where 99.5% of all transactions commit and one doesn't care much about reading data which has not yet been committed. If the point is to maximize concurrency, read uncommitted is an option.
Sure, I would usually prefer to run a row-versioning isolation level to solve the readers-blocking-writers (and vice-versa) issue, but not every environment is ready for that. Especially those running SQL2K servers 🙂
One can argue about whether the best use of developer and DBA time on a system is to 'properly tune' it to minimize the impact of shared locks on concurrency - or to use that time for new projects for example. I don't know how it is where you work, but the guys that make the decisions here are a lot more excited about bug fixes and delivering new capabilities than they are about adopting a 'better' isolation level.
Don't get me wrong - I'm not saying that NOLOCK isn't ever abused - it is. I'm just arguing that there are systems where it makes sense.
This is one of my favourite articles illustrating how READ COMMITTED gives you rather less guarantees than many people assume:
http://blogs.msdn.com/craigfr/archive/2007/04/25/read-committed-isolation-level.aspx
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 21, 2009 at 4:06 pm
I just hate seeing NOLOCK put out there as the bad practice of first resort for solving concurrency problems, especially before trying SNAPSHOT isolation.
May 21, 2009 at 4:20 pm
Michael Valentine Jones (5/21/2009)
I just hate seeing NOLOCK put out there as the bad practice of first resort for solving concurrency problems, especially before trying SNAPSHOT isolation.
Agreed. NOLOCK as a first resort makes baby Jesus cry. 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 21, 2009 at 4:58 pm
Paul White (5/21/2009)
Hey Bruce,I'm not usually one to disagree 😉 but:
Read uncommitted is just as valid as any other isolation level.
[font="Verdana"]Hey Paul,
I'm not really entirely sure you are disagreeing with me. :w00t:
I agree that read uncommitted is quite valid (although I prefer snapshot isolation, but that has it's own issues around tempdb -- I have to say I'm used to that from my Oracle days.)
What I argue with is that NOLOCK is the first thing that people look at to fix performance issues. Okay, in high concurrency scenarious that can be valid, but I'm with Gail on this one: the first thing to look at is tuning indexes and table accesses and queries. Adding NOLOCK doesn't help that much -- you'd be better spending valuable developer time on actually tuning.
NOLOCK isn't a magic bullet: that's the attitude that I think has been prevalent, and that's what I was disagreeing with. That doesn't mean NOLOCK is invalid.
[/font]
May 21, 2009 at 5:03 pm
Bruce,
Darn it. I was looking forward to an energetic debate there but it seems we all agree - oh well 😀
I'm a huge fan of RLV isolation levels too - tempdb issues can largely be fixed with better design/faster hardware. Fixing code that depended on read committed blocking behaviour is rather less fun. Triggers are a good source of problems in this regard, though a sprinkling of READCOMITTEDLOCK table hints helps a lot.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 21, 2009 at 5:12 pm
Paul White (5/21/2009)
Bruce,Darn it. I was looking forward to an energetic debate there but it seems we all agree - oh well 😀
[font="Verdana"]Just because we agree doesn't mean we can't have a good argument![/font]
Paul White (5/21/2009)
Triggers are a good source of problems in this regard...
[font="Verdana"]Triggers. Don't talk to me about triggers. And me with a terrible pain in all the diodes down my left side...[/font]
May 21, 2009 at 5:59 pm
Bruce W Cassidy (5/21/2009)
[font="Verdana"]Just because we agree doesn't mean we can't have a good argument![/font]
Oh I couldn't disagree more! :w00t:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply