Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews - 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\/using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews - 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 In the previous post, I looked at root-level cardinality estimates vs. […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2013-11-28T16:22:13+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-12-30T03:13:42+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/11\/image_thumb18.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=\"4 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\/using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews\/\",\"name\":\"Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2013-11-28T16:22:13+00:00\",\"dateModified\":\"2013-12-30T03:13:42+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews\/#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\":\"Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews\"}]},{\"@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_head_json":{"title":"Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews - 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\/using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews\/","og_locale":"en_US","og_type":"article","og_title":"Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews - 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 In the previous post, I looked at root-level cardinality estimates vs. […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews\/","og_site_name":"Joe Sack","article_published_time":"2013-11-28T16:22:13+00:00","article_modified_time":"2013-12-30T03:13:42+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/11\/image_thumb18.png"}],"author":"Joseph Sack","twitter_misc":{"Written by":"Joseph Sack","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews\/","name":"Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2013-11-28T16:22:13+00:00","dateModified":"2013-12-30T03:13:42+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews\/#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":"Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews"}]},{"@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\/1130","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=1130"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/1130\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=1130"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=1130"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=1130"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}

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":1130,"date":"2013-11-28T08:22:13","date_gmt":"2013-11-28T16:22:13","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/joe\/?p=1130"},"modified":"2013-12-29T19:13:42","modified_gmt":"2013-12-30T03:13:42","slug":"using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews\/","title":{"rendered":"Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews"},"content":{"rendered":"

This post is a continuation of the SQL Server 2014 Cardinality Estimator enhancements exploration series:<\/p>\n

A first look at the query_optimizer_estimate_cardinality XE event<\/a><\/li>\n
\u201cCSelCalcCombineFilters_ExponentialBackoff\u201d Calculator<\/a><\/li>\n
\u201cCSelCalcCombineFilters_ExponentialBackoff\u201d Calculator\u2013 Part II<\/a><\/li>\n
The CSelCalcAscendingKeyFilter Calculator<\/a><\/li>\n
Cardinality Estimation Model Version<\/a><\/li>\n
Comparing Root-Level Skews in the new Cardinality Estimator<\/a><\/li>\n<\/ul>\n
In the previous post, I looked at root-level cardinality estimates vs. actual row counts across a set of 28 queries).\u00a0 9 plans had improved cardinality but two of them had significant changes.\u00a0 One of the plans for the new CE version had a root-level estimate of 11,199 rows vs. 1,468 actual rows.\u00a0 Compare that to the original estimate of 348 vs. 1,468.<\/p>\n
The query in-question was as follows:<\/p>\n
```
\r\nSELECT AVG(OrderQty) AS 'Average Quantity',\r\nNonDiscountSales = (OrderQty * UnitPrice)\r\nFROM Sales.SalesOrderDetail\r\nGROUP BY (OrderQty * UnitPrice)\r\nORDER BY (OrderQty * UnitPrice) DESC;\r\n<\/pre>\nThe pre-SQL Server 2014 CTP2 query plan was as follows (via SQL Sentry Plan Explorer, showing estimated <\/em>rows):<\/p>\n<\/a><\/p>\n
And here was the new version of the plan (using the new Cardinality Estimator):<\/p>\n
<\/a><\/p>\n
You can see that at the leaf-level, the Cluster Index Scan estimates remain the same, as does the consecutive Compute Scalar. Where it changes is in the Hash Match (Aggregate) operator \u2013 with the new CE estimating 11,200 rows versus the old estimate of 348.<\/p>\n
Out of curiosity, I changed the original query to group by just OrderQty and another query to just group by UnitPrice (in other words \u2013 was the estimate issue related to our OrderQty * UnitPrice expression, or were the skews introduces at the single-column level?) Here was the OrderQty grouping query and plan tree (ignoring the useless nature of averaging the column you\u2019re already group by):<\/p>\n
\r\nSELECT AVG(OrderQty) AS 'Average Quantity',\r\nOrderQty\r\nFROM Sales.SalesOrderDetail\r\nGROUP BY OrderQty\r\nORDER BY OrderQty\u00a0 DESC;\r\n<\/pre>\n<\/a><\/p>\n
And here was the UnitPrice query and plan tree:<\/p>\n
\r\nSELECT AVG(OrderQty) AS 'Average Quantity',\r\nUnitPrice\r\nFROM Sales.SalesOrderDetail\r\nGROUP BY UnitPrice\r\nORDER BY UnitPrice DESC;\r\n<\/pre>\n<\/a><\/p>\n
No large skews for the prior two queries, compared to our original (OrderQty * UnitPrice) skew \u2013 shown below in plan tree form:<\/p>\n
<\/a><\/p>\n
Aside from the standard methods of evaluating single-column statistics (do they exist, are they up to date, do I need to change the sampling) – I then wondered if some of the legacy methods for helping improve estimates would still apply?\u00a0 For example, would adding a calculated column help?<\/p>\n
\r\nALTER TABLE Sales.[SalesOrderDetail]\r\nADD [NonDiscountSales] AS (OrderQty * UnitPrice);\r\n<\/pre>\nRe-executing the original query, I saw the following plan tree (with auto-created stats to the rescue associated with the calculated column):<\/p>\n<\/a><\/p>\n
I wish I could look at the query_optimizer_estimate_cardinality results for the old version of the CE to compare the event output to the new CE.\u00a0 I know it isn\u2019t possible, but it would have been more helpful to understand what changed regarding the Hash Match (Aggregate) estimate.\u00a0 As an aside, the exact math behind the estimates are hidden both in the new and old versions \u2013 so perhaps a comparison of calculators in this scenario would still be limited?<\/p>\n
Regarding XE information, there were 12 query_optimizer_estimate_cardinality events raised for query with the larger estimate skew.\u00a0 The second event out of the 12 events raised had the following calculator output (and it is interesting to see the SingleColumnStat reference in this output followed by two column names \u2013 but I believe this just means both single-column stats were loaded):<\/p>\n
<CalculatorList>
\n<DistinctCountCalculator CalculatorName=”CDVCPlanProjectOrGroupBy”>
\n<SubCalculator Role=”DistinctCountCalculatorToDelegate”>
\n<DistinctCountCalculator CalculatorName=”CDVCPlanLeaf” SingleColumnStat=”OrderQty,UnitPrice”<\/span> \/>
\n<\/SubCalculator>
\n<\/DistinctCountCalculator>
\n<\/CalculatorList><\/p><\/blockquote>\n
And the stats collection was as follows:<\/p>\n
<StatsCollection Name=”CStCollGroupBy” Id=”3″ Card=”11199.81<\/span>“>
\n<LoadedStats>
\n<StatsInfo DbId=”7″ ObjectId=”898102240″ StatsId=”5″ \/>
\n<StatsInfo DbId=”7″ ObjectId=”898102240″ StatsId=”6” \/>
\n<\/LoadedStats>
\n<\/StatsCollection><\/p><\/blockquote>\n
The stats collection was interesting to me as it was our first appearance out of the 12 events of the GROUP BY estimate \u2013 11,199.81 (show earlier in the plan tree rounded up).\u00a0 The loaded stats StatsId of 5 was for the UnitPrice column and the StatsId of 6 was for the OrderQty column.<\/p>\n
On the subject of SingleColumnStat, I wondered if creating multi-column stats (another legacy method for improving CE) on UnitPrice and OrderQty would be helpful.\u00a0 I dropped the old calculated column and created the following multi-column stats instead:<\/p>\n
\r\nCREATE STATISTICS [stats_SalesOrderDetail_OrderQty_UnitPrice] ON\r\nSales.[SalesOrderDetail](UnitPrice, OrderQty);\r\n<\/pre>\nThis answer is, multi-column stats were indeed helpful:<\/p>\n<\/a><\/p>\n
We do see now a reference to MultiColumnStat in the CalculatorList for one of the query_optimizer_estimate_cardinality events:<\/p>\n
<CalculatorList>
\n<DistinctCountCalculator CalculatorName=”CDVCPlanProjectOrGroupBy”>
\n<SubCalculator Role=”DistinctCountCalculatorToDelegate”>
\n<DistinctCountCalculator CalculatorName=”CDVCPlanLeaf” MultiColumnStat=”OrderQty,UnitPrice”<\/span> \/>
\n<\/SubCalculator>
\n<\/DistinctCountCalculator>
\n<\/CalculatorList><\/p><\/blockquote>\n
And we see the new stats object was loaded instead of looking at the two individual stats objects:<\/p>\n
<StatsCollection Name=”CStCollGroupBy” Id=”3″ Card=”1600.00″>
\n<LoadedStats>
\n<StatsInfo DbId=”7″ ObjectId=”898102240″ StatsId=”10″ \/>
\n<\/LoadedStats>
\n<\/StatsCollection><\/p><\/blockquote>\n
So we see that with skews introduced in the new CE \u2013 similar to previous versions and depending on the query and schema, multi-column stats and calculated columns can still be helpful in reducing the CE skew.<\/p>\n
To be continued\u2026<\/p>\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 In the previous post, I looked at root-level cardinality estimates vs. […]<\/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-1130","post","type-post","status-publish","format-standard","hentry","category-cardinality-estimation","category-performance"],"yoast_head":"\nUsing Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews - 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\/using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews - 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 In the previous post, I looked at root-level cardinality estimates vs. […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2013-11-28T16:22:13+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-12-30T03:13:42+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/11\/image_thumb18.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=\"4 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\/using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews\/\",\"name\":\"Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2013-11-28T16:22:13+00:00\",\"dateModified\":\"2013-12-30T03:13:42+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews\/#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\":\"Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews\"}]},{\"@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_head_json":{"title":"Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews - 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\/using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews\/","og_locale":"en_US","og_type":"article","og_title":"Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews - 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 In the previous post, I looked at root-level cardinality estimates vs. […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews\/","og_site_name":"Joe Sack","article_published_time":"2013-11-28T16:22:13+00:00","article_modified_time":"2013-12-30T03:13:42+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/11\/image_thumb18.png"}],"author":"Joseph Sack","twitter_misc":{"Written by":"Joseph Sack","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews\/","name":"Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2013-11-28T16:22:13+00:00","dateModified":"2013-12-30T03:13:42+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/using-legacy-methods-to-lessen-sql-server-2014-cardinality-estimator-skews\/#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":"Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews"}]},{"@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\/1130","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=1130"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/1130\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=1130"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=1130"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=1130"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
```