When should versioning - read_committed_snapshot - be off?

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

In all Automic Automation versions 12.x and higher, when using SQL Server database, is there a time that db versioning, the READ_COMMITTED_SNAPSHOT setting, should be turned off?

Environment

Product: Automic Automation
Version: 12.0 and above
Database: SQL Server

Resolution

The only time that the database should have READ_COMMITTED_SNAPSHOT set to "off" is if an alternative schema is used where the MQ tables are not on the same database as the rest of the Automic tables.
At that point, READ_COMMITTED_SNAPSHOT should be "on" on the main database (where the MQ tables are NOT) and "off" on the database that houses the MQ* tables.

Many times, time critical db calls in the WP and CP logs showing up a lot may be a reason to have an alternative schema as described above.  If there are a lot of time critical calls on the MQ* tables the recommendation would be the following:

1) Have a DBA check for fragmentation on the MQ* table indexes, if there is a lot of fragmentation, rebuild the indexes
2) If after rebuilding the indexes, there are still time critical calls on the MQ* tables, check which occur most frequently and have a DBA confirm that the execution plan for each statement is optimized.
3) If there are still a lot of time critical calls on the MQ* tables as seen in the WP logs, it may be best to consider using the Alternative MS SQL Schema as outlined in the documentation - https://docs.automic.com/documentation/webhelp/english/AA/12.3/DOCU/12.3/Automic%20Automation%20Guides/help.htm#Installation_Common/install_alternative_MS_SQL_Server_DB_schema.htm

You can confirm if the alternative schema configuration is already in place by doing the following:

• Open SQL Server Management Studio and log in as the user who can reach the Automic database
• Under the server, expand Databases, expand the Automic database, and expand Synonyms
• Note if there are any synonyms - they should have a name like dbo.MQ1CP001.

If there are no synonyms defined, then the regular server schema is used.  If there are synonyms defined, READ_COMMITTED_SNAPSHOT should be "off" only for the database that has the MQ tables.