This post, like the previous nine posts on this subject, uses SQL Server 2014 CTP2 as a reference point.\u00a0 There may be changes by SQL Server 2014 RTM, and if so, I\u2019ll write a post about applicable changes.<\/em><\/p>\nNow in terms of troubleshooting the new Cardinality Estimator, what I\u2019m specifically referring to is the introduction of cardinality estimate skews that negatively impact query performance compared to the pre-SQL Server 2014 cardinality estimator functionality.\u00a0\u00a0 Ideally performance regressions should be rare, but when they happen, what are our troubleshooting options?<\/p>\n
To frame this discussion, let\u2019s first discuss what may or may not warrant action\u2026<\/p>\n
No Action (Necessarily) Needed<\/strong><\/p>\n\n- The estimates are identical to old CE functionality and the query plan is unchanged<\/li>\n
- The estimates are skewed compared to the old CE functionality, but the query plan \u201cshape\u201d is identical (and you see no side-effects from the skews, such as sort or hash spills and query runtime degradation)<\/li>\n
- The estimates are skewed compared to the old CE functionality, the plan is different, but performance is equal or improved \u2013 or even more stable<\/li>\n
- The estimates are skewed compared to the old CE functionality, the plan is different, and performance is somewhat impacted but not enough to justify changes (totally depends on your SLAs & workload performance requirements of course)<\/li>\n<\/ul>\n
Action Potentially Needed<\/strong><\/p>\n\n- The estimates are skewed, the plan shape is unchanged, but the estimates lead to performance issues such as spills (due to under-estimates) or concurrency issues (due to over-estimates) for memory-intensive operators<\/li>\n
- The estimates are skewed, the plan is changed, and the plan quality leads to performance degradation (a variety of query optimizer plan choices which may lead to issues)<\/li>\n<\/ul>\n
So what troubleshooting methods and options are available to us?<\/p>\n
Troubleshooting Options<\/strong><\/p>\n\n- Do nothing (yeah, I know, but this can be a decision you ultimately make, looking at risk\/effort\/reward)<\/li>\n
- Revert to the pre-SQL Server 2014 CE\u00a0version (for example, via database compatibility level change)<\/li>\n
- Apply legacy troubleshooting methods, which may fix other issues directly or indirectly related to the skew and thus help close the gap (framing these legacy methods\u00a0as questions below)\n
\n- Are the statistics old and need updating?<\/li>\n
- Should the statistics sampling be changed?<\/li>\n
- Are multi-column stats needed to help establish a correlation where one currently isn\u2019t seen by the query optimizer?<\/li>\n
- Parameter sniffing troubleshooting needed? (a much larger topic, but indulge me on including this as a method)<\/li>\n
- Is your table variable usage contributing to the skew?<\/li>\n
- Is your multi-statement table-valued function or scalar user-defined function contributing to the skew?<\/li>\n
- Any data-type conversions occurring for predicates (join or filter)?<\/li>\n
- Are you comparing column values from the same table?<\/li>\n
- Is your column reference being buried by a function or embedded in a complex expression?<\/li>\n
- Are hints being used and if so, is their usage appropriate?<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
The new CE story will unfold as customers start upgrading to SQL Server 2014 and I’ll be curious to see which regression patterns are most common.<\/p>\n
Open Questions<\/strong><\/p>\n\n- Regarding the new query_optimizer_estimate_cardinality XE event\u2026 Will it be a practical source of information for most SQL Server users in cardinality estimator skew regression scenarios \u2013 or will it be something reserved for edge-cases and advanced Microsoft\u00a0customer support\u00a0scenarios?\u00a0 I suspect this XE event will have limited applicability, but I\u2019m reserving judgment for now.<\/li>\n
- Will SQL Server 2014 RTM introduce finer-grained methods for reverting to the pre-2014 cardinality estimator?<\/li>\n
- How will the new CE behave with newer functionality?\u00a0 For example, Hekaton and clustered columnstore indexes.<\/li>\n
- Will this be it for CE changes for the next few versions?\u00a0 There is plenty left on the CE-improvement wish list, so I hope not.<\/li>\n<\/ul>\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 Cardinality Estimation Model Version Comparing Root-Level Skews in the new Cardinality Estimator Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews […]<\/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-1150","post","type-post","status-publish","format-standard","hentry","category-cardinality-estimation","category-performance"],"yoast_head":"\n
Troubleshooting 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\/troubleshooting-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=\"Troubleshooting 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 Comparing Root-Level Skews in the new Cardinality Estimator Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/troubleshooting-the-new-cardinality-estimator\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2013-12-29T23:42:52+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-12-30T03:11:37+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=\"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\/troubleshooting-the-new-cardinality-estimator\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/troubleshooting-the-new-cardinality-estimator\/\",\"name\":\"Troubleshooting the new Cardinality Estimator - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2013-12-29T23:42:52+00:00\",\"dateModified\":\"2013-12-30T03:11:37+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/troubleshooting-the-new-cardinality-estimator\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/troubleshooting-the-new-cardinality-estimator\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/troubleshooting-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\":\"Troubleshooting 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":"Troubleshooting 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\/troubleshooting-the-new-cardinality-estimator\/","og_locale":"en_US","og_type":"article","og_title":"Troubleshooting 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 Comparing Root-Level Skews in the new Cardinality Estimator Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/troubleshooting-the-new-cardinality-estimator\/","og_site_name":"Joe Sack","article_published_time":"2013-12-29T23:42:52+00:00","article_modified_time":"2013-12-30T03:11:37+00:00","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\/troubleshooting-the-new-cardinality-estimator\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/troubleshooting-the-new-cardinality-estimator\/","name":"Troubleshooting the new Cardinality Estimator - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2013-12-29T23:42:52+00:00","dateModified":"2013-12-30T03:11:37+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/troubleshooting-the-new-cardinality-estimator\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/troubleshooting-the-new-cardinality-estimator\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/troubleshooting-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":"Troubleshooting 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\/1150","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=1150"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/1150\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=1150"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=1150"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=1150"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}