Search This Blog & Web

Thursday, May 27, 2010

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
SP_INDEXOPTION 'table_name', 'AllowPageLocks', FALSE
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:

No comments: