// Week 16: Cardinality Estimation From SQL Server 2014

Hello and welcome back to the SQL Server Performance Tuning series. In the last week we have talked about some problems with the cardinality estimation process in SQL Server. Today we will continue, and we will have a more detailed look on the shiny new Cardinality Estimator that is introduced with SQL Server 2014.

The new Cardinality Estimator

One of the enhancements in SQL Server 2014 is a new Cardinality Estimator. You have already learned last week that the old one has some limitations, that can produce a wrong estimation, which can lead to bad performing execution plans. Till SQL Server 2012 you have used the Cardinality Estimator that was introduced back with SQL Server 7.0!

Of course, there were bug fixes over the years, but they were not enabled by default - you needed to enable specific trace flags in SQL Server to get access to these fixes. With that approach Microsoft made sure that they are not introducing so-called Plan-Quality Regressions. Therefore the new Cardinality Estimator in SQL Server 2014 is the first major change in that area since SQL Server 7.0.

The goal of the new Cardinality Estimator is to improve the quality of your execution plans. But of course, there can be situations where you will see plan regressions. Therefore you have to evaluate very carefully if the new Cardinality Estimator makes sense for your workload and your specific queries. For that reason SQL Server 2014 introduces (again) different trace flags with which you can influence how the Query Optimizer works.

To make use of the new Cardinality Estimator your database has to have the Database Compatibility Level 120. When you are restoring or attaching a database from a previous SQL Server version, your compatibility level is not changed - therefore the Query Optimizer will not use the new Cardinality Estimator. With the following query you can check very easy the compatibility level of each database on your SQL Server instance:

SELECT name, compatibility_level FROM sys.databases
GO

If you have an execution plan in front of you, you can also check the properties window for the SELECT operator to see the value of the property CardinalityEstimationModelVersion. The value of 70 means that the old Cardinality Estimator is used, and 120 means that the new one is in use.

In addition SQL Server 2014 provides you the following 2 new trace flags:

1. 2312
2. 9481

With the trace flag 2312 you can indicate that you want to use the new Cardinality Estimator of SQL Server 2014 (e.g. when you are using a compatibility level below 120). If you want to revert back to the old Cardinality Estimator, trace flag 9481 is your friend. You can set these trace flags on the instance level, on the session level, and also on the query level through the query hint QUERYTRACEON. Let’s have a look on the following example, where trace flag 2312 is used to enforce the new Cardinality Estimator.

SELECT * FROM Person.Person
OPTION (QUERYTRACEON 2312)
GO

The new Cardinality Estimator provides you a lot of different changes, which can lead to better estimations and probably better execution plans. Microsoft has reworked the Cardinality Estimator in the following areas:

1. Estimations for Multi-Column predicates
2. How to deal with the Ascending Key Column problem
3. Estimations for JOIN predicates
4. Troubleshooting through Extended Events

If you want to have a more detailed look on these various changes, I highly recommend to read the whitepaper Optimizing your Query Plans with the SQL Server 2014 Cardinality Estimator, written by Joe Sack.

Summary

In this installment of the SQL Server Performance Tuning series I have given you an overview about the new Cardinality Estimator that is introduced with SQL Server 2014.

In this month we have done a really huge deep dive into statistics in SQL Server! As you have seen over the last 4 weeks, their accuracy is really important to get good performing execution plans. With next week, the 5th month of the SQLpassion Performance Tuning Training Plans starts, where you will learn more about Locking, Blocking, and Deadlocking in SQL Server. Stay tuned.