September 17, 2003 at 9:15 am
Hi Guys,
Can any one suggest that whether i should use NOLOCK optimizer hint in Reporting stored procedures. i build a SELECT query dynamically which references many tables and execute it at end with Exec? Can i get some performance benefit with it?
September 17, 2003 at 12:22 pm
I have not seen a performance benefit with using nolock, of course if you are getting any deadlocks or blocking it would help, but then again, you would want to troubleshoot those instead of this approach.
I've seen on big tables (> million rows) where nolock seemed to have more logical IO. Never been able to explain that one.
HTH
Ray Higdon MCSE, MCDBA, CCNA
------------
Ray Higdon MCSE, MCDBA, CCNA
September 17, 2003 at 12:44 pm
For reporting that isn't money/life critical, I'd say set the isolation level to read uncommitted. For reporting it hardly ever matters and blocking is often an issue - easier to avoid it!
Andy
September 17, 2003 at 4:47 pm
Andy,
Let's assume that I can't get to the connect string (long story). Is there any way to globally (by DB? by user? by ??) configure the isolation level besides the connect string?
We have a Data Warehouse server that's being killed by page and key locks from simple querys (on complex joined views). Connect string is embedded in a separate DB with 1000's of querys, can't get to it easily...
All we have been able to do is use NOLOCK on the underlying views. Can you define a view so locks aren't held?
September 17, 2003 at 6:12 pm
As far as I know it's per connection, no way to set globally. If you're running any type of login proc to record entry/exit, you could potentially put the SET there (assuming that the connection stays open for the duration). Within a view I think NOLOCK is the only option you have. You could put the entire db into read only mode if you're just during periodic refreshes.
I think maybe it's worth spending more time looking at the blocking. Locks are ok as long as they are quick, it's only blocking that matters and that is a performance issue - bad query plan due to bad index plan, bad query plan due to bad statistics, tables too wide for decent IO, insufficent IO or CPU capacity.
Andy
September 18, 2003 at 12:18 am
Thans for your response.
i should use "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" in my Stored procedure and at the end of stored procedure set it back to default value. Am i right?
September 22, 2003 at 9:54 pm
If you want to use a global "nolock" equivalent then set your DB to read_only mode, if you can. SQL will not issue locks on a database that is in read_only mode (ok, there may be DB schema locks - not sure). HTH!
James Stover, McDBA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply