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?
Product: Automic Automation
Version: 12.0 and above
Database: SQL Server
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:
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.