Glenn Berry

Compatibility Levels and Cardinality Estimation Primer

January 29, 2019 by in SQL Performance, System Configuration | 4 Comments
Database smarter with deeper insights for Microsoft SQL
SentryOne eBooks

In these books, you will find useful, hand-picked articles that will help give insight into some of your most vexing performance problems. These articles were written by several of the SQL Server industry’s leading experts, including Paul White, Paul Randal, Jonathan Kehayias, Erin Stellato, Glenn Berry, Aaron Bertrand, and Joe Sack.

Free Download →

Featured Author

Itzik is a T-SQL trainer, a co-founder of SolidQ, and blogs about T-SQL fundamentals and query tuning.

Itzik’s Posts →

Introduction

Between 1998 and early 2014, SQL Server used one cardinality estimator (CE), but would introduce a new database compatibility level with every new major version of SQL Server, (with the exception of SQL Server 2008 R2). The native compatibility levels for SQL Server are shown by major SQL Server version in Table 1:

SQL Server Version Native Compatibility Level
SQL Server 7.0 70
SQL Server 2000 80
SQL Server 2005 90
SQL Server 2008
SQL Server 2008 R2		 100
SQL Server 2012 110
SQL Server 2014 120
SQL Server 2016 130
SQL Server 2017 140
SQL Server 2019 150

Table 1: SQL Server Versions and Native Compatibility Levels

Between SQL Server 7.0 and SQL Server 2012, there was no connection between the compatibility level of a database and the cardinality estimator that queries in that database would use. This is because there was just the one cardinality estimator, which received a major update in 1998. The compatibility level of a database was only used for backwards functional compatibility and to enable/disable some new features in each new version of SQL Server (see this Stack Exchange answer for examples of how behavior changed between 80 and 90, probably the most disruptive change). Unlike the file version of a SQL Server database, you can change the compatibility level of a database at any time, to any supported compatibility level, with a simple ALTER DATABASE command.

By default, if you created a new database in SQL Server 2012, the compatibility level would be set to 110, but you could change it to an earlier level if you wished. If you restored a database backup from a SQL Server 2008 instance onto a SQL Server 2012 instance, it would upgrade the file version of the database, but would leave the compatibility level where it had been on the SQL Server 2008 instance (unless it was 80, which would get upgraded to 90, the minimum version supported by SQL Server 2012). Besides knowing the fundamental difference between the file version of a database and the compatibility level of a database, most DBAs and developers didn’t have to worry very much about database compatibility levels before SQL Server 2014 was released. In many cases, most databases never had their compatibility levels changed after a migration to a new version of SQL Server. This usually didn’t cause any issues unless you actually needed a new feature or behavior that changed in the latest database compatibility level.

SQL Server 2014 Changes

This old state of affairs changed radically with the release of SQL Server 2014. SQL Server 2014 introduced a “new” cardinality estimator that was enabled by default when a database was in 120 compatibility level. In the classic whitepaper, “Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator,” Joe Sack explains the background and behavior of this change back in April of 2014. In many cases, most of your queries ran faster when using the new cardinality estimator, but it was fairly common to run into some queries that had major performance regressions with the new cardinality estimator. If that happened, SQL Server 2014 did not have that many options for alleviating the performance issues caused by the new CE. Joe’s whitepaper covers those options in great detail, but essentially, you were limited to instance-level trace flags or query-level query hints to control which cardinality estimator was used by the query optimizer, unless you wanted to revert to compatibility level 110 or lower.

SQL Server 2016 Changes

SQL Server 2016 introduced database scoped configuration options, which give you the ability to control some behaviors that were formerly configured at the instance level, using an ALTER DATABASE SCOPED CONFIGURATION command. In SQL Server 2016, these options included MAXDOP, LEGACY_CARDINALITY ESTIMATION, PARAMETER_SNIFFING, and QUERY_OPTIMIZER_HOTFIXES. There was also a CLEAR PROCEDURE_CACHE option that let you clear the entire plan cache for a single database.

Most relevant in this context are the LEGACY_CARDINALITY ESTIMATION and QUERY_OPTIMIZER_HOTFIXES database scoped configuration options. LEGACY_CARDINALITY ESTIMATION enables the legacy CE regardless of the database compatibility level setting. It is equivalent to trace flag 9481, but it only affects the database in question, not the entire instance. It allows you to set the database compatibility level to 130 in order to get a number of functional and performance benefits, but still use the legacy CE database-wide (unless overridden by a query-level query hint).

