Database Performance - Legacy Cardinality Estimation
This article is only applicable when using SQL Server version of 2014 or higher. When using KTA version of 7.7 or lower this setting is essential for good performance.
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. This recommendation is not included in the best practices guide of KTA 7.6 and 7.7, however it is still an important part of improving performance in the Documents database.
KTA 7.8 makes significant changes in the structure of the Documents database aimed at improving performance which should prevent the need for this setting.
How to Enable Legacy Cardinality Estimation
SQL Server 2016 and Later Including Azure
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 (non-Azure). Execute the script in the specific way described below.
SELECT @@VERSION AS Version SELECT ServerProperty('ServerName') AS ServerName --If SQL 2016+ non-Azure IF CONVERT(sysname, SERVERPROPERTY ('ProductMajorVersion')) > 12 AND CONVERT(sysname, SERVERPROPERTY ('EngineEdition'))<5 BEGIN RAISERROR(' *** Database scoped configurations (LCE/MAXDOP 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'';'; END --If Azure IF CONVERT(sysname, SERVERPROPERTY ('EngineEdition'))>4 BEGIN RAISERROR(' *** AZURE SQL: Please query provide results of the following query for each KTA database: *** ', 0, 1) WITH NOWAIT RAISERROR(' SELECT DB_NAME() AS DBName, * FROM sys.database_scoped_configurations', 0, 1) WITH NOWAIT RAISERROR(' ', 0, 1) WITH NOWAIT END -- 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.name, 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(' *** DBCC TRACESTATUS *** ', 0, 1) WITH NOWAIT RAISERROR(' For SQL Server 2014, if trace flag 9481 is listed below, then LCE is enabled. ', 0, 1) WITH NOWAIT DBCC TRACESTATUS(-1)
Azure SQL: Note that in Azure SQL the above script cannot enumerate the separate databases, so either connect to each KTA DB separately and get the results from the following:
SELECT DB_NAME() AS DBName, * FROM sys.database_scoped_configurations
Or, if your user has access to each database, replace default names with the database names from your environment and get results from the following query:
SELECT 'TotalAgility' AS DBName, * FROM TotalAgility.sys.database_scoped_configurations UNION ALL SELECT 'TotalAgility_Documents' AS DBName, * FROM TotalAgility_Documents.sys.database_scoped_configurations UNION ALL SELECT 'TotalAgility_Reporting' AS DBName, * FROM TotalAgility_Reporting.sys.database_scoped_configurations UNION ALL SELECT 'TotalAgility_Reporting_Staging' AS DBName, * FROM TotalAgility_Reporting_Staging.sys.database_scoped_configurations ORDER BY name, DBName
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.
Level of Complexity
Article # 3034963