INF: Reducing Lock Contention in SQL Server

• Microsoft SQL Server for OS/2, version 4.2
• Microsoft SQL Server version 4.2x

SUMMARY

Locking in SQL Server helps ensure consistency when reading and writing to the database. While this is desirable, it does affect performance.

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, this can occur in a non-indexed table, because all additions are applied to the last page in the chain. Creating separate history tables that are accessed by various groups can minimize the delay in this situation. In addition, creating a clustered index to distribute data will also help.



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



• Use HOLDLOCK only when necessary, because updates can be delayed by waiting for shared locks to be released.



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



• Using the SQL Server 4.2 cursor extensions to DB-Library (DB-Lib), optimistic concurrency control can be specified, using the CCUR_OPTCC setting in dbcursoropen(). This is ideal for applications that require a high degree of user interaction combined with high concurrency. This option ensures that update locks are obtained only when a user wants to commit a transaction.

Additional query words: 4.20 dblib Windows NT

Keywords          : kbprg SSrvProg 
Version           : 4.2
Platform          : OS/2 WINDOWS 
Issue type        : 

Last Reviewed: March 11, 1999