{"id":1111,"date":"2013-11-25T19:53:15","date_gmt":"2013-11-26T03:53:15","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/joe\/?p=1111"},"modified":"2013-12-29T19:13:10","modified_gmt":"2013-12-30T03:13:10","slug":"cardinality-estimation-model-version","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/cardinality-estimation-model-version\/","title":{"rendered":"Cardinality Estimation Model Version"},"content":{"rendered":"<p>This post is a continuation of the SQL Server 2014 Cardinality Estimator enhancements exploration series:<\/p>\n<ul>\n<li><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/a-first-look-at-the-query_optimizer_estimate_cardinality-xe-event\/\">A first look at the query_optimizer_estimate_cardinality XE event<\/a><\/li>\n<li><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/cselcalccombinefilters_exponentialbackoff-calculator\/\">\u201cCSelCalcCombineFilters_ExponentialBackoff\u201d Calculator<\/a><\/li>\n<li><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/cselcalccombinefilters_exponentialbackoff-calculator-part-ii\/\">\u201cCSelCalcCombineFilters_ExponentialBackoff\u201d Calculator\u2013 Part II<\/a><\/li>\n<li><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/the-cselcalcascendingkeyfilter-calculator\/\" target=\"_blank\">The CSelCalcAscendingKeyFilter Calculator<\/a><\/li>\n<\/ul>\n<p>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<p>As you saw for earlier posts, I see the new CE behavior by changing a database\u2019s compatibility level as follows:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nUSE &#x5B;master]\r\nGO\r\nALTER DATABASE &#x5B;Credit] SET COMPATIBILITY_LEVEL = 120;\r\nGO\r\n\r\n<\/pre>\n<p>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<blockquote><p>&lt;StmtSimple StatementCompId=&#8221;1&#8243; StatementEstRows=&#8221;1600000&#8243; StatementId=&#8221;1&#8243; StatementOptmLevel=&#8221;FULL&#8221; <span style=\"background-color: #ffff00;\">CardinalityEstimationModelVersion=&#8221;120&#8243;<\/span> StatementSubTreeCost=&#8221;21.0963&#8243; StatementText=&#8221;SELECT\u00a0\u00a0\u00a0 c.[charge_no],&amp;#xD;&amp;#xA;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 m.[lastname],&amp;#xD;&amp;#xA;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 m.[firstname]&amp;#xD;&amp;#xA;FROM [dbo].[charge] AS c&amp;#xD;&amp;#xA;INNER JOIN [dbo].[member] AS m ON&amp;#xD;&amp;#xA;\u00a0\u00a0\u00a0 c.[member_no] + &#8216; &#8216;\u00a0 =\u00a0 m.[member_no] &amp;#xD;&amp;#xA;OPTION (RECOMPILE)&#8221; StatementType=&#8221;SELECT&#8221; QueryHash=&#8221;0xF698FF11488A3A3D&#8221; QueryPlanHash=&#8221;0x41F421A8F51D61CE&#8221; RetrievedFromCache=&#8221;false&#8221;&gt;<\/p><\/blockquote>\n<p>Note that if my database compatibility level was for an earlier level, I see the following:<\/p>\n<blockquote><p>&lt;StmtSimple StatementCompId=&#8221;1&#8243; StatementEstRows=&#8221;1597640&#8243; StatementId=&#8221;1&#8243; StatementOptmLevel=&#8221;FULL&#8221; <span style=\"background-color: #ffff00;\">CardinalityEstimationModelVersion=&#8221;70&#8243;<\/span> StatementSubTreeCost=&#8221;21.0895&#8243; StatementText=&#8221;SELECT\u00a0\u00a0\u00a0 c.[charge_no],&amp;#xD;&amp;#xA;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 m.[lastname],&amp;#xD;&amp;#xA;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 m.[firstname]&amp;#xD;&amp;#xA;FROM [dbo].[charge] AS c&amp;#xD;&amp;#xA;INNER JOIN [dbo].[member] AS m ON&amp;#xD;&amp;#xA;\u00a0\u00a0\u00a0 c.[member_no] + &#8216; &#8216;\u00a0 =\u00a0 m.[member_no] &amp;#xD;&amp;#xA;OPTION (RECOMPILE)&#8221; StatementType=&#8221;SELECT&#8221; QueryHash=&#8221;0xF698FF11488A3A3D&#8221; QueryPlanHash=&#8221;0x41F421A8F51D61CE&#8221; RetrievedFromCache=&#8221;false&#8221;&gt;<\/p><\/blockquote>\n<p>And I saw that value of <strong>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 <em>major <\/em>change back in the (70) SQL Server 7.0 days. Just a guess though.<\/p>\n<p>More to come\u2026<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/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":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Cardinality Estimation Model Version - Joe Sack<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/cardinality-estimation-model-version\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Cardinality Estimation Model Version - Joe Sack\" \/>\n<meta property=\"og:description\" content=\"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 [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/cardinality-estimation-model-version\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2013-11-26T03:53:15+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-12-30T03:13:10+00:00\" \/>\n<meta name=\"author\" content=\"Joseph Sack\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Joseph Sack\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/cardinality-estimation-model-version\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/cardinality-estimation-model-version\/\",\"name\":\"Cardinality Estimation Model Version - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2013-11-26T03:53:15+00:00\",\"dateModified\":\"2013-12-30T03:13:10+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/cardinality-estimation-model-version\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/cardinality-estimation-model-version\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/cardinality-estimation-model-version\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Cardinality Estimation\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/cardinality-estimation\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Cardinality Estimation Model Version\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\",\"name\":\"Joe Sack\",\"description\":\"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\",\"name\":\"Joseph Sack\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"caption\":\"Joseph Sack\"},\"description\":\"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.\",\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/joe\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Cardinality Estimation Model Version - Joe Sack","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/joe\/cardinality-estimation-model-version\/","og_locale":"en_US","og_type":"article","og_title":"Cardinality Estimation Model Version - Joe Sack","og_description":"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 [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/cardinality-estimation-model-version\/","og_site_name":"Joe Sack","article_published_time":"2013-11-26T03:53:15+00:00","article_modified_time":"2013-12-30T03:13:10+00:00","author":"Joseph Sack","twitter_misc":{"Written by":"Joseph Sack","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/cardinality-estimation-model-version\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/cardinality-estimation-model-version\/","name":"Cardinality Estimation Model Version - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2013-11-26T03:53:15+00:00","dateModified":"2013-12-30T03:13:10+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/cardinality-estimation-model-version\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/cardinality-estimation-model-version\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/cardinality-estimation-model-version\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/"},{"@type":"ListItem","position":2,"name":"Cardinality Estimation","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/cardinality-estimation\/"},{"@type":"ListItem","position":3,"name":"Cardinality Estimation Model Version"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/","name":"Joe Sack","description":"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648","name":"Joseph Sack","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","caption":"Joseph Sack"},"description":"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.","sameAs":["http:\/\/3.209.169.194\/blogs\/joe","https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack"],"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/1111","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/comments?post=1111"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/1111\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=1111"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=1111"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=1111"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}