In this post I just wanted to step back and discuss how to enable and identify the Cardinality Estimator version, at least as it stands in SQL Server 2014 CTP2.<\/p>\n

As you saw for earlier posts, I see the new CE behavior by changing a database\u2019s compatibility level as follows:<\/p>\n

\r\nUSE [master]\r\nGO\r\nALTER DATABASE [Credit] SET COMPATIBILITY_LEVEL = 120;\r\nGO\r\n\r\n<\/pre>\n
Then when I execute a query within the database, looking at the execution plan I see the following attribute in the StmtSimple element:<\/p>\n
<StmtSimple StatementCompId=”1″ StatementEstRows=”1600000″ StatementId=”1″ StatementOptmLevel=”FULL” CardinalityEstimationModelVersion=”120″<\/span> StatementSubTreeCost=”21.0963″ StatementText=”SELECT\u00a0\u00a0\u00a0 c.[charge_no],&#xD;&#xA;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 m.[lastname],&#xD;&#xA;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 m.[firstname]&#xD;&#xA;FROM [dbo].[charge] AS c&#xD;&#xA;INNER JOIN [dbo].[member] AS m ON&#xD;&#xA;\u00a0\u00a0\u00a0 c.[member_no] + ‘ ‘\u00a0 =\u00a0 m.[member_no] &#xD;&#xA;OPTION (RECOMPILE)” StatementType=”SELECT” QueryHash=”0xF698FF11488A3A3D” QueryPlanHash=”0x41F421A8F51D61CE” RetrievedFromCache=”false”><\/p><\/blockquote>\n
Note that if my database compatibility level was for an earlier level, I see the following:<\/p>\n
<StmtSimple StatementCompId=”1″ StatementEstRows=”1597640″ StatementId=”1″ StatementOptmLevel=”FULL” CardinalityEstimationModelVersion=”70″<\/span> StatementSubTreeCost=”21.0895″ StatementText=”SELECT\u00a0\u00a0\u00a0 c.[charge_no],&#xD;&#xA;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 m.[lastname],&#xD;&#xA;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 m.[firstname]&#xD;&#xA;FROM [dbo].[charge] AS c&#xD;&#xA;INNER JOIN [dbo].[member] AS m ON&#xD;&#xA;\u00a0\u00a0\u00a0 c.[member_no] + ‘ ‘\u00a0 =\u00a0 m.[member_no] &#xD;&#xA;OPTION (RECOMPILE)” StatementType=”SELECT” QueryHash=”0xF698FF11488A3A3D” QueryPlanHash=”0x41F421A8F51D61CE” RetrievedFromCache=”false”><\/p><\/blockquote>\n
And I saw that value of 70<\/strong> for compatibility levels 110 (SQL Server 2012), 100 (SQL Server 2008), and 90 (SQL Server 2005).\u00a0 Perhaps this indicates that the cardinality estimation model being used pre-SQL Server 2014 CTP2 had its last major <\/em>change back in the (70) SQL Server 7.0 days. Just a guess though.<\/p>\n
More to come\u2026<\/p>\n","protected":false},"excerpt":{"rendered":"
This post is a continuation of the SQL Server 2014 Cardinality Estimator enhancements exploration series: A first look at the query_optimizer_estimate_cardinality XE event \u201cCSelCalcCombineFilters_ExponentialBackoff\u201d Calculator \u201cCSelCalcCombineFilters_ExponentialBackoff\u201d Calculator\u2013 Part II The CSelCalcAscendingKeyFilter Calculator In this post I just wanted to step back and discuss how to enable and identify the Cardinality Estimator version, at least as […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[42,28],"tags":[],"class_list":["post-1111","post","type-post","status-publish","format-standard","hentry","category-cardinality-estimation","category-performance"],"yoast_head":"\n