Routine use of NOLOCK in a datawarehouse? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Routine use of NOLOCK in a datawarehouse?

We are implementing views on our underlying datawarehouse tables. Should we use the NOLOCK hint on every view on the assumption it will save resources on every read? Or is the saving insignificant? Any downsides (assuming we put the warehouse out of action during loading)?
All share locks (reading) are compatible with each other so if you are going to take your warehouse off line during loads then you are fine. Another note is to be careful using NOLOCK as a replacement of having to take your warehouse off line as it could result in inaccurate data reads.
Agree with Raulie. Just make sure that you don’t have any chance of changing data and selecting from it at the same time for a critical report. This would be a good way to get your company in trouble. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Hi All, thanks for your comments. I was not thinking of using NOLOCK as a measure to improve CONCURRENCY but was seeking people’s opinions on whether it be worth it purely for the PERFORMANCE improvement.
By default SQL Server places Shared locks on all resources that are SELECTed. I don’t think it is much of a difference if you place the hint or not. Last night I performed some test just for fun and actually noticed a slight performance gain in the execution plans NOT using nolock query hint. NOLOCK’s true strengths are with concurrency control.
I’ve seen a SQL server grind to halt due to the number of shared locks it had to try and obtain. Accepted that the situation in which this arose was fixed so that it couldn’t happen again but it did show that obtaining locks does cause a performance hit.