SQL Server Performance

Routine use of NOLOCK in a datawarehouse?

Discussion in 'Analysis Services/Data Warehousing' started by idur022, Aug 9, 2004.

  1. idur022 New Member

    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)?
    Cheers,
    Bill
  2. Raulie New Member

    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.
  3. derrickleggett New Member

    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
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  4. idur022 New Member

    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.
    Bill
  5. Raulie New Member

    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.
  6. Dave Wells New Member

    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.

Share This Page