The QUERY_OPTIMIZER_HOTFIXES option is equivalent to trace flag 4199 at the database level. SQL Server 2016 will enable all query optimizer hotfixes before SQL Server 2016 RTM when you use the 130 database compatibility level (without enabling trace flag 4199). If you do enable TF 4199 or enable QUERY_OPTIMIZER_HOTFIXES, you will also get all of the query optimizer hotfixes that were released after SQL Server 2016 RTM.

SQL Server 2016 SP1 also introduced the USE HINT query hints that are easier to use, understand, and remember than the older QUERYTRACEON query hints. This gives you even more fine-grained control over optimizer behavior that is related to database compatibility level and the version of the cardinality estimator that is being used. You can query sys.dm_exec_valid_use_hints to get a list of valid USE HINT names for the exact build of SQL Server that you are running.

SQL Server 2017 Changes

The new adaptive query processing feature was added in SQL Server 2017, and is enabled by default when you are using database compatibility level 140.

Microsoft is trying to move away from the old terminology of “New CE” and “Old CE”, since there are actually changes and fixes to query optimization in each new major version of SQL Server. Because of this, there is no single “New CE” anymore. Instead, Microsoft wants to refer to CE70 (default CE for SQL Server 7.0 through SQL Server 2012), CE120 for SQL Server 2014, CE130 for SQL Server 2016, CE140 for SQL Server 2017, and CE150 for SQL Server 2019. Starting with SQL Server 2017 CU10, you can use the USE HINT functionality to control this with query hints. For example:

/*...query...*/ OPTION (USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_130'));

… would be a valid query hint to force the CE130 cardinality estimator for a particular query.

SQL Server 2019 Changes

SQL Server 2019 is adding even more performance improvements and behavior changes that are enabled by default when a database is using compatibility mode 150. A prime example is scalar UDF inlining. Another example is the intelligent query processing feature, which is a superset of the adaptive query processing in SQL Server 2017.

There are five new USE HINT options, including ways to disable batch mode or disable adaptive memory grant feedback, as shown in Table 2:

DISABLE_BATCH_MODE_ADAPTIVE_JOINS
DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK
DISABLE_INTERLEAVED_EXECUTION_TVF
DISALLOW_BATCH_MODE
QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150

Table 2 : New USE HINT options

And there are also sixteen new database scoped configuration options (as of CTP 2.2) that give you database-level control of more items that are also affected by trace flags or database compatibility level. It gives you more fine-grained control of higher level changes that are enabled by default with database compatibility level 150. These are listed in Table 3:

ACCELERATED_PLAN_FORCING ELEVATE_RESUMABLE ROW_MODE_MEMORY_GRANT_FEEDBACK
BATCH_MODE_ADAPTIVE_JOINS GLOBAL_TEMPORARY_TABLE_AUTO_DROP TSQL_SCALAR_UDF_INLINING
BATCH_MODE_MEMORY_GRANT_FEEDBACK INTERLEAVED_EXECUTION_TVF XTP_PROCEDURE_EXECUTION_STATISTICS
BATCH_MODE_ON_ROWSTORE ISOLATE_SECURITY_POLICY_CARDINALITY XTP_QUERY_EXECUTION_STATISTICS
DEFERRED_COMPILATION_TV LIGHTWEIGHT_QUERY_PROFILING
ELEVATE_ONLINE OPTIMIZE_FOR_AD_HOC_WORKLOADS

Table 3 : New database scoped configuration options

Conclusion

Migrating to a modern version of SQL Server (meaning SQL Server 2016 or newer) is significantly more complicated than it was with legacy versions of SQL Server. Because of the changes associated with the various database compatibility levels and various cardinality estimator versions, it is actually very important to put some thought, planning, and actual testing into what database compatibility level you want to use on the new version of SQL Server that you are migrating your existing databases to.

Microsoft’s recommended upgrade process is to upgrade to the latest SQL Server version, but keep the source database compatibility level. Then, enable Query Store on each database and collect baseline data on the workload. Next, you set the database compatibility level to the latest version, and then use Query Store to fix performance regressions by forcing the last known good plan.

You really want to avoid a haphazard “blind” migration where you are blissfully unaware of how this works and how your workload will react to these changes. Changing the database compatibility level to an appropriate version and using the appropriate database scoped configuration options, along with appropriate query hints where absolutely necessary, is extremely important with modern versions of SQL Server.