Warning: Constant WP_TEMP_DIR already defined in /var/www/html/blogs/joe/wp-config.php on line 93

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902
{"id":1097,"date":"2013-11-17T08:48:55","date_gmt":"2013-11-17T16:48:55","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/joe\/?p=1097"},"modified":"2014-02-14T12:45:38","modified_gmt":"2014-02-14T20:45:38","slug":"cselcalccombinefilters_exponentialbackoff-calculator-part-ii","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/cselcalccombinefilters_exponentialbackoff-calculator-part-ii\/","title":{"rendered":"“CSelCalcCombineFilters_ExponentialBackoff” Calculator– Part II"},"content":{"rendered":"

At the end of my last post, \u201cCSelCalcCombineFilters_ExponentialBackoff\u201d Calculator<\/a> I gave the following homework:<\/p>\n

Homework assignment \u2013 add another member column to the mix that has a 0.100 selectivity (for example \u2013 county) and compare the estimates using a pre-2014 and 2014 CTP2 compatibility mode.<\/p><\/blockquote>\n

Here is the answer-key.\u00a0 I\u2019ll start off with adding a new column called \u201ccounty\u201d to the member table (and yes \u2013 county gets a little involved for some cities like NYC, but let\u2019s just go with a uniform county association to city\/state for now):<\/p>\n

\r\nALTER TABLE [dbo].[member]\r\nADD [county] varchar(15) NULL;\r\n<\/pre>\n
And then next I\u2019ll revise my previous city\/state update script to include county:<\/p>\n
\r\nUPDATE\u00a0 [dbo].[member]\r\nSET\u00a0\u00a0\u00a0\u00a0 [city] = 'Minneapolis',\r\n[state_prov] = 'MN',\r\n[county] = 'Hennepin'\r\nWHERE\u00a0\u00a0 [member_no] % 10 = 0;\r\n\r\nUPDATE\u00a0 [dbo].[member]\r\nSET\u00a0\u00a0\u00a0\u00a0 [city] = 'New York',\r\n[state_prov] = 'NY',\r\n[county]='Manhattan'\r\nWHERE\u00a0\u00a0 [member_no] % 10 = 1;\r\n\r\nUPDATE\u00a0 [dbo].[member]\r\nSET\u00a0\u00a0\u00a0\u00a0 [city] = 'Chicago',\r\n[state_prov] = 'IL',\r\n[county]='Cook'\r\nWHERE\u00a0\u00a0 [member_no] % 10 = 2;\r\n\r\nUPDATE\u00a0 [dbo].[member]\r\nSET\u00a0\u00a0\u00a0\u00a0 [city] = 'Houston',\r\n[state_prov] = 'TX',\r\n[county]='Harrisburg'\r\nWHERE\u00a0\u00a0 [member_no] % 10 = 3;\r\n\r\nUPDATE\u00a0 [dbo].[member]\r\nSET\u00a0\u00a0\u00a0\u00a0 [city] = 'Philadelphia',\r\n[state_prov] = 'PA',\r\n[county]='Philadelphia'\r\nWHERE\u00a0\u00a0 [member_no] % 10 = 4;\r\n\r\nUPDATE\u00a0 [dbo].[member]\r\nSET\u00a0\u00a0\u00a0\u00a0 [city] = 'Phoenix',\r\n[state_prov] = 'AZ',\r\n[county]=''\r\nWHERE\u00a0\u00a0 [member_no] % 10 = 5;\r\n\r\nUPDATE\u00a0 [dbo].[member]\r\nSET\u00a0\u00a0\u00a0\u00a0 [city] = 'San Antonio',\r\n[state_prov] = 'TX',\r\n[county]='Bexar'\r\nWHERE\u00a0\u00a0 [member_no] % 10 = 6;\r\n\r\nUPDATE\u00a0 [dbo].[member]\r\nSET\u00a0\u00a0\u00a0\u00a0 [city] = 'San Diego',\r\n[state_prov] = 'CA',\r\n[county]='San Diego'\r\nWHERE\u00a0\u00a0 [member_no] % 10 = 7;\r\n\r\nUPDATE\u00a0 [dbo].[member]\r\nSET\u00a0\u00a0\u00a0\u00a0 [city] = 'Dallas',\r\n[state_prov] = 'TX',\r\n[county]='Dallas'\r\nWHERE\u00a0\u00a0 [member_no] % 10 = 8;\r\nGO\r\n<\/pre>\n
Now, what row estimate will we get for the member table pre-2014 if we execute the following query?<\/p>\n
\r\nSELECT\u00a0 m.[lastname],\r\nm.[firstname],\r\nSUM(c.[charge_amt]) AS [Total_Charge_amt]\r\nFROM\u00a0\u00a0\u00a0 [dbo].[member] AS [m]\r\nINNER JOIN [dbo].[charge] AS [c] ON\r\nm.[member_no] = c.[member_no]\r\nWHERE\u00a0\u00a0 [city] = 'Minneapolis' AND\r\n[state_prov] = 'MN' AND\r\n[county] = 'Hennepin'\r\nGROUP BY m.[lastname],\r\nm.[firstname]\r\nOPTION (RECOMPILE);\r\nGO\r\n<\/pre>\n
We see the following estimated rows versus actual (keeping in mind that there are no multi-column stats to help us out):<\/p>\n
\"image\"<\/a><\/p>\n
Estimated number of rows is 10, but actual is 1000.<\/p>\n
Given that we<\/em> know the three predicates are correlated, but SQL Server thinks they are independent (no multi-column stats), we can see the calculation is as follows:<\/p>\n
\"image\"<\/a><\/p>\n
Now if I switch to 2014 compatibility mode, I see the following estimated number of rows:<\/p>\n
\"image\"<\/a><\/p>\n
And I see the following query_optimizer_estimate_cardinality event output we see the following for the 2014 version of the plan:<\/p>\n
<CalculatorList>
\n<FilterCalculator CalculatorName=”CSelCalcCombineFilters_ExponentialBackoff<\/span>” Conjunction=”true”>
\n<SubCalculator>
\n<FilterCalculator CalculatorName=”CSelCalcColumnInInterval” Selectivity=”0.100″ TableName=”[m]” ColumnName=”state_prov” StatId=”5″ \/>
\n<FilterCalculator CalculatorName=”CSelCalcColumnInInterval” Selectivity=”0.100″ TableName=”[m]” ColumnName=”county” StatId=”8″ \/>
\n<FilterCalculator CalculatorName=”CSelCalcColumnInInterval” Selectivity=”0.100″ TableName=”[m]” ColumnName=”city” StatId=”4″ \/>
\n<\/SubCalculator>
\n<\/FilterCalculator>
\n<\/CalculatorList><\/p>\n
So updating the formulas, how is the 177.828 derived?<\/p>\n
\"image\"<\/a><\/p>\n
So we see in 2014 CTP2 that instead of multiplying the selectivity of each predicate (again, assuming independence), the Exponential Backoff calculator is blunting the impact 10000*0.1*POWER(0.1,1\/2<\/span><\/strong>)*POWER(0.1,1\/4<\/span><\/strong>).<\/p>\n
Still a skew \u2013 but less so.\u00a0 177.828 estimated vs. 10.\u00a0 And think it doesn\u2019t really matter?<\/p>\n
For this example on my test system, pre-2014 the Clustered Index Scan was serial and fed to a Distribute Streams exchange operator, and with the new estimate, the operator is parallel.<\/p>\n
Before:<\/p>\n
\"image\"<\/a><\/p>\n
After:<\/p>\n
\"image\"<\/a><\/p>\n
Plenty more to explore in this area\u2026<\/p>\n","protected":false},"excerpt":{"rendered":"
At the end of my last post, \u201cCSelCalcCombineFilters_ExponentialBackoff\u201d Calculator I gave the following homework: Homework assignment \u2013 add another member column to the mix that has a 0.100 selectivity (for example \u2013 county) and compare the estimates using a pre-2014 and 2014 CTP2 compatibility mode. Here is the answer-key.\u00a0 I\u2019ll start off with adding a […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[42],"tags":[],"class_list":["post-1097","post","type-post","status-publish","format-standard","hentry","category-cardinality-estimation"],"yoast_head":"\n“CSelCalcCombineFilters_ExponentialBackoff” Calculator– Part II - 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\/cselcalccombinefilters_exponentialbackoff-calculator-part-ii\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"“CSelCalcCombineFilters_ExponentialBackoff” Calculator– Part II - Joe Sack\" \/>\n<meta property=\"og:description\" content=\"At the end of my last post, \u201cCSelCalcCombineFilters_ExponentialBackoff\u201d Calculator I gave the following homework: Homework assignment \u2013 add another member column to the mix that has a 0.100 selectivity (for example \u2013 county) and compare the estimates using a pre-2014 and 2014 CTP2 compatibility mode. Here is the answer-key.\u00a0 I\u2019ll start off with adding a […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/cselcalccombinefilters_exponentialbackoff-calculator-part-ii\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2013-11-17T16:48:55+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-02-14T20:45:38+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/11\/image_thumb5.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\/cselcalccombinefilters_exponentialbackoff-calculator-part-ii\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/cselcalccombinefilters_exponentialbackoff-calculator-part-ii\/\",\"name\":\"“CSelCalcCombineFilters_ExponentialBackoff” Calculator– Part II - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2013-11-17T16:48:55+00:00\",\"dateModified\":\"2014-02-14T20:45:38+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/cselcalccombinefilters_exponentialbackoff-calculator-part-ii\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/cselcalccombinefilters_exponentialbackoff-calculator-part-ii\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/cselcalccombinefilters_exponentialbackoff-calculator-part-ii\/#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\":\"“CSelCalcCombineFilters_ExponentialBackoff” Calculator– Part II\"}]},{\"@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":"“CSelCalcCombineFilters_ExponentialBackoff” Calculator– Part II - 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\/cselcalccombinefilters_exponentialbackoff-calculator-part-ii\/","og_locale":"en_US","og_type":"article","og_title":"“CSelCalcCombineFilters_ExponentialBackoff” Calculator– Part II - Joe Sack","og_description":"At the end of my last post, \u201cCSelCalcCombineFilters_ExponentialBackoff\u201d Calculator I gave the following homework: Homework assignment \u2013 add another member column to the mix that has a 0.100 selectivity (for example \u2013 county) and compare the estimates using a pre-2014 and 2014 CTP2 compatibility mode. Here is the answer-key.\u00a0 I\u2019ll start off with adding a […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/cselcalccombinefilters_exponentialbackoff-calculator-part-ii\/","og_site_name":"Joe Sack","article_published_time":"2013-11-17T16:48:55+00:00","article_modified_time":"2014-02-14T20:45:38+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/11\/image_thumb5.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\/cselcalccombinefilters_exponentialbackoff-calculator-part-ii\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/cselcalccombinefilters_exponentialbackoff-calculator-part-ii\/","name":"“CSelCalcCombineFilters_ExponentialBackoff” Calculator– Part II - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2013-11-17T16:48:55+00:00","dateModified":"2014-02-14T20:45:38+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/cselcalccombinefilters_exponentialbackoff-calculator-part-ii\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/cselcalccombinefilters_exponentialbackoff-calculator-part-ii\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/cselcalccombinefilters_exponentialbackoff-calculator-part-ii\/#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":"“CSelCalcCombineFilters_ExponentialBackoff” Calculator– Part II"}]},{"@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\/1097","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=1097"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/1097\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=1097"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=1097"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=1097"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}