July 30, 2008 at 11:28 am
Hi,
Can using (Nolock) or (ReadPass) help gaining performance benefits for select queries?
In my point of view may be we could......like in my scenario......db is accessed by multiple applications & multiple users concurrently........so if a table is locked by any user of any application......using (ReadPass) will not wait for that lock to be released so using this hint will give us results quickly..........any thoughts??
Thanks,
usman
July 30, 2008 at 12:11 pm
Rather stay away from any form of hints. If you have blocking problems look at optimising the queries that are causing the blocking, rather than hiding the symptoms with locking hints.
Also, be very sure that you know and are happy with the side effects of those two locking hints and that you know exactly what they are doing. If you're not sure, rather don't.
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
July 30, 2008 at 12:21 pm
Like Gail said, these hints have some awful side-effects. NOLOCK in particular can result in missed or even duplicated data as your query reads through the pages.
I have found very few instances in which these hints really should be used. I have found lots of places in which they have been mis-used and have even had some devestating effects.
The best way to avoid blocking problems is to design the database and application properly.
July 30, 2008 at 12:40 pm
Michael Earl (7/30/2008)
NOLOCK in particular can result in missed or even duplicated data as your query reads through the pages.
And readpast can skip rows by definition, since it doesn't wait for locks to be released but ignores locked rows and pages.
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
July 30, 2008 at 8:21 pm
usman.tanveer (7/30/2008)
Hi,Can using (Nolock) or (ReadPass) help gaining performance benefits for select queries?
In my point of view may be we could......like in my scenario......db is accessed by multiple applications & multiple users concurrently........so if a table is locked by any user of any application......using (ReadPass) will not wait for that lock to be released so using this hint will give us results quickly..........any thoughts??
Thanks,
usman
The answer is, "Yes, they will help performance"... but as others have stated, you either be reading potentially dirty data, or missing data that is in the process of being updated in some form and you really should determine what all the blocking that prevents you from reading is.
That, not withstanding...
If it's for a "point in time" type of report and you can tolerate the risk of some data being read by your report and rolled back, making the report incorrect, then don't mess around with hints... use a directive!
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
That's the same as setting WITH (NOLOCK) on every table in the query.
Heh... I tell people all the time... "Don't write data unless you know it's good to begin with!!! Using ROLLBACK to do your job is the wrong way to do it." 😛
Ok... duck... here they come. :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply