Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. How to reduce lock contention in SQL Server

How to reduce lock contention in SQL Server

Summary

Locking in SQL Server helps ensure consistency when reading and writing to the database. There is always a tradeoff in any relational database system between concurrency and consistency. It is always important to maintain consistency, or accuracy, of the data. However, the highest levels of consistency can result in less concurrency, or worse performance, if the proper steps are not taken.

↑ Back to the top

More Information

The following methods can be used to reduce lock contention and increase overall throughput:

  • Avoid situations in which many processes are attempting to perform updates or inserts on the same data page. For example, in version 6.x and earlier, if there is no clustered index on a table, or if the clustered index consists of a nonrandom value, such as an ever-increasing key value, all inserts will go on the last page of a table. This particular hotspot situation can be avoided by creating a clustered index on a value that will insure each user and/or process is inserting to a different page in the table.
  • Avoid transactions that include user interaction. Because locks are held for the duration of the transaction, a single user can degrade the entire systems performance.
  • Keep transactions that modify data as short as possible. The longer the transaction, the longer the exclusive or update locks are held. This blocks other activity and can lead to an increased number of deadlock situations.
  • Keep transactions in one batch. Unanticipated network problems may delay transactions from completing and thus releasing locks.
  • Avoid pessimistic locking hints such as holdlock whenever possible. They can cause processes to wait even on shared locks.
  • In most cases, you should use SQL Server's default isolation level. The isolation level determines at what point the tradeoffs are made between concurrency and consistency. If you have a strong business need for a higher isolation level, make sure that you evaluate all the tradeoffs and perform thorough testing under a high stress load.
  • Reduce the fillfactor when creating an index to help diminish the chance of random updates requiring the same page. This is especially useful for small tables that are frequently accessed.
  • If you are using DB-Library (DB-Lib), optimistic concurrency control can be specified by using the CCUR_OPTCC setting in dbcursoropen(). This option ensures that update locks are obtained only when a user wants to commit a transaction.

↑ Back to the top

Keywords: kb, kbbillprodsweep, kbprogramming, kbinfo, kbdsupport, kbappliestoyukon, kbyukonsweep

↑ Back to the top

Article Info
Article ID : 75722
Revision : 5
Created on : 8/19/2020
Published on : 8/20/2020
Exists online : False
Views : 190