June 9, 2003 at 7:07 am
Hi,
I have utilized the 'WITH(NOLOCK)' query hint in a number of SQL select queries within my application to improve the performance. This would allow certain SELECT queries to read past any and all transactions and locks on records, tables etc. The premise was to reduce the number of SQL Timeouts on longer running queries. We have accepted the risk of 'dirty reads' and felt that it was better to see something than get timeouts.
However, lately there are some instances where we are still getting timeouts. I have been reading some more about 'SQL Server query optimizer' and am wondering if the 'WITH(NOLOCK)' may actually be self-defeating; that it actually may be preventing SQL Sever from using the 'path of least resistance'.
Does anyone have any experience or suggestions for me?
Thanks
rob.
June 9, 2003 at 7:22 am
I don't think the hint hurts you. Easy enough to test a few times with/without. Probably need to revisit the query plan, see what can be tweaked.
Andy
June 10, 2003 at 1:35 am
what is your frontEnd Application ?
you can Execute stored procedure "with (NOLOCK)" in stead of long runing queries ..
check again All reports and make all reports that gets huge data use "with (NOLOCK)" in it ..
Alamir Mohamed
Alamir Mohamed
Alamir_mohamed@yahoo.com
June 10, 2003 at 6:51 am
I'm inclined to side with Alamir on fron-end question. I also have an application with a similar timeout problem. The application has a timeout value set so low that the query won't complete all the time. However, if I go into Query Analyzer, it runs to completion (no timeout value set to cause problems).
If the front-end is you application (e.g. VB or something) check the .CommandTimeout (or whatever is appropriate for your environment)property of the query you are trying to run.
Hope this helps.
David
June 10, 2003 at 8:16 am
Hi rob
we faced a similar problem when we had a lot of deadlocks in a couple of our input screens(front end was ASP and query timeout values were set to the higher end)....
SQL Server was escalating the row locks to page and table locks and this was causing the problem...
our application had no issues with 'dirty reads' - i.e whatever the user was inserting/updating was specific only to that user....hence we put in WITH (NOLOCK) in all the relevant stored procedure select statements and WITH (ROWLOCK) in the insert/update statements...and the deadlocks vanished...
we started off using WITH (NOLOCK) only to resolve the deadlock error portions of our code and we are now trying it out it in a few 'heavy duty' queries to see if it improves performance...of course if always pays to first check if the existing query can be tweaked to run faster and use the lock hints as the next step...
so far I haven't had any issues with the lock hints...
HTH...
p.s :
I remember reading somewhere that specifying WITH (NOLOCK) in the select statement always ensures that the lock is honoured - but that WITH (ROWLOCK) may be ignored by SQL Server and it might be escalated to a page/table lock...another problem in WITH (ROWLOCK) is that the granularity of these locks might lead to performance issues..
June 10, 2003 at 8:31 am
NOLOCK should just affect the locking behavior and any slow downs due to waiting for locks to clear would be solved. If the incidence of lock contention is low, then it should make no difference and the SQL should be optimized.
June 10, 2003 at 8:59 am
Thanks all for your feedback:
I think I'm ok then. I was curious to see if anyone had an issues with using too many 'with(nolock)' hints. It doesn't appear so.
"WITH(NOLOCK) neither creates nor honors any locks" and seems to be the fastest way to get at data.
rob.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply