The locks configuration option for SQL Server controls the number of locks available in SQL Server. You can view this through Management Studio by right clicking on the Server in object exploer, selecting and selecting the tab. The default is 0 which allows SQL server to allocate and de-allocate locking structure dynamically. Initially SQL server allocates enough memory for a pool of 2500 locks (Each lock structure consumes 96 bytes)

It is recommended that you leave the locks configuration set to 0 to allow SQL Server to manage lock structure dynamically.

Lock contention is one of the things that can causeyour SQL Server to perform poorly, but unlike causes like poorly written queries, Poor database and index design which will cause poor performance regardless of the number of users on the system. Lock contention becomes more of a performance problem as the number of users increase.

Tips on avoiding locking contention:

• · Keep transactions as short and concise as possible

• · Keep statements that comprise a transaction in a single batch to avoid unnecessary delays

• · Consider coding transaction in store procedures

• · If you have to use cursors commit updates frequently and as soon as is possible. Being much slower than set based processing locks in cursors will be held for longer.

• · Use the lowest isolation level required by the process.

• · Don’t allow users interaction between a BEGIN TRAN and a COMMIT TRAN statement