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 re-designed 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 Cardinality Estimator resolved the issue. Issues where the re-designed Cardinality Estimator are the source can include queries whose execution plans have excessively long compilation times, inefficient access methods, and inaccurate row count estimates.
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 disabling the re-designed Cardinality Estimator.
To disable the re-designed Cardinality Estimator in SQL Server 2014, consult the following Microsoft Support article for information on reverting to the original Cardinality Estimator: https://support.microsoft.com/en-us/help/3044519/fix-query-performance-issues-when-new-cardinality-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.