Cardinality Estimation Framework Version Control March 30, 2018 by Dmitry Piliugin This is a small post about how you may control the cardinality estimator version and determine which version was used to build a plan. The version of the cardinality framework is determined by the query database context, where the database has a specific compatibility level. When you create a database in SQL Server 2014 it has the latest compatibility level equals 120 by default. If you issue a query in that database context, the new cardinality version will be used. You may verify this by inspecting the plan property “CardinalityEstimationModelVersion” of the root language element (the one with the green icon), SELECT, for example. If you issue a query in a database context that is less than 120 level compatibility, the old version of the cardinality estimation framework will be used. If the new cardinality estimation version was used you’ll see the value 120 (you may remember it as 12.0 – the SQL Server 2014 version number) or 70 (also the server version number 7.0, because the old one was first introduced in early 1998 for the SQL Server 7.0 and then was evolved without major concept changes). Here is how it looks like: Note: Please, make sure, when searching the CardinalityEstimationModeVersion property, that you are using the SQL Server Management Studio 2014. If you use SSMS 2012, for example, it will silently throw it out, even without telling you, and you won’t see it! When Microsoft developed the new cardinality estimation framework, there was a goal to improve the quality of plans, however, there wasn’t a goal to avoid any regressions. That means that you should be prepared to regressions and know how to make an easy fix. Also, you should test your workload before moving on to the new cardinality mechanism. Exactly for that purpose, Microsoft issued two new trace flags, that are officially documented. TF 2312 to force the new (120 and later) cardinality estimation behavior, the exact CE will depend on the database compatibility level, and TF 9481 to force the old one, also regardless of the compatibility level. You may use these flags on various levels, globally, session or query. That opens a lot of possible scenarios, i’ll enlist some of them: You want to use the new features, like parallel SELECT INTO, but don’t want to enable the new CE – enable TF 9481 globally. You want to try the new CE, but you are afraid of regressions – leave the compatibility level lower than 120 and enable the new CE in a particular query with the TF 2312, using QUERYTRACEON option, to test it. You enabled the new CE and everything is fine, except a few queries that had a better plan with the old CE- use TF 9481 and the option QUERYTRACEON to leverage these particular queries. You may imagine the scenario you want, what is needed to remember, that you have a full control over what’s happening – if you don’t like it – simply turn it off. The next question naturally is, what if both TFs are enabled. In this case, they neutralize each other and the behavior is if no TF were enabled. Now we are ready to move on to the changes of the new cardinality estimation framework! Table of Contents Cardinality Estimation Role Cardinality Estimation Place in the Optimization Process Cardinality Estimation Concepts Cardinality Estimation Process Cardinality Estimation Framework Version Control Filtered Stats and CE Model Variation Join Containment Assumption and CE Model Variation Overpopulated Primary Key and CE Model Variation Ascending Key and CE Model Variation MTVF and CE Model Variation References ALTER DATABASE (Transact-SQL) Compatibility Level Enable plan-affecting SQL Server query optimizer behavior that can be controlled by different trace flags on a specific-query level Identifying SQL Server 2014 New Cardinality Estimator issues and Service Pack 1 improvement See more To view and analyze SQL Server query execution plans for free, check out ApexSQL Plan About Latest Posts Dmitry PiliuginDmitry is a SQL Server enthusiast from Russia, Moscow. He started his journey to the world of SQL Server more than ten years ago. Most of the time he was involved as a developer of corporate information systems based on the SQL Server data platform.Currently he works as a database developer lead, responsible for the development of production databases in a media research company. He is also an occasional speaker at various community events and tech conferences. His favorite topic to present is about the Query Processor and anything related to it. Dmitry is a Microsoft MVP for Data Platform since 2014. Latest posts by Dmitry Piliugin (see all) Query Trace Column Values - April 23, 2018 Runtime Constants Sniffing - April 23, 2018 Few Outer Rows Optimization - April 20, 2018 Related posts: Cardinality Estimation Role Cardinality Estimation Process Cardinality Estimation Concepts Cardinality Estimation Place in the Optimization Process What’s new in SQL Server 2014 Cardinality Estimator?