Skip to main content

KTA Database Performance: Legacy Cardinality Estimation


SQL Server Cardinality Estimation

This article is only applicable with the combination of a SQL Server version of 2014 or higher, and a KTA version of 7.5 or lower, and is essential for good performance in these combinations.

Starting with SQL Server 2014, Microsoft made significant changes to cardinality estimation, which determines how query plans are generated, and therefore how queries are executed.  KTA 7.5 and earlier are developed and tuned against the previous cardinality estimation model, and therefore perform better when using legacy cardinality estimation. 

In many cases, especially under load, the difference is dramatic.  That is why the KTA 7.5 Best Practices Guide recommends using legacy cardinality estimation for each of the KTA databases.  This recommendation also applies to previous versions of KTA.  Starting with KTA 7.6, this setting is no longer necessary.

Enabling Legacy Cardinality Estimation

SQL Server 2016 and Later

In SQL Server 2016 and later, legacy cardinality estimation is available as a database level setting.  In SQL Server Management Studio, for each database:

  • Right click on the database and click Properties
  • Go to Options, and scroll down to the group named Database Scoped Configurations
  • Change Legacy Cardinality Estimation to ON
  • In this same spot is another setting critical for performance: MAXDOP.


SQL Server 2014

SQL Server 2014 was the first version with the new cardinality estimator and does not have the clear-cut setting that was added later in 2016.  To use the legacy cardinality estimator without the specific setting, change the compatibility level to 110, which is the level used in SQL Server 2012.  For each database:

  • Right click on the database and click Properties
  • Click Options
  • Change Compatibility level to SQL Server 2012 (110)


It is also possible to use trace flag 9481, however this is a more complicated route.

SQL Server 2012 and Earlier

This setting is not applicable to SQL Server 2012 or earlier.  It already uses legacy cardinality estimation.

Checking Legacy Cardinality Estimation Status

Instead of using the version specific instructions to check the properties of the instance or each database, the following script can be run the check the Legacy Cardinality Estimation setting at each level regardless of SQL Server version.  Execute the script in the specific way described below.

SELECT @@VERSION AS Version, ServerProperty('ServerName')

--If SQL 2016
declare @SqlServerProductMajorVersion smallint ; SELECT @SqlServerProductMajorVersion = CONVERT(sysname, SERVERPROPERTY ('ProductMajorVersion')); 
IF @SqlServerProductMajorVersion > 12 
    -- For SQL 2016+
    RAISERROR(' *** Database scoped configurations (LCE in SQL 2016+) *** ', 0, 1) WITH NOWAIT
    EXEC sys.sp_MSforeachdb N'SELECT name, CAST(value AS NVARCHAR(1)), N''?'' AS DatabaseName FROM ?.sys.database_scoped_configurations WHERE name=''LEGACY_CARDINALITY_ESTIMATION'';'; 
-- Requires VIEW ANY DATABASE server-level permission
RAISERROR(' *** Database compatiblity level *** ', 0, 1) WITH NOWAIT
RAISERROR(' Compatibility level of 110 or lower already uses LCE ', 0, 1) WITH NOWAIT
SELECT, d.compatibility_level
FROM sys.databases AS d  

-- Check if any trace flags are enabled (notably 9481 enables LCE prior to new option in SQL Server 2016) 
-- Requires membership in the public role
RAISERROR(' For SQL Server 2014, if trace flag 9481 is listed below, then LCE is enabled. ', 0, 1) WITH NOWAIT

How to run this script

Because this script returns many different result sets, it is substantially more convenient to save the results by running the scripts this way:

  • After opening a script in SQL Management Studio, Click the “Query” menu, then under “Results To…”, choose “Results to File”.
  • Alternatively, use the shortcut: Ctrl + Shift + F.
  • Now when you execute the script it will prompt to save the entire set of results as a file.
  • It is important to save the results using a descriptive filename.


Applies to:  

Product Version
KTA 7.5
KTA 7.4
KTA 7.3