SQL Server 2014’s new cardinality estimator (Part 1)

One of the performance improvement in SQL Server 2014 is the redesign of cardinality estimation. The component which does cardinality estimation (CE) is called cardinality estimator. It is the essential component of SQL query processor for query plan generation. Cardinality estimates are predictions of final row count and row counts of intermediate results (such as joins, filtering and aggregation). These estimates have direct impact on plan choices such as join order, join type etc. Prior to SQL Server 2014, cardinality estimator was largely based on SQL Server 7.0 code base. SQL Server 2014 introduces new design and the new cardinality estimator is based on research on modern workloads and learning from past experience.

A whitepaper planned by the SQL Server product team will document specific scenarios where new and old cardinality estimators differ. We will follow up with a later blog post when that paper is released. Additionally, Juergen Thomas has posted an overview of the feature on "Running SAP on SQL Server blog".

In this blog, we will provide a quick overview about controlling the SQL Server 2014 feature, guidelines on troubleshooting issues. We have plans to release more blog posts related to SQL Server 2014 new cardinality estimator in the future.

One of the goals for this blog post is to help make customers aware of this feature for upgrades and new deployments as query plans may change. We encourage users test sufficiently prior to upgrading to avoid performance surprises.

New deployments vs upgrade

SQL Server 2014 uses database compatibility level to determine if new cardinality estimator will be used. If the database compatibility level is 120, new cardinality estimator will be used. If you create a new database on SQL Server 2014, compatibility level will be 120. When you upgrade or restore from a previous version to SQL 2014, a user database compatibility level will not be updated. In other words, you will continue to use old cardinality estimator in upgrade and restore situations by default. This is to avoid plan change surprises for upgrades. You can manually change the compatibility level to be 120 so that new cardinality estimator can be used. Please refer to online documentation on how to view and change database compatibility level. Be aware that changing database compatibility level will remove all existing query plans from the plan cache for the database.

Please note the following:

1. Which version of cardinality estimator to use is based on current database context where the query is compiled even if the query references multiple databases. Let's assume you have db1 with compatibility level of 120 and db2 with compatibility level of 110, and you have a query that references two databases. If the query is compiled under db1, new cardinality estimator will be used. But if the query is compiled under db2, old cardinality estimator will be used.
   • For distributed (linked server) queries, there are two servers (local and remote servers) and databases (local and remote databases) involved.  For the remote query, the compatibility level of default catalog is used to determine if new or old cardinality estimator will be used.  If you didn't specify default catalog for your linked server, the master database will be used.  This may not be obvious to you because you reference your remote objects using 4-part name.  Compatibility level of default catalog or master (in case one is not specified in linked server definition) will be a determining factor.
2. Regarding system databases and upgrade, the compatibility levels of model, msdb and tempdb will be changed to 120 following the upgrade to SQL Server 2012.  But the master system database retains the compatibility level it had before upgrade per online documenation.  Therefore in upgrade scenarios, if your query is compiled under the context of master, old cardinality estimator will be used, but new cardinality estimator will be used for a query compiled under the context model, msdb, or tempdb.
3. If your query references temporary tables, the database context under which the query is compiled determines which version of cardinality estimator to be used. In other words, if your query is compiled under a user database, the user database compatibility level (not tempdb) will determine which version of cardinality estimator to be used even though the query references temp table.

How to tell if you are using new cardinality estimator

There are two ways you can tell if new cardinality estimator is used.

In the SQL 2014 XML plan, there is a new attribute in StmtSimple called CardinalityEstimationModelVersion. When the value is 120, it means the new cardinality estimator is used. If the value is 70, it means the old cardinality estimator is used. This new XML attribute is only available for SQL 2014 and above (see screenshot below).

If you start capturing a new SQL Server 2014 XEvent called query_optimizer_estimate_cardinality, this event will be produced during compilation if new cardinality estimator is used. If the old cardinality estimator is used, this XEvent won't be produced even if you enable the capturing (see a screenshot below). We will talk more about how to use this XEvent to help troubleshoot cardinality issues in future blogs.

