How to reduce deadlocks and improve SQL server throughput and performance

With SQL Server’s default configuration, it is possible for SELECT statements to deadlock with UPDATE statements on the same table due to the order locks are obtained on the table and index. For more details, see http://samsaffron.com/archive/2008/08/27/Deadlocked

To reduce deadlocks and improve SQL server throughput and performance, Senstar recommends turning on snapshot isolation, which allows the SELECT statement to execute using slightly stale data that is already committed to the database. This is the same data it would have received if the SELECT statement completed before the UPDATE statement started, so there is no risk of inconsistency. For more details on snapshot isolation, see http://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.80).aspx

To enable snapshot isolation:

1. Disconnect all clients from the database, including all Senstar applications on all servers in your farm.  “killall 9” is the easiest way to do this.

2. Start all Symphony Services again.  “killall 5” is the easiest way to do this.

Notes:

This setting is enabled by default in newer Symphony installations.

During a Symphony upgrade, this setting cannot be automatically enabled. However, you can check the current state of this setting by running the following database query:

Verify that your database is set to On, True. If it is not, then snapshot isolation is currently disabled. Complete the steps above to enable snapshot isolation.