16th/24 weeks cardinality calculation in SQL Server 2014

Hello everyone, welcome back to Performance tuning training . Last week we discussed some of the problems in the cardinality calculation process in SQL Server. Today we continue with a detailed discussion of the new cardinality calculations introduced in SQL Server 2014.

New Cardinality calculation

An enhancement in SQL Server 2014 is the new cardinality calculation. Last week you've learned that the old cardinality calculation has some limitations that generate erroneous estimates, which can lead to poor execution plan performance. As of SQL Server 2012, you have been using cardinality calculations that were introduced from SQL Server 7.0.

Of course, many problems have been fixed in the past few years, but they are not enabled by default-you need to enable the trace flags specified in SQL Server to make these corrections effective. In that way Microsoft ensures that they do not introduce so-called planned quality Degradation (plan-quality regressions). Therefore the new cardinality calculation in SQL Server is the first significant change in that field since SQL Server 7.0.

The purpose of the new cardinality calculation is to improve the quality of your execution plan. But of course, there are situations where you will see a deterioration of the plan. Therefore, for your workload and for the specified query, you should carefully evaluate whether the new cardinality calculation is available. So SQL Server 2014 again introduces different trace flags that you can use to influence how the query optimizer works.

in order to use the new cardinality calculation, your database must have the database compatibility level set to 120. When you restore or attach a database from a previous version of SQL Server, your compatibility level will change-so the query optimizer will not use the new cardinality calculation. Using the following query you can easily derive the compatibility level for each database in your instance of SQL Server:

1 SELECT  from sys.databases 2 3 GO

If you have an execution plan in front of you, you can look at its Properties window on the Select operator and see the value of the cardinalityestimationmodelversion property. 70 means using the old cardinality calculation, 120 means using the new cardinality calculation.

Additionally, SQL Server 2014 provides the following 2 new trace flags:

• 2312
• 9481

Using the 2312 trace flag You can point out that you want to use SQL Server 2014 for new cardinality calculations (for example, when you want to use a compatibility level below 120). If you want to go back to the old cardinality calculation, you can use the 9481 trace flag. You can set these trace flags at the instance level, at the session level, or by querytraceon query hints. Let's take a look at the following example, using a 2312 trace flag to force a new cardinality calculation.

1 SELECT *  from Person.person 2 OPTION 2312 )3GO

New cardinality calculations provide a lot of changes that can lead to better estimates and more likely to lead to better execution plans. Microsoft has rewritten the following areas of cardinality calculation:

• Estimates for multiple-column predicates
• How to handle the self-increment key column problem
• Estimation of the JOIN predicate
• Troubleshooting through Extended Events

If you want to know more details about these changes, I highly recommend reading the white paper written by Joe Sack. Optimize your query plan with SQL Server 2014 cardinality calculations (optimizing Your query plans with the SQL Server 2014 Cardinality estimator).

Summary

In this 1-period Performance Tuning Training I gave you an overview of the new cardinality calculations introduced by SQL Server 2014.

This month we have done a very deep study of the statistics in SQL Server! Over the past 4 weeks, we have seen that they are really important to the implementation plan for good performance. Starting next week, the 5th-month Performance tuning training begins, and you'll learn about locks, blockages, and deadlocks in SQL Server (Locking, Blocking, and deadlocking). Keep your eye on it!

16th/24 weeks cardinality calculation in SQL Server 2014