Senstar
Safeguarding people, places and property
Contact       Xnet Login       English
Knowledge Base Article
Article Type: How To
Product: Symphony
Product Version:
Component: Symphony Server
Device Brands:
Created: 3-Jun-2013 4:27:23 PM
Last Updated:

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.

dbupdater "ALTER DATABASE Aira SET ALLOW_SNAPSHOT_ISOLATION ON"

dbupdater "ALTER DATABASE Aira SET READ_COMMITTED_SNAPSHOT ON"

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:

dbupdater "select name, snapshot_isolation_state_desc, is_read_committed_snapshot_on from sys.databases 

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.

Average rating:
Please log in to rate.
Rated by 33, Viewed by 4287