The Cardinality Estimator is a core function of the SQL Server query optimizer which evaluates the uniqueness and skew of row data in order to produce efficient execution plans. A redesigned Cardinality Estimator was introduced with SQL Server 2014.
Since the introduction of support for SQL Server 2014, OnBase performance degradations have been reported to Hyland Software. In many cases, the re-designed Cardinality Estimator was found to be the source of the issue and reverting to the previous one resolved the issue.
Issues where the re-design is the source can include:
- Queries whose execution plans have excessively long compilation times
- Inefficient access methods
- Inaccurate row count estimates
Note: The re-designed Cardinality Estimator will only be enabled when the database compatibility level is set to SQL Server 2014 (120) or higher.
If OnBase performance degradations are noticed after upgrading to SQL Server 2014 or higher, consider reverting to the original Cardinality Estimator. Consult the following Microsoft Support article for more information:
https://support.microsoft.com/en-us/help/3044519/fix-query-performance-issues-when-newcardinality-estimator-is-enabled
To disable the re-designed Cardinality Estimator in SQL Server 2016 and higher, navigate to the properties of the database and set the Legacy Cardinality Estimation option to ON.
