{"id":4889,"date":"2018-06-05T13:10:59","date_gmt":"2018-06-05T20:10:59","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4889"},"modified":"2020-10-15T12:26:43","modified_gmt":"2020-10-15T19:26:43","slug":"cxconsumer-wait-type-history-and-what-you-need-to-know","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/cxconsumer-wait-type-history-and-what-you-need-to-know\/","title":{"rendered":"CXCONSUMER wait type &#8211; history and what you need to know"},"content":{"rendered":"<p style=\"text-align: justify;\">I&#8217;m a little bit overdue to blog about this new wait type, but I wanted to wait until SQL Server 2016 SP2 was released for a bit and people started seeing this new wait type.<\/p>\n<p style=\"text-align: justify;\"><strong>History<\/strong><\/p>\n<p style=\"text-align: justify;\">Back in September 2016 I created a Connect item (3102145) to split the <a href=\"https:\/\/www.sqlskills.com\/help\/waits\/cxpacket\/\" target=\"_blank\" rel=\"noopener noreferrer\"><em>CXPACKET<\/em><\/a> wait into benign and actionable waits, so that when a <em>CXPACKET<\/em> wait happens, there&#8217;s really something to investigate. Unfortunately the Connect website has been shut down by Microsoft so I can&#8217;t point you to the original request I made (I also tried in the <a href=\"http:\/\/web.archive.org\/\" target=\"_blank\" rel=\"noopener noreferrer\">Internet Archive<\/a> but couldn&#8217;t find it).<\/p>\n<p style=\"text-align: justify;\">The reason I wanted this change is that <em>CXPACKET<\/em> waits have\u00a0always\u00a0been registered by both producer and consumer threads for query plan operators where some threads produce data (i.e. producer threads) and some threads consume the produced data (i.e. consumer threads). However, the waits for the consumer threads are not actionable, because it&#8217;s the *producer* threads that are the cause of the consumer thread waits, and so it&#8217;s the producer thread waits that are actionable. By splitting the consumer waits out, the number of <em>CXPACKET<\/em> waits should be reduced, and those that are left should be investigated.<\/p>\n<p style=\"text-align: justify;\">During the PASS Summit in 2017, my friend Pedro Lopes (<a href=\"https:\/\/blogs.msdn.microsoft.com\/sql_server_team\/\" target=\"_blank\" rel=\"noopener noreferrer\">b<\/a>|<a href=\"https:\/\/twitter.com\/SQLPedro\" target=\"_blank\" rel=\"noopener noreferrer\">t<\/a>) on the Tiger Team announced that they&#8217;d made the change.\u00a0The new wait type, <em>CXCONSUMER<\/em>, was initially added to SQL Server 2017 RTM CU3 and was added to SQL Server 2016 SP2 in April. You can read Pedro&#8217;s blog post <a href=\"https:\/\/blogs.msdn.microsoft.com\/sql_server_team\/making-parallelism-waits-actionable\/\" target=\"_blank\" rel=\"noopener noreferrer\">here<\/a>. Note that there was initially a <a href=\"https:\/\/support.microsoft.com\/en-us\/help\/4057054\/cxpacket-and-cxconsumer-wait-types-show-inconsistent-results\" target=\"_blank\" rel=\"noopener noreferrer\">bug<\/a> with the implementation which was fixed in SQL Server 2017 RTM CU4.<\/p>\n<p style=\"text-align: justify;\"><strong>What You Need To Know<\/strong><\/p>\n<p style=\"text-align: justify;\"><em>CXCONSUMER<\/em> waits can *generally* be ignored, but not always, just like most of the waits we generally ignore. I&#8217;ve seen control threads (thread ID = 0) show <em>CXPACKET<\/em> or <em>CXCONSUMER<\/em>, depending on what&#8217;s happening in the query plan, and I&#8217;ve seen some weird cases of skewed parallelism where everything shows as <em>CXCONSUMER<\/em> instead of\u00a0<em>CXPACKET.\u00a0<\/em><\/p>\n<p style=\"text-align: justify;\">I&#8217;ve added a <a href=\"https:\/\/www.sqlskills.com\/help\/waits\/cxconsumer\/\" target=\"_blank\" rel=\"noopener noreferrer\"><em>CXCONSUMER<\/em> page<\/a> to my waits library, and added it to the &#8216;ignore&#8217; list in all published locations of <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wait-statistics-or-please-tell-me-where-it-hurts\/\" target=\"_blank\" rel=\"noopener noreferrer\">my wait stats script<\/a>. However, if you see a bunch of parallel threads on a long-running query accruing a lot of time waiting for\u00a0<em>CXCONSUMER<\/em>, I&#8217;d troubleshoot it just like\u00a0<em>CXPACKET<\/em> &#8211; look for skewed work distribution problems or something on the producer side of the parallel operator that&#8217;s stopping the producer threads from pushing through rows to the consumers efficiently.<\/p>\n<p style=\"text-align: justify;\">PS And if you do have <em>CXPACKET<\/em> waits, start <a href=\"https:\/\/www.sqlskills.com\/help\/waits\/cxpacket\/\" target=\"_blank\" rel=\"noopener noreferrer\">here<\/a>. Don&#8217;t just blindly follow random internet advice to set your <em>sp_configure<\/em> &#8216;max degree of parallelism&#8217; or &#8216;cost threshold for parallelism&#8217; options so a set value without testing how it affects your workload and whether a higher or lower number is better for your environment.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;m a little bit overdue to blog about this new wait type, but I wanted to wait until SQL Server 2016 SP2 was released for a bit and people started seeing this new wait type. History Back in September 2016 I created a Connect item (3102145) to split the CXPACKET wait into benign and actionable [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[101],"tags":[],"class_list":["post-4889","post","type-post","status-publish","format-standard","hentry","category-wait-stats"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>CXCONSUMER wait type - history and what you need to know - Paul S. Randal<\/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\/paul\/cxconsumer-wait-type-history-and-what-you-need-to-know\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"CXCONSUMER wait type - history and what you need to know - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"I&#8217;m a little bit overdue to blog about this new wait type, but I wanted to wait until SQL Server 2016 SP2 was released for a bit and people started seeing this new wait type. History Back in September 2016 I created a Connect item (3102145) to split the CXPACKET wait into benign and actionable [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/cxconsumer-wait-type-history-and-what-you-need-to-know\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2018-06-05T20:10:59+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-10-15T19:26:43+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\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\/paul\/cxconsumer-wait-type-history-and-what-you-need-to-know\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/cxconsumer-wait-type-history-and-what-you-need-to-know\/\",\"name\":\"CXCONSUMER wait type - history and what you need to know - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2018-06-05T20:10:59+00:00\",\"dateModified\":\"2020-10-15T19:26:43+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/cxconsumer-wait-type-history-and-what-you-need-to-know\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/cxconsumer-wait-type-history-and-what-you-need-to-know\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/cxconsumer-wait-type-history-and-what-you-need-to-know\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"CXCONSUMER wait type &#8211; history and what you need to know\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"CXCONSUMER wait type - history and what you need to know - Paul S. Randal","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\/paul\/cxconsumer-wait-type-history-and-what-you-need-to-know\/","og_locale":"en_US","og_type":"article","og_title":"CXCONSUMER wait type - history and what you need to know - Paul S. Randal","og_description":"I&#8217;m a little bit overdue to blog about this new wait type, but I wanted to wait until SQL Server 2016 SP2 was released for a bit and people started seeing this new wait type. History Back in September 2016 I created a Connect item (3102145) to split the CXPACKET wait into benign and actionable [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/cxconsumer-wait-type-history-and-what-you-need-to-know\/","og_site_name":"Paul S. Randal","article_published_time":"2018-06-05T20:10:59+00:00","article_modified_time":"2020-10-15T19:26:43+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/cxconsumer-wait-type-history-and-what-you-need-to-know\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/cxconsumer-wait-type-history-and-what-you-need-to-know\/","name":"CXCONSUMER wait type - history and what you need to know - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2018-06-05T20:10:59+00:00","dateModified":"2020-10-15T19:26:43+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/cxconsumer-wait-type-history-and-what-you-need-to-know\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/cxconsumer-wait-type-history-and-what-you-need-to-know\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/cxconsumer-wait-type-history-and-what-you-need-to-know\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"CXCONSUMER wait type &#8211; history and what you need to know"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4889","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=4889"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4889\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4889"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4889"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4889"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}