SYMPTOM:
Deadlocks are issued on connection strings running against the database hosting the statistics tables and multiple deadlock messages for these processes are seen in DSSErrors.log. The following error message is repeatedly seen in DSSErrors.log.
" Execute Query failed. Deadlocked. Error type: Odbc error. Odbc operation attempted: SQLExecDirect. Transaction (Process ID ###) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.....SQL Statement: update IS_SESSION_STATS set DISCONNECTTIME = ....."
Note: The error message seen above is for SQLServer and the content of the error message can differ based on the database being used.
CAUSE:
This error message is seen in DSSErrors.log when connection strings to the statistics database get locked because of high load on the database while updating statistics data in the statistics tables.
This can happen when 'Complete Session Logging' is chosen at the Strategy Intelligence Server level and all the projects are configured to use the same database instance and database. When an UPDATE statement is issued against the database, the table used in the SQL query is locked and when simultaneous update statements are issued, this deadlock condition can arise. Starting with Strategy 8.0 and later, project GUID is not recorded in IS_SESSION_STATS and only details about sessions created at the Strategy Intelligence Server level are logged. Because of choosing complete session logging at the Server level, the IS_SESSION_STATS table is updated by inserting the same row of data for each project that is configured to collect statistics, as shown below:
If complete session logging for statistics is chosen at the Strategy Intelligence Server level, statistics for each project should be written to a different database instance to connect to different databases for its own statistics logging. For more information on these settings, refer to the following Strategy Knowledge Base technical note:
KB17819 (KB6400-8X-0523): What are the "Complete Session Logging" and "Single Instance Session Logging" options for Statistics in Strategy Intelligence Server 8.x and newer?
ACTION:
Modify the Strategy Intelligence Server statistics configuration to use 'Single Instance Session Logging' and select a project that is configured to collect statistics and loaded on the Intelligence Server.
Modifying this setting on the Strategy Intelligence Server should help in reducing the load to a great extent. Remove duplicate entries created in the IS_SESSION_STATS table because of having complete session logging at the Strategy Intelligence Server 8.x level. Also, depending on the amount of statistics data being recorded, statistics tables should be purged frequently so that the tables are not huge.
Note: Performance of the statistics database is not governed by the Strategy Intelligence Server 8.x and the DBA should have the appropriate settings on the database to ensure optimal performance.
WARNING:
Before making any changes to the statistics tables, it is recommended to make a backup of all the available data in the tables.
KB16875
