{"id":1115,"date":"2013-11-26T17:21:10","date_gmt":"2013-11-27T01:21:10","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/joe\/?p=1115"},"modified":"2013-12-29T19:13:26","modified_gmt":"2013-12-30T03:13:26","slug":"comparing-root-level-skews-in-the-new-cardinality-estimator","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/comparing-root-level-skews-in-the-new-cardinality-estimator\/","title":{"rendered":"Comparing Root-Level Skews in the new Cardinality Estimator"},"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\/\" target=\"_blank\">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\/\" target=\"_blank\">\u201cCSelCalcCombineFilters_ExponentialBackoff\u201d Calculator<\/a><\/li>\n<li><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/cselcalccombinefilters_exponentialbackoff-calculator-part-ii\/\" target=\"_blank\">\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<li><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/cardinality-estimation-model-version\/\" target=\"_blank\">Cardinality Estimation Model Version<\/a><\/li>\n<\/ul>\n<p>In the previous post I showed you how to start using the new SQL Server 2014 CE model, but what different does it actually make?<\/p>\n<p>Of course your own mileage may vary (and you should expect it to), but I thought I would kick the tires a bit by comparing root-level cardinality estimate skews for a specific workload against the <a href=\"http:\/\/msftdbprodsamples.codeplex.com\/releases\/view\/59211\" target=\"_blank\" class=\"broken_link\">AdventureWorks2008R2 database<\/a>.\u00a0 I used Jonathan\u2019s \u201cAdventureWorks BOL Workload.sql\u201d script that he put together for use in the context of <a href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/the-adventureworks2008r2-books-online-random-workload-generator\/\" target=\"_blank\">The AdventureWorks2008R2 Books Online Random Workload Generator<\/a> (I made a few minor adjustments \u2013 for example, removing the queries that referenced the deprecated COMPUTE).<\/p>\n<ol>\n<li>I first cleared the cache of the (test) SQL Server 2014 CTP2 instance and then ran the script against the AdventureWorks2012 database using a pre-SQL Server 2014 database compatibility level (100).<\/li>\n<li>I then collected the root-level cardinality skew information and then re-ran the test using the SQL Server 2014 database compatibility level (120) and again collected the root-level cardinality skew information for that test as well.<\/li>\n<\/ol>\n<p>The query I used for extracting estimated vs. actual last rows was as follows (for both tests):<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSELECT\u00a0 s.&#x5B;query_hash],\r\np.&#x5B;query_plan].value('(\/\/@EstimateRows)&#x5B;1]', 'varchar(128)') AS &#x5B;estimated_rows],\r\ns.&#x5B;last_rows]\r\nFROM\u00a0\u00a0\u00a0 sys.&#x5B;dm_exec_query_stats] AS &#x5B;s]\r\nCROSS APPLY sys.&#x5B;dm_exec_sql_text](sql_handle) AS &#x5B;t]\r\nCROSS APPLY sys.&#x5B;dm_exec_query_plan](plan_handle) AS &#x5B;p]\r\nWHERE\u00a0\u00a0 DATEDIFF(mi, s.&#x5B;last_execution_time], GETDATE()) &amp;lt; 1\r\nGO\r\n<\/pre>\n<p>Of course, root level cardinality skews don\u2019t really give us the <em>full<\/em> picture (see my post <a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/detecting-cardinality-estimate-issues-with-sys-dm_exec_query_stats\/\" target=\"_blank\">Detecting Cardinality Estimate Issues with sys.dm_exec_query_stats<\/a>), especially for significant skews that are in the leaf-level or intermediate levels of the plan that work themselves out by the time they reach the root. But this informal test was a way for me to get a general feel for changes across several statements in a workload.<\/p>\n<p>Now for these tests, I had query_hash values that would appear for the old-CE and not the new-CE and for the new-CE and not the old-CE.\u00a0 Rather than run down that bunny trail (background requests, for example), I just focused for the time being on those query_hash values that joined consistently across each test run.<\/p>\n<p>Here is what I saw \u2013 adding a bit of color to the noteworthy changes of old-CE vs. new-CE.<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/11\/image17.png\"><img fetchpriority=\"high\" decoding=\"async\" style=\"background-image: none; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border: 0px;\" title=\"image\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/11\/image_thumb17.png\" width=\"1139\" height=\"728\" border=\"0\" \/><\/a><\/p>\n<p>Now with regards to one round of informal testing I saw:<\/p>\n<ul>\n<li>9 root-level estimates improve (some slightly, some more significant)<\/li>\n<li>3 root-level estimates degrade slightly<\/li>\n<li>2 root-level estimates skew \u201cmore than slightly\u201d<\/li>\n<li>13 plans remained the same<\/li>\n<\/ul>\n<p>How consistent were the results across tests?\u00a0 It was repeatable.\u00a0 I did three test rounds where the skew was identical across workloads for the new CE and and for the old \u2013 as I would expect (and ruling out any rogue background queries jumping in during my test or new query hash values \u2013 there were 28 query_hash values I could keep track of).<\/p>\n<p>Now again, this is just root-level skew.\u00a0 Quite a bit can be buried in the leaf and intermediate levels of the plan, so I\u2019ll be exploring changes there as well, but I thought this would be an interesting first cut at the overall workload estimate changes.<\/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 Cardinality Estimation Model Version In the previous post I showed you how to start using the new SQL Server 2014 CE model, but [&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-1115","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>Comparing Root-Level Skews in the new Cardinality Estimator - 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\/comparing-root-level-skews-in-the-new-cardinality-estimator\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Comparing Root-Level Skews in the new Cardinality Estimator - 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 Cardinality Estimation Model Version In the previous post I showed you how to start using the new SQL Server 2014 CE model, but [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/comparing-root-level-skews-in-the-new-cardinality-estimator\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2013-11-27T01:21:10+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-12-30T03:13:26+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/11\/image_thumb17.png\" \/>\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=\"3 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\/comparing-root-level-skews-in-the-new-cardinality-estimator\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/comparing-root-level-skews-in-the-new-cardinality-estimator\/\",\"name\":\"Comparing Root-Level Skews in the new Cardinality Estimator - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2013-11-27T01:21:10+00:00\",\"dateModified\":\"2013-12-30T03:13:26+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/comparing-root-level-skews-in-the-new-cardinality-estimator\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/comparing-root-level-skews-in-the-new-cardinality-estimator\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/comparing-root-level-skews-in-the-new-cardinality-estimator\/#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\":\"Comparing Root-Level Skews in the new Cardinality Estimator\"}]},{\"@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":"Comparing Root-Level Skews in the new Cardinality Estimator - 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\/comparing-root-level-skews-in-the-new-cardinality-estimator\/","og_locale":"en_US","og_type":"article","og_title":"Comparing Root-Level Skews in the new Cardinality Estimator - 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 Cardinality Estimation Model Version In the previous post I showed you how to start using the new SQL Server 2014 CE model, but [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/comparing-root-level-skews-in-the-new-cardinality-estimator\/","og_site_name":"Joe Sack","article_published_time":"2013-11-27T01:21:10+00:00","article_modified_time":"2013-12-30T03:13:26+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/11\/image_thumb17.png"}],"author":"Joseph Sack","twitter_misc":{"Written by":"Joseph Sack","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/comparing-root-level-skews-in-the-new-cardinality-estimator\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/comparing-root-level-skews-in-the-new-cardinality-estimator\/","name":"Comparing Root-Level Skews in the new Cardinality Estimator - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2013-11-27T01:21:10+00:00","dateModified":"2013-12-30T03:13:26+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/comparing-root-level-skews-in-the-new-cardinality-estimator\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/comparing-root-level-skews-in-the-new-cardinality-estimator\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/comparing-root-level-skews-in-the-new-cardinality-estimator\/#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":"Comparing Root-Level Skews in the new Cardinality Estimator"}]},{"@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\/1115","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=1115"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/1115\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=1115"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=1115"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=1115"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}