Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««56789»»

The Effect of NOLOCK on Performance Expand / Collapse
Author
Message
Posted Sunday, March 11, 2007 8:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 22, 2014 7:51 AM
Points: 24, Visits: 54

About read-only report database - already underway - as you say very obvious - just thought there might be a quick interim fix. A large number of the reports do have to be bang up to date (about 40%) and due to the nature of the business they do not restict themselves to three table joins with two records - plus the client insists on using CR parameters which seem to have the effect of doing the report query at least one for each dropdown etc

It looks like somewhere under the hood of CR it should be possible buy version 10 is the nearest I can find where it is possible.

I'll let you know when we find a definitive way around this one.

Post #350589
Posted Sunday, March 11, 2007 12:55 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:02 PM
Points: 35,397, Visits: 31,955

Ok... confusion has set in, Richard... in your latest post, you say "A large number of the reports do have to be bang up to date" and in a previous post, you say "There are a large number of reasons why we don't care if the data in these reports is correct to the second"

Which is it, really?



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #350594
Posted Sunday, March 11, 2007 3:14 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:34 AM
Points: 15, Visits: 125
no idea if the situation would be similar, but...

I recently ran into some problems with an app that used parameterized queries generated by some OO middleware. This middleware was pretty flexible, but it was creating the parameters as nvarchars while all the fields we use were just varchars.

SQL appeared to be creating/holding a bunch of locks while it converted every value in the table or index to unicode, THEN do the comparison. So basically it was having to scan through & process every row vs. doing a simple index seek.

I wonder if you could feed some of those dropdowns via. separate views or indexed views.
Post #350598
Posted Friday, March 23, 2007 12:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 28, 2011 1:18 PM
Points: 2, Visits: 15

Iam working on 8 terabytes of data, where i found the vital role of NOLOCK.Definetly it improves the performance

Post #353441
Posted Friday, March 23, 2007 4:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 22, 2014 7:51 AM
Points: 24, Visits: 54

By "up to the second" I just meant that the reports are almost always based on data just saved - i.e. the transaction is complete before the report is run so uncommitted data is irrelevant to these outputs.

I began the arduous task of applying the WITH (READUNCOMMITTED) hint to all the views used by reports in the system (all 2500+ of them) and started seeing immediate performance gains. Overall the servers cache seems to be coping better and the CPU and IO have dropped considerably. The system also has an OLE component that talks to and populates word documents which we took another look at, changed the connection it uses to read uncommitted and explicitly made the recordsets read-only and it's now running five times faster and taking up a half of the server resources it used to. We also reviewed a lot of the indexes and found a few missing based on the current usage. The application as a whole uses transactions sparingly but we also discovered a few that had been misplaced around batches of work and changed their granularity - it's been a hell of a week.

Overall things are back to being operational performant but it's still driving me crazy that I can't tell Crippled Reports to configure it's connections as NOLOCK or READUNCOMMITTED - how hard can it be

Post #353458
Posted Friday, March 23, 2007 4:48 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:02 AM
Points: 2,674, Visits: 697

I maintain that presenting the use of nolock as a tuning tool is both dangerous and missleading.

That there is a use for read uncommitted I will agree in certain circumstances, and lets use isolation levels here to make sure everyone understands exactly what we're talking about, although I'd always prefer to lean towards read only databases or filegroups.  There's always going to be some gain if you tell sql server not to issue shared locks but compared to normal tuning and optimisation I think the risk outweighs any possible gains.

I also take exception to the phrase "Using hints in a query is something that most DBAs don't ever seem to bother with" - there's a REALLY REALLY good reason we generally don't - telling the optimiser that you know better is not recomended. I'd also be more interested if the tests had been run on a server rather than a laptop, I also note that your plans show parallelism, I suggest you try running your tests using profiler, IO stats often do not show additional io generated by parallel plans .. as I remarked first time around I tried a couple of simple tests on a server and could not see any difference.

I'd also question testing against table scans, this is something your average DBA tries to avoid.



The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Post #353466
Posted Friday, March 23, 2007 6:36 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, Visits: 711

I wonder if maybe it could be a good idea on a read-only database.  Many situations I've worked with in the past had a 'readonly' db used as a datamart for reporting.

There would be little contention for records on that database, and little chance of a record being changed unless the read-only db was being repopulated...  Something to think about anyway.

 

Mark

Post #353489
Posted Friday, March 23, 2007 11:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, May 24, 2014 7:58 PM
Points: 219, Visits: 824

Be very careful: even with READ COMMITTED isolation level you can get inconsistent results in your reports:

http://www.devx.com/dbzone/Article/32957/0/page/2

Post #353589
Posted Friday, March 23, 2007 11:26 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:12 AM
Points: 31,210, Visits: 15,651
I think there is good reason to warn people of dangers, but I've always been a practical DBA, using whatever is in my toolbox and works. If inconsistent results are a problem, then certainly re-examine your use of NOLOCK (or any other tool).

I hate that so often we don't get details or good information on the various tools available to us, like undocumented tools. I think we'd be better off if everything was very well documented, especially the dangers, and let people use it when it works in their environment.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #353595
Posted Friday, March 23, 2007 12:29 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 16, 2014 6:06 AM
Points: 6,259, Visits: 2,030
If you SET the DB to read-only all locking mechanisms will be bypassed!


* Noel
Post #353618
« Prev Topic | Next Topic »

Add to briefcase «««56789»»

Permissions Expand / Collapse