Additional ways to control new cardinality estimator

In addition to database compatibility level, you can use trace flags 2312 and 9481 to control if new or old cardinality estimator will be used. Trace flag 2312 is used to force new cardinality estimator while 9481 is used to force old cardinality estimator regardless of the database compatibility level setting. If you enable both trace flags, neither will be used to determine which version of cardinality estimator. Instead, database compatibility level will determine which version of cardinality estimator to be used. When such a case occurs, a new XEvent "query_optimizer_force_both_cardinality_estimation_behaviors" will be raised to warn user (if you enable this XEvent).

You can enable these trace flags at server, session or query level. To enable the trace flag at query level, you use QUERYTRACEON hint documented in 2801413. Below is an example query

select * from FactCurrencyRate where DateKey = 20101201 option (QUERYTRACEON 2312)

Since we have multiple ways to control the behavior, let's talk about order of precedence. If the query has QUERYTRACEON hint to disable or enable the new cardinality estimate, it will be respected regardless of server/session or database level settings. If you have a trace flag enabled at server or session level, it will be used regardless the database compatibility level setting. See the diagram below.

Guidelines on query performance troubleshooting with new cardinality estimator

When you run into issues with new cardinality estimator, you have a choice to revert to the old behavior. But we encourage you spend time troubleshooting the query and find out if the new cardinality estimator even plays a role in terms of your slow query performance. Basic troubleshooting query performance stays the same.

Regardless of the versions of cardinality estimators, the optimizer still relies on statistics for cardinality estimate. Make sure you enable auto update and auto create statistics for the database. Additionally, if you have large tables, auto update statistics threshold may be too high to trigger statistics update frequently. You may need to schedule jobs to manually update statistics.

You may not have sufficient indexes on tables involved for the slow query. Here are a few ways you can help tune your indexes.

1. XML Plan will display missing index warning for a query.
2. Missing index DMVs. SQL Server tracks potential indexes that can improve performance in DMVs. This blog has sample queries on how to use the DMVs. Additionally, SQL Nexus also has a report on missing indexes server wide.
3. Database Tuning Advisor (DTA) can be used to help you tune a specific query. Not only can DTA recommend indexes but also recommend statistics needed for the query. Auto create statistics feature of SQL Server doesn't create multi-column statistics. But DTA can identify and recommend multi-column statistics as well.

There are a few constructs that are known to have cardinality estimate issues but are not addressed by the new cardinality estimator. Below are a few common ones.

1. Table variables. You will continue to get low estimate (1) for table variables. This issue is documented in a previous blog.
2. Multi-statement table valued function (TVF): Multi-statement TVF will continue to get estimate of 100 instead of 1 in earlier version. But this can still cause issues if your TVF returns many rows. See blog for more details.
3. Behaviors of Table valued parameter (TVP) and local variables are unchanged. The number of rows of TVP at compile time will be used for cardinality estimate regardless if the rows will change for future executions. Local variables will continued to be optimized for unknown.

References

1. New cardinality estimator online documentation.
2. Juergen Thomas's blog on New cardinality estimator and SAP applications

In the future blogs, we will document more on how to use new XEvent query_optimizer_estimate_cardinality to troubleshoot query plan issues and how plan guide may be used to control the new cardinality estimator behavior.

Many thanks to Yi Fang, a Senior Software Design Engineer from SQL Server Query Processor team at Microsoft, for reviewing and providing technical details on this blog.

Jack Li - Senior Escalation Engineer and Bob Ward - Principal Escalation Engineer, Microsoft SQL Server Support

• Anonymous
  July 28, 2015
  great article. We're planning on upgrading/migrating so this is very valuable info! Thx a lot

• Anonymous
  January 29, 2017
  How can I detect sql bad performance in SQL 2014 if the same sql has good performance in SQL 2008 R2 - SQL 2012?

• Anonymous
  November 10, 2017
  In the section "Constructs not significantly addressed by the new cardinality estimator.", is there any evidence that CTEs might fall into that category?