Activation of versioning (READ_COMMITTED_SNAPSHOT => ON) on MS SQL Server
Article ID: 84738
Updated On:
Products
CA Automic Workload Automation - Automation Engine
AUTOMIC WORKLOAD AUTOMATION
Issue/Introduction
Severe performance issues are observed:
- in job execution in general
- WP and maybe even CP utilization (AE system overview) goes up to 100% constantly
- UI responses might be slow as well
- Deadlocks
Cause
Cause type: Configuration
Environment
Release: AUTWAB99000-11.0-Automic Workload Automation-Base Edition
Component:
Component:
Resolution
Activating Versioning is recommended by Automic to enhance the overall system performance by enabling concurrent access to tables of the Automic database schema:
Reference
For a more detail explanation about “Snapshot Isolation” on SQL-Server please follow this link:
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server
Even though activating Versioning is a recommended setting, it can severely slow down the performance of the database if not all of the requirements are applied:
1. Make sure to use the optimized driver ‘SQL Server Native Client’, rather than the standard driver SQLSRV32.DLL
To take advantage of the optimizations provided by the native client driver, MARS connection needs to be activated by setting the SQLDRIVERCONNECT config parameter within ucsrv.ini.
e.g.: SQLDRIVERCONNECT=ODBCVAR=NNNNNNRN,DSN=UC4;UID=UC4;PWD=****;Mars_Connection=Yes
2. Check beforehand the settings for ‘temdb’ on the SQL instance. Due to the fact that "… Once snapshot isolation is enabled, updated row versions for each transaction are maintained in tempdb…." 1) the tempdb needs to be configured for maximum performance according to this Microsoft recommendations: 1)https://technet.microsoft.com/en-us/library/ms175527(v=sql.105).aspx
In short, the recommendation is to have as many data files as CPU cores.
“… Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs. …”
3. Another aspect which needs to be considered when versioning is activated is the “ghost_record_cleaner”. For background information on this MS SQL component, we suggest working through the following article https://social.technet.microsoft.com/wiki/contents/articles/23290.ghost-rows-buster-in-sql-server.aspx
4. As recommended in general for Automation Engine systems, the AE database should be the only one hosted by an instance to avoid negative impact by settings required by other databases; e.g. in case versioning is active for other databases as well, this would add load to the system.
Please make sure to cover the above before activating versioning on the SQL Server instance.
alter database <database name> set READ_COMMITTED_SNAPSHOT ON
Reference
For a more detail explanation about “Snapshot Isolation” on SQL-Server please follow this link:
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server
Even though activating Versioning is a recommended setting, it can severely slow down the performance of the database if not all of the requirements are applied:
1. Make sure to use the optimized driver ‘SQL Server Native Client’, rather than the standard driver SQLSRV32.DLL
To take advantage of the optimizations provided by the native client driver, MARS connection needs to be activated by setting the SQLDRIVERCONNECT config parameter within ucsrv.ini.
e.g.: SQLDRIVERCONNECT=ODBCVAR=NNNNNNRN,DSN=UC4;UID=UC4;PWD=****;Mars_Connection=Yes
2. Check beforehand the settings for ‘temdb’ on the SQL instance. Due to the fact that "… Once snapshot isolation is enabled, updated row versions for each transaction are maintained in tempdb…." 1) the tempdb needs to be configured for maximum performance according to this Microsoft recommendations: 1)https://technet.microsoft.com/en-us/library/ms175527(v=sql.105).aspx
In short, the recommendation is to have as many data files as CPU cores.
“… Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs. …”
3. Another aspect which needs to be considered when versioning is activated is the “ghost_record_cleaner”. For background information on this MS SQL component, we suggest working through the following article https://social.technet.microsoft.com/wiki/contents/articles/23290.ghost-rows-buster-in-sql-server.aspx
4. As recommended in general for Automation Engine systems, the AE database should be the only one hosted by an instance to avoid negative impact by settings required by other databases; e.g. in case versioning is active for other databases as well, this would add load to the system.
Please make sure to cover the above before activating versioning on the SQL Server instance.
Additional Information
Workaround :
In case Versioning is already active:
Reference
How to improve overall Automation Engine performance
Root cause investigation for Automation Engine outage / freeze / unavailability
In case Versioning is already active:
- deactivate versioning if possible: set READ_COMMITTED_SNAPSHOT => OFF
- have the Database Administrator (DBA) rebuild all indexes related to any MQ* tables periodically (approx. once a day) until the suggested solution can be applied
Reference
How to improve overall Automation Engine performance
Root cause investigation for Automation Engine outage / freeze / unavailability
Feedback
Yes
No
Powered by
