SQL Server Performance

NOLOCK and Store Procedures

Discussion in 'T-SQL Performance Tuning for Developers' started by kimscott, Oct 19, 2005.

  1. kimscott New Member

    Hello All,

    I am trying to find some documentation that will help me understand the benefits of using NOLOCK in stored procedures, if there is a benefit that is. Does anyone have some info that could help me out? Sites would be great, but personal experience is much appreciated.

    Thanks in advanced!
  2. kimscott New Member

  3. sachinsamuel New Member


    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.

    But there are also some risk factors like you may be getting some phantom reads or dirty reads as it ignores all locks and reads the table even when they undergoing changes. So you need to strike out a balance between the volume of modification which are carried on the tables.

    Sachin Samuel
  4. kimscott New Member

    Thanks Sachin!

  5. null New Member

    quote:Originally posted by sachinsamuel
    So you need to strike out a balance between the volume of modification which are carried on the tables.

    Sachin Samuel
    There are times when a high level of traffic to a table involving altering transactions (inserts/updates/deletes) shouldn't deter you from using noLock. To elaborate, you really have to know your data @ a system level, how does the application read/write to the db/table? We've seen some huge performance wins when adding noLock to a table that was by far the most traffic intensive table. On any given day there are some 100+ users pegging the table w/inserts and updates at a time, does this mean we should strike using noLock on it based on it's constant state of alteration? n this specific case though, the data is very user specific. Ie. User_A only ever accesses User_A's records, and User_B only ever accesses User_B's records, and so on. So even though the table is in a constant state of alteration as a whole, knowing that the system only read user specific records in all instances when the table is accessed showed tiny risk in using noLock on it.

    ps. If noLock seems to liberal, consider lock hints of other granularities. For the most part, I've used noLock in probably 90% of the Selects I've written, and have thrown some rowLocks in when I know the records in question can be read and altered by multiple users at multiple times. Watch that you're not burning too many resources on such small lock granularites though.
  6. x002548 New Member

  7. peterlemonjello New Member

    Be carefull, if a query using the no lock hint is scanning a data page that another query deletes the no lock query will fail.
  8. ghemant Moderator

    hi,<br /><br />read below links also :<br />1). <a href='http://www.sql-server-performance.com/rd_table_hints.asp' target='_blank' title='http://www.sql-server-performance.com/rd_table_hints.asp'<a target="_blank" href=http://www.sql-server-performance.com/rd_table_hints.asp>http://www.sql-server-performance.com/rd_table_hints.asp</a> </a> <br />2). <a href='http://www.sql-server-performance.com/hints.asp' target='_blank' title='http://www.sql-server-performance.com/hints.asp'<a target="_blank" href=http://www.sql-server-performance.com/hints.asp>http://www.sql-server-performance.com/hints.asp</a></a><br />3). <a href='http://www.sql-server-performance.com/reducing_locks_hints.asp' target='_blank' title='http://www.sql-server-performance.com/reducing_locks_hints.asp'<a target="_blank" href=http://www.sql-server-performance.com/reducing_locks_hints.asp>http://www.sql-server-performance.com/reducing_locks_hints.asp</a></a><br />4). <a href='http://www.sql-server-performance.com/absolutenm/templates/?a=217&z=0' target='_blank' title='http://www.sql-server-performance.com/absolutenm/templates/?a=217&z=0'<a target="_blank" href=http://www.sql-server-performance.com/absolutenm/templates/?a=217&z=0>http://www.sql-server-performance.com/absolutenm/templates/?a=217&z=0</a></a> <br />5). <a href='http://www.sql-server-performance.com/reducing_locks.asp' target='_blank' title='http://www.sql-server-performance.com/reducing_locks.asp'<a target="_blank" href=http://www.sql-server-performance.com/reducing_locks.asp>http://www.sql-server-performance.com/reducing_locks.asp</a> </a> <br /><br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Regards<br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami<br />
  9. null New Member

    quote:Originally posted by x002548

    It's probably the fastest way to hose your database

    Brett, do you mind elaborating on this?

Share This Page