Question:

Two-part:  What is different in the new Cardinality Estimator, and how can I check to see which one I’m using?

Answer:

The cardinality estimator (“CE”) was changed in SQL Server 2014.  New assumptions and logic were added based upon a modern workload (OLTP and Data Warehousing).  These new additions have shown, according to Microsoft, that most customers experience query performance enhancement, or at the least they remain the same; while a small amount of customers have seen regressions.

Microsoft doesn’t document much about the query optimizer, but their online documentation does explain some of what’s going on.  Their examples are shown in a “new” and “old” format of what happens now vs. what happened with the old estimator.

Microsoft has provided a couple ways to change the database configuration to enable the new cardinality estimator.  Trace Flags: 9481 and 2312 have been provided to disable/enable the new (2014) cardinality estimator.  You can apply these flags to an individual query using the OPTION (QUERYTRACEON  9481) statement.

SELECT * FROM Sales.SalesOrderDetail SOD
WHERE SOD.SalesOrderID = 3124
OPTION (QUERYTRACEON 9481); — use legacy CE

Alternatively, to set the entire database to use the new CE, you use the ALTER DATABASE statement.

ALTER DATABASE <DATABASE_NAME> SET COMPATABILITY LEVEL = 120;

You could also used DBCC TRACEON with a -1 option to make it global, or use the –T option in the startup configuration.  This allows you to use the features of SQL Server 2014, while keeping the legacy CE.

So getting back to what changes have actually been made, Microsoft examples show us the following:

  1. Ascending Key Issue: where statistics may not contain a specific value since they were last updated.  For instance, data being inserted on a daily basis, but the statisitics may not have been updated to reflect the latest data.  A Where predicate based upon a date for instance where the predicate contains a date later than what is reflected in the current statistics.
  2. Correlation between filtered predicates on the same table: in a query where you “AND” columns in the WHERE predicate that contain a correlation – such as City and ZipCode, the old CE would not draw a correlation between the two.  The new CE makes an assumption that there is a correlation between the two columns and uses a different algorithm to estimate the data distribution.
  3. Filtered predicates on different tables: rather than making the assumption that filtered columns from different tables “AND’ed” together are correlated, the new CE assumes they are not correlated.  This is based on the modern workloads tested.

Prior to migrating older databases (pre-2014) to the new CE in a production system, Microsoft suggests testing the database against the new CE.  This can be done in a testing environment using the two methods I mentioned earlier in this blog – Alter Database and Trace Flags.  This allows you to identify any potential regressions on a test box, and fix them prior to a production migration.  Any databases migrated from an older version to a 2014 instance will retain the legacy CE by default.

To view the compatibility level of databases on an instance, use the sys.databases DMV, and check the compatibility_level column:

SELECT DB.compatibility_level AS [Compatibility Level]
             , DB.name AS [Database]
FROM sys.databases DB;

To validate the CE being used within a query, you have a couple options.  One is to include the Actual Query Execution plan.  Consider the query:

SELECT *
FROM SALES.SalesOrderDetail SOD;

SSMS QEP capture 1

Right-click the “Select” operator and choose “Properties”:

Select Properties 1

Under “CardinalityEstimationModeVersion”,  you’ll see either 70, for the old CE, or 120 for the new CE.

*NOTE:  70 does not mean that the database is using the CE from SQL Server 7.0, 70 is used for all the legacy versions.  70 = old, 120 = new CE.

The other option is to view the XML from the Query Execution Plan:

SSMS QEP capture 1

 

 

Select Properties XML

Right-click in the result window and select “Show Execution Plan XML

Once you have the XML screen, find the tag:  StmtSimple.

You should see:

<StmtSimple … CardinalityEstimationModelVersion=“70”

To turn on / off the new CE on an individual query, you can use the QUERYTRACEON option.  Here is an example of turning “off” the new CE:

SELECT *
FROM HR.Employees
OPTION (QUERYTRACEON 9481); — 9481 old CE

Here is an interesting scenario using the DBCC TRACEON command to set the CE to the old CE:

DBCC TRACEON (9481, -1) — old CE, make it global
GO

Indeed, if I perform a query of any of my user databases on the instance, I’ll see that the legacy CE is
being used. However, if I use the QUERYTRACEON option show above, with the 2312 value, that query
execution plan will overwrite the global setting and use the new (120) CE. Here is the code:

DBCC TRACEON (9481, -1) — set to old CE globally
GO
USE TSQLFundamentals2008
GO
SELECT *
FROM HR.Employees
OPTION (QUERYTRACEON 2312); — option to use new CE for query

Oh, and while all this is happening, the Compatibility Level stays the same. Here is the query to prove it:

SELECT DB.compatibility_level AS [Compatibility Level]
            , DB.name AS [Database]
FROM SYS.databases DB
WHERE DB.name IN (‘AdventureWorks2014’, ‘TSQLFundamentals2008’)
ORDER BY [Compatibility Level] DESC;

Compatibility Level query output

 

*NOTE:  AdventureWorks2014 is showing a 110 Level because I changed it manually earlier.

So, TSQLFundementals database will use the legacy CE, but keep the Compatibility Level of 120 (2014), unless the querytraceon option is used.

Isn’t science fun!

Summary

In this blog I’ve shown you how to enable and disable the new cardinality estimator.  I’ve discussed how to set this at the instance level, and at the query level using querytraceon.  Finally, I’ve shown how to validate the version of the cardinality estimator being used by including the QEP and looking at the properties, or the XML version of the plan.

There is a lot more to discuss about the new cardinality estimator:  how it acts with filtered predicates; associations derived between related columns; and how joins are computed using the new estimator.  I’ll post another blog with some examples of these.

You can review a white paper written by Joseph Sack that goes into detail on the items I’ve mentioned above.  Check out the references for the link.

Knowledge is Power!

References

Microsoft SQL Server – Optimize your Query Plans with the SQL Server 2014 Cardinality Estimator,  written by Joseph Sack:

http://download.microsoft.com/download/D/2/0/D20E1C5F-72EA-4505-9F26-FEF9550EFD44/Optimizing%20Your%20Query%20Plans%20with%20the%20SQL%20Server%202014%20Cardinality%20Estimator.docx

MSDN:

MSDN blog on the new Cardinality Estimator (Two parts):

https://blogs.msdn.microsoft.com/psssql/2014/04/01/sql-server-2014s-new-cardinality-estimator-part-1/

https://blogs.msdn.microsoft.com/saponsqlserver/2014/01/16/new-functionality-in-sql-server-2014-part-2-new-cardinality-estimation/