SQL Server Performance

NOLOCK question.

Discussion in 'Performance Tuning for DBAs' started by sql_jr, Oct 9, 2007.

  1. sql_jr New Member

    I want to use NOLOCK in an overnight routine/sql job that queries our database (financial/GL stuff), to reduce possible contention of other processes running against the same data ables. During the night, there should be no changes, but want to make sure I don't run into trouble of getting uncommitted data. From SSP.com, it specifically talks about it: PLease advise! TIA
    Using NOLOCK politely asks SQL Server to ignore locks and read directly from the tables. This means you completely circumvent the lock system, which is a major performance and scalability improvement. However, you also completely circumvent the lock system, which means your code is living dangerously. You might read the not-necessarily-valid uncommitted modifications of a running transaction. This is a calculated risk.

    For financial code and denormalized aggregates (those little counters of related data that you stash away and try desperately to keep accurate), you should play it safe and not use this technique. But I think you'll find that for better than 90% of your application, it would not be that big of a deal if a user (or even intermediate code) saw an uncommitted modification. In fact, you'll probably find that most of your data never or only very rarely changes, in which case the overhead of locking the data is almost always completely wasted.
  2. thomas New Member

    This very week a developer came to me, he'd been running a large query with NOLOCK, and he encountered the error

    could not continue with scan with nolock due to data movement (or something very similar)

    I have seen this before. SO be careful!

Share This Page