How to reduce lock contention in SQL Server (75722)

MORE INFORMATION

• 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.