This article describes changes to our previously recommended server and database configurations. They have been shown to deliver significant performance improvements when interacting with large datasets stored in the various History and Workflow related tables.
These steps must be performed on all environments running iCM 10.0.7.0 and have to be done before running an upgrade from 10.0.6.0 to 10.0.7.0. Performance improvements can also be gained in iCM 10.0.6.0.
Enabling Trace Flag 2371
For SQL Server 2016 and above with database compatibility level 130 or higher this trace option no longer needs to be enabled (the corresponding behaviour is enabled by default).
To permanently enable trace flag 2371, use SQL Server Configuration Manager:
To add -T2371 to the SQL Server startup parameters:
Changing this parameter requires a restart of SQL Server.
You can find Microsoft's documentation for modifying startup parameters at https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/scm-services-configure-server-startup-options?view=sql-server-2014
Alternatively you can enable the flag globally for the running service without a restart by running the following query against the master database, using SQL Server Management Studio:
DBCC TRACEON (2371, -1);
This enables the trace flag until the next restart of SQL Server.
We recommend performing both steps, as this effectively allows you to enable the trace flag without an unscheduled restart of SQL Server.
You can check which trace options are currently enabled by running:
DBCC TRACESTATUS;
If no results are returned, no trace flags are enabled.
Enabling Auto Update Statistics Asynchronously per Database
For each database that might have a large number of rows and might cause unexpected delays while updating statistics, asynchronous updating of statistics should be enabled. This can be found on the "Options" page of the database properties:
This setting also requires Auto Update Statistics to be set to True, which is already the default for all new databases in SQL Server as long as the model database has not been modified.
To ensure this setting works as expected it is also best practice to ensure that the database Compatibility Level matches the version of SQL Server you are on. This setting is persisted as part of database backups, restores, and SQL migrations, so could be set to an older version of SQL Server.
You can also run the following queries with sysadmin or dbowner privileges to enable these settings programmatically:
ALTER DATABASE [YourDatabaseName] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [YourDatabaseName] SET AUTO_UPDATE_STATISTICS_ASYNC ON
GO
ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 120
GO
For the compatibility level you will need to find out the level for your version of Microsoft SQL: 130 = SQL 2016, 120 = SQL 2014, 110 = SQL 2012
Further Reading
- Official Microsoft KB article regarding trace flag 2371: https://support.microsoft.com/en-gb/help/2754171/controlling-autostat-auto-update-statistics-behavior-in-sql-server
- Official Microsoft documentation regarding database compatibility levels: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-2017
- Microsoft blog article about the SQL Server 2016 changes and potential pitfalls: https://blogs.msdn.microsoft.com/psssql/2016/10/04/default-auto-statistics-update-threshold-change-for-sql-server-2016/
- Useful blog article explaining the Auto Statistics options and their impact: https://www.virtual-dba.com/sql-server-statistics-trace-flag-2371/
Share this page
