Toggle menu

Performance Improvements - SQL Server and SQL Databases

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 and have to be done before running an upgrade from to Performance improvements can also be gained in iCM

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: 

SQL Server Configuration Manager

To add -T2371 to the SQL Server startup parameters:

SQL Server Properties - Trace Flag 2371

Changing this parameter requires a restart of SQL Server.

You can find Microsoft's documentation for modifying startup parameters at

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:


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:

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:


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

Last modified on May 18, 2021

Share this page

Facebook icon Twitter icon email icon


print icon