Skip to main content
Kofax

KTA Database Performance: Legacy Cardinality Estimation

3020866

SQL Server 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.

Enabling 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.

LegacyCardinalityEstimation.png

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)

CompatibilityLevel.png

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.

Checking Legacy Cardinality Estimation Status In Azure

 

Applies to:  

Product Version
KTA 7.5
KTA 7.4
KTA 7.3