{"id":463,"date":"2012-12-11T05:05:37","date_gmt":"2012-12-11T05:05:37","guid":{"rendered":"\/blogs\/joe\/post\/Thinking-about-the-inaccurate_cardinality_estimate-Event.aspx"},"modified":"2013-12-29T19:17:58","modified_gmt":"2013-12-30T03:17:58","slug":"thinking-about-the-inaccurate_cardinality_estimate-event","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/thinking-about-the-inaccurate_cardinality_estimate-event\/","title":{"rendered":"Thinking about the inaccurate_cardinality_estimate Event"},"content":{"rendered":"

SQL Server 2012 introduces the new inaccurate_cardinality_estimate extended event, and I had a few miscellaneous thoughts about it that I wanted to discuss on this post.<\/p>\n

Background reading<\/strong><\/p>\n

If you\u2019re unfamiliar with this event, there isn\u2019t anything as of today in Books Online, but there are two blog references \/ discussions:<\/p>\n

How the query execution engine calculates the threshold it uses to detect inaccurate cardinality estimation and fire the inaccurate_cardinality_estimate extended event?<\/a> – By my friend and former PFE colleague Nacho Alonso Portillo (blog<\/a>)<\/p>\n

Extended Events – inaccurate_cardinality_estimate<\/a> – David Ballantyne\u2019s (blog<\/a> | @davebally<\/a>)<\/p><\/blockquote>\n

Give these both a read before digging in further on this event.\u00a0 The event output of \u201cactual_rows\u201d and \u201cestimated_rows\u201d can be a bit confusing at first, so Nacho walks through the algorithm for when this event fires.<\/p>\n

When should (or could) you use this event?<\/strong><\/p>\n

The primary tool I use for detecting cardinality estimates is in using the Actual (not estimated) query execution plan.\u00a0 Pulling a plan from cache isn\u2019t helpful, because it is just the estimated plan, so the best scenario for troubleshooting suspected cardinality estimate issues is to generate the actual<\/em> plan.<\/p>\n

And yet, there are scenarios where it isn\u2019t practical or possible.\u00a0 For example \u2013 what if the suspected cardinality estimate is related to a long running data modification statement?\u00a0 And what if the issue only occurs in production?\u00a0 You may not be able to actually capture the Actual plan in a contained way in SQL Server Management Studio without polluting production data. And yes, you could capture the plan via \u201cShowplan XML Statistics Profile\u201d or \u201cShowplan Statistics Profile\u201d \u2013 but at what cost to your production activity (remember the \u201cobserver overhead\u201d).\u00a0 Those are events that you should be very careful about capturing in a production environment.<\/p>\n

But this leads us to a similar cost\/benefit discussion of the inaccurate_cardinality_estimate event.\u00a0 The description from Microsoft of this event is very clear about it\u2019s overhead:<\/p>\n

\u201cOccurs when an operator outputs significantly more rows than estimated by the Query Optimizer. Use this event to identify queries that may be using sub-optimal plans due to cardinality estimate inaccuracy. Using this event can have a significant performance overhead so it should only be used when troubleshooting or monitoring specific problems for brief periods of time<\/span>.\u201d<\/p>\n

This event can help us spot cardinality estimate issues for live activity \u2013 and even map to the nodes in the query plan tree (more on this later) \u2013 but Microsoft is very clear that there can be \u201csignificant\u201d performance overhead.\u00a0 This is similar to the warning about pulling the execution plan too, by the way: \u201cOccurs after a SQL statement is executed. This event returns an XML representation of the actual query plan. Using this event can have a significant performance overhead so it should only be used when troubleshooting or monitoring specific problems for brief periods of time.<\/span>\u201d.<\/p>\n

So if you\u2019re going to use this new inaccurate_cardinality_estimate event or other execution plan related events, don\u2019t keep this running for a long period of time \u2013 and think carefully before enabling this on very high throughput systems or those systems running near to capacity.\u00a0 What is the overhead percentage?\u00a0 I haven\u2019t tested this specifically using the inaccurate_cardinality_estimate extended in isolation, but I may do so eventually.\u00a0 It isn\u2019t urgent, as Jonathan Kehayias (blog<\/a> | @sqlpoolboy<\/a>) has already done plenty of work in this area (see Measuring \u201cObserver Overhead\u201d of SQL Trace vs. Extended Events\u201d<\/a>) to establish overhead with even some of the more standard events.<\/p>\n

Where is inaccurate_cardinality_estimate in the GUI?\u00a0 I don\u2019t see it.<\/strong><\/p>\n

David Ballantyne (blog<\/a> | @davebally<\/a>) created a Connect item on this subject, and Microsoft noted that this was a debug event, requiring the debug channel to be enabled (checked off) in order to see it.<\/p>\n

\"SNAGHTMLfbfd75\"<\/a><\/p>\n

And of course, you can skip the New Session Wizard and just reference it directly in your CREATE EVENT SESSION definition.\u00a0 For example:<\/p>\n

\r\nCREATE EVENT SESSION [Track_CE_Issues] ON SERVER\r\nADD EVENT sqlserver.inaccurate_cardinality_estimate\r\nADD TARGET package0.ring_buffer\r\nWITH (STARTUP_STATE=ON);\r\nGO\r\n<\/pre>\n
What\u2019s this \u201cnodes\u201d thing you mentioned earlier?<\/strong><\/p>\n
I\u2019ll demonstrate with the following query in the Credit database that has a significant enough cardinality estimate skew to trigger the inaccurate_cardinality_estimate event:<\/p>\n
\r\nUSE [Credit];\r\nGO\r\n\r\nDECLARE @Column INT = 2,\r\n    @Value INT = 10;\r\n\r\nSELECT  [member].[member_no],\r\n        [member].[street],\r\n        [member].[city],\r\n        [charge].[charge_no],\r\n        [charge].[provider_no],\r\n        [charge].[category_no],\r\n        [charge].[charge_dt],\r\n        [charge].[charge_amt],\r\n        [charge].[charge_code]\r\nFROM    [dbo].[charge]\r\nINNER JOIN [dbo].[member]\r\n        ON [member].[member_no] = [charge].[member_no]\r\nWHERE   CHOOSE(@Column, [charge].[provider_no], [charge].[category_no]) = @Value;\r\nGO\r\n<\/pre>\n
SQL Sentry Plan Explorer<\/a> shows the following:<\/p>\n
\"image\"<\/a><\/p>\n
I added in Node ID in the Plan Tree tab for cross reference.<\/p>\n
As for the captured inaccurate_cardinality_estimate event data, we see the following:<\/p>\n
\"image\"<\/a><\/p>\n
7 event rows were captured with seven pieces of information, including the estimated vs. actual rows (and again, read Nacho\u2019s blog<\/a> for the reason why actual_rows doesn\u2019t match the final \u201cactual rows\u201d value).\u00a0 We also see the node_id and thread_id associated with each event. (As an aside – if you\u2019re wondering about the estimated_rows (4532 vs. 36257, for example) \u2013 note that this was a parallel execution plan which used 8 threads.)<\/em><\/p>\n
You see that the operators associated with the firing include node_id 5 (Clustered Index Scan on [charge].[ChargePK]) and node_id 1 (Hash Match).\u00a0 Also notice that while there are skews for the parallelism-related operators, they didn\u2019t fire inaccurate_cardinality_estimate, which seems acceptable to me because the skew I really care about originates at node_id 5.<\/p>\n
\"image\"<\/a><\/p>\n
Anything else?<\/strong><\/p>\n
This post was just a mix of thoughts about this new event.\u00a0 Time will tell if it is practical (or safe) enough to use with any significant frequency.<\/p>\n","protected":false},"excerpt":{"rendered":"
SQL Server 2012 introduces the new inaccurate_cardinality_estimate extended event, and I had a few miscellaneous thoughts about it that I wanted to discuss on this post. Background reading If you\u2019re unfamiliar with this event, there isn\u2019t anything as of today in Books Online, but there are two blog references \/ discussions: How the query execution […]<\/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-463","post","type-post","status-publish","format-standard","hentry","category-cardinality-estimation","category-performance"],"yoast_head":"\nThinking about the inaccurate_cardinality_estimate Event - 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\/thinking-about-the-inaccurate_cardinality_estimate-event\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Thinking about the inaccurate_cardinality_estimate Event - Joe Sack\" \/>\n<meta property=\"og:description\" content=\"SQL Server 2012 introduces the new inaccurate_cardinality_estimate extended event, and I had a few miscellaneous thoughts about it that I wanted to discuss on this post. Background reading If you\u2019re unfamiliar with this event, there isn\u2019t anything as of today in Books Online, but there are two blog references \/ discussions: How the query execution […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/thinking-about-the-inaccurate_cardinality_estimate-event\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2012-12-11T05:05:37+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-12-30T03:17:58+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/inaccurate_cardinality_estimate\/30a29c03\/snaghtmlfbfd75_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=\"5 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\/thinking-about-the-inaccurate_cardinality_estimate-event\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/thinking-about-the-inaccurate_cardinality_estimate-event\/\",\"name\":\"Thinking about the inaccurate_cardinality_estimate Event - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2012-12-11T05:05:37+00:00\",\"dateModified\":\"2013-12-30T03:17:58+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/thinking-about-the-inaccurate_cardinality_estimate-event\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/thinking-about-the-inaccurate_cardinality_estimate-event\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/thinking-about-the-inaccurate_cardinality_estimate-event\/#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\":\"Thinking about the inaccurate_cardinality_estimate Event\"}]},{\"@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":"Thinking about the inaccurate_cardinality_estimate Event - 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\/thinking-about-the-inaccurate_cardinality_estimate-event\/","og_locale":"en_US","og_type":"article","og_title":"Thinking about the inaccurate_cardinality_estimate Event - Joe Sack","og_description":"SQL Server 2012 introduces the new inaccurate_cardinality_estimate extended event, and I had a few miscellaneous thoughts about it that I wanted to discuss on this post. Background reading If you\u2019re unfamiliar with this event, there isn\u2019t anything as of today in Books Online, but there are two blog references \/ discussions: How the query execution […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/thinking-about-the-inaccurate_cardinality_estimate-event\/","og_site_name":"Joe Sack","article_published_time":"2012-12-11T05:05:37+00:00","article_modified_time":"2013-12-30T03:17:58+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/inaccurate_cardinality_estimate\/30a29c03\/snaghtmlfbfd75_thumb.png"}],"author":"Joseph Sack","twitter_misc":{"Written by":"Joseph Sack","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/thinking-about-the-inaccurate_cardinality_estimate-event\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/thinking-about-the-inaccurate_cardinality_estimate-event\/","name":"Thinking about the inaccurate_cardinality_estimate Event - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2012-12-11T05:05:37+00:00","dateModified":"2013-12-30T03:17:58+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/thinking-about-the-inaccurate_cardinality_estimate-event\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/thinking-about-the-inaccurate_cardinality_estimate-event\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/thinking-about-the-inaccurate_cardinality_estimate-event\/#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":"Thinking about the inaccurate_cardinality_estimate Event"}]},{"@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\/463","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=463"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/463\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=463"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=463"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=463"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}