{"id":1149,"date":"2013-12-20T08:30:38","date_gmt":"2013-12-20T16:30:38","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/joe\/?p=1149"},"modified":"2013-12-29T19:14:19","modified_gmt":"2013-12-30T03:14:19","slug":"non-join-cross-table-predicate-correlation-changes","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/non-join-cross-table-predicate-correlation-changes\/","title":{"rendered":"Non-Join Cross-Table Predicate Correlation Changes"},"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<li><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/comparing-root-level-skews-in-the-new-cardinality-estimator\/\" target=\"_blank\">Comparing Root-Level Skews in the new Cardinality Estimator<\/a><\/li>\n<li><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews\/\" target=\"_blank\">Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews<\/a><\/li>\n<li><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/more-on-exponential-backoff\/\" target=\"_blank\">More on Exponential Backoff<\/a><\/li>\n<\/ul>\n<p>In previous posts for this series, I discussed how the assumption of independence with regards to multiple predicates against the same table, in absence of multi-column stats, is blunted a bit with the new Cardinality Estimator for SQL Server 2014.\u00a0 So your estimates may increase for this scenario.<\/p>\n<p>On the flip-side, when it comes to joins between two tables, you may see a <em>reduction <\/em>in join estimate values for scenarios where there are non-join filter predicates on the tables being joined.<\/p>\n<p>Take the following example, using pre-SQL Server 2014 CE behavior:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nUSE &#x5B;master]\r\nGO\r\n\r\nALTER DATABASE &#x5B;Credit] SET COMPATIBILITY_LEVEL = 110\r\nGO\r\n\r\nUSE &#x5B;Credit];\r\nGO\r\n\r\nSELECT\u00a0 &#x5B;m].&#x5B;member_no] ,\r\n&#x5B;m].&#x5B;lastname] ,\r\n&#x5B;p].&#x5B;payment_no] ,\r\n&#x5B;p].&#x5B;payment_dt] ,\r\n&#x5B;p].&#x5B;payment_amt]\r\nFROM\u00a0\u00a0\u00a0 dbo.&#x5B;member] AS &#x5B;m]\r\nINNER JOIN dbo.&#x5B;payment] AS &#x5B;p]\r\nON\u00a0\u00a0\u00a0\u00a0\u00a0 &#x5B;m].&#x5B;member_no] = p.&#x5B;member_no]\r\nWHERE\u00a0\u00a0 &#x5B;m].&#x5B;region_no] = 2\r\nAND &#x5B;p].&#x5B;payment_dt] = '1999-09-02 00:00:00.000'\r\nOPTION\u00a0 ( RECOMPILE );\r\nGO\r\n<\/pre>\n<p>The SQL Sentry Plan Explorer plan tree view is as follows:<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/12\/image.png\"><img fetchpriority=\"high\" decoding=\"async\" style=\"display: inline; border: 0px;\" title=\"image\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/12\/image_thumb.png\" width=\"721\" height=\"109\" border=\"0\" \/><\/a><\/p>\n<p>We see that the estimates are spot-on for the Clustered Index Scan and Table Scan, but we have an over-estimate for the Hash Match operation (1,767 estimated vs. 1,003 actual).<\/p>\n<p>Now if I set the database compatibility level to 120 and re-execute the query, here is what we see instead:<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/12\/image1.png\"><img decoding=\"async\" style=\"display: inline; border: 0px;\" title=\"image\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/12\/image_thumb1.png\" width=\"729\" height=\"107\" border=\"0\" \/><\/a><\/p>\n<p>We still have identical estimate vs. actual values for the Clustered Index Scan and Table Scan, and now our over-estimate for the Hash Match is less pronounced (1,140 rows estimated instead of the 1,767 rows previously estimated).<\/p>\n<p>For the pre-SQL Server 2014 cardinality estimation process, the assumption is that the non-join predicates for the two tables are somehow correlated (in our example, region_no = 2 and payment_dt = &#8216;1999-09-02 00:00:00.000&#8217;).\u00a0 This is called \u201cSimple Containment\u201d. For the new Cardinality Estimator, these non-join predicates are assumed to be independent (called \u201cBase Containment\u201d), and so this can translate into a reduced row estimate for the join.<\/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 Comparing Root-Level Skews in the new Cardinality Estimator Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews [&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-1149","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>Non-Join Cross-Table Predicate Correlation Changes - 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\/non-join-cross-table-predicate-correlation-changes\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Non-Join Cross-Table Predicate Correlation Changes - 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 [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/non-join-cross-table-predicate-correlation-changes\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2013-12-20T16:30:38+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-12-30T03:14:19+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/12\/image_thumb.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=\"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\/non-join-cross-table-predicate-correlation-changes\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/non-join-cross-table-predicate-correlation-changes\/\",\"name\":\"Non-Join Cross-Table Predicate Correlation Changes - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2013-12-20T16:30:38+00:00\",\"dateModified\":\"2013-12-30T03:14:19+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/non-join-cross-table-predicate-correlation-changes\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/non-join-cross-table-predicate-correlation-changes\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/non-join-cross-table-predicate-correlation-changes\/#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\":\"Non-Join Cross-Table Predicate Correlation Changes\"}]},{\"@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":"Non-Join Cross-Table Predicate Correlation Changes - 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\/non-join-cross-table-predicate-correlation-changes\/","og_locale":"en_US","og_type":"article","og_title":"Non-Join Cross-Table Predicate Correlation Changes - 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 [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/non-join-cross-table-predicate-correlation-changes\/","og_site_name":"Joe Sack","article_published_time":"2013-12-20T16:30:38+00:00","article_modified_time":"2013-12-30T03:14:19+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/12\/image_thumb.png"}],"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\/non-join-cross-table-predicate-correlation-changes\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/non-join-cross-table-predicate-correlation-changes\/","name":"Non-Join Cross-Table Predicate Correlation Changes - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2013-12-20T16:30:38+00:00","dateModified":"2013-12-30T03:14:19+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/non-join-cross-table-predicate-correlation-changes\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/non-join-cross-table-predicate-correlation-changes\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/non-join-cross-table-predicate-correlation-changes\/#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":"Non-Join Cross-Table Predicate Correlation Changes"}]},{"@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\/1149","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=1149"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/1149\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=1149"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=1149"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=1149"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}