Reducing SQL Server Locks from SQL Server Performance

1. Use appropriate locking level

you can override how SQL Server performs locking on a table by using the SP_INDEXOPTION command. Below is an example of code you can run to tell SQL Server to use page locking, not row locks, for a specific table:

SP_INDEXOPTION 'table_name', 'AllowRowLocks', FALSE
GO
SP_INDEXOPTION 'table_name', 'AllowPageLocks', FALSE
GO
This code turns off both row and page locking for the table, thus only table locking is available.

2. Keep all Transact-SQL transactions as short as possible.
3. An often overlooked cause of locking is an I/O bottleneck.
4. To help reduce the amount of time tables are locked, which hurts concurrency and performance, avoid interleaving reads and database changes within the same transaction.
5. Any conditional logic, variable assignment, and other related preliminary setup should be done outside of transactions
6. Encapsulate all transactions within stored procedures
7. If you have a client application that needs to "check-out" data


for more information in detail please review: