{"id":492,"date":"2012-06-12T10:17:00","date_gmt":"2012-06-12T10:17:00","guid":{"rendered":"\/blogs\/joe\/post\/SQL-Server-2012-Execution-Plane28099s-NonParallelPlanReason.aspx"},"modified":"2013-02-07T15:49:48","modified_gmt":"2013-02-07T23:49:48","slug":"sql-server-2012-execution-plans-nonparallelplanreason","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/sql-server-2012-execution-plans-nonparallelplanreason\/","title":{"rendered":"SQL Server 2012 Execution Plan\u2019s NonParallelPlanReason"},"content":{"rendered":"<p>SQL Server 2012 introduces a \u201cNonParallelPlanReason\u201d attribute in the QueryPlan element in a query execution plan.\u00a0 This attribute is not officially documented as of this writing, and so I was curious which scenarios this new functionality covered.<\/p>\n<p>Take the following example query which runs in parallel on my test SQL Server instance:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSELECT\u00a0\u00a0\u00a0\u00a0 p.ProductLine,\r\nSUM(f.SalesAmount) AS TotalSalesAmount\r\nFROM &#x5B;dbo].&#x5B;FactInternetSales] AS f\r\nINNER JOIN &#x5B;dbo].&#x5B;DimProduct] AS p ON\r\nf.ProductKey = p.ProductKey\r\nGROUP BY p.ProductLine\r\nORDER BY p.ProductLine;\r\n<\/pre>\n<style type=\"text\/css\"><!--\n.csharpcode, .csharpcode pre\n{\nfont-size: small;\ncolor: black;\nfont-family: consolas, \"Courier New\", courier, monospace;\nbackground-color: #ffffff;\n\/*white-space: pre;*\/\n}\n.csharpcode pre { margin: 0em; }\n.csharpcode .rem { color: #008000; }\n.csharpcode .kwrd { color: #0000ff; }\n.csharpcode .str { color: #006080; }\n.csharpcode .op { color: #0000c0; }\n.csharpcode .preproc { color: #cc6633; }\n.csharpcode .asp { background-color: #ffff00; }\n.csharpcode .html { color: #800000; }\n.csharpcode .attr { color: #ff0000; }\n.csharpcode .alt\n{\nbackground-color: #f4f4f4;\nwidth: 100%;\nmargin: 0em;\n}\n.csharpcode .lnum { color: #606060; }\n--><\/style>\n<style type=\"text\/css\"><!--.csharpcode, .csharpcode pre\n{\nfont-size: small;\ncolor: black;\nfont-family: consolas, \"Courier New\", courier, monospace;\nbackground-color: #ffffff;\n\/*white-space: pre;*\/\n}\n.csharpcode pre { margin: 0em; }\n.csharpcode .rem { color: #008000; }\n.csharpcode .kwrd { color: #0000ff; }\n.csharpcode .str { color: #006080; }\n.csharpcode .op { color: #0000c0; }\n.csharpcode .preproc { color: #cc6633; }\n.csharpcode .asp { background-color: #ffff00; }\n.csharpcode .html { color: #800000; }\n.csharpcode .attr { color: #ff0000; }\n.csharpcode .alt\n{\nbackground-color: #f4f4f4;\nwidth: 100%;\nmargin: 0em;\n}\n.csharpcode .lnum { color: #606060; }\n--><\/style>\n<p>A few details on my <em>starting<\/em> state:<\/p>\n<ul>\n<li>I increased the number of rows in FactInternetSales to 123,695,104 rows.<\/li>\n<li>Max degree of parallelism is \u201c0\u201d<\/li>\n<li>Cost threshold for parallelism is \u201c5\u201d<\/li>\n<li>Resource governor is not enabled beyond the default values<\/li>\n<li>The table has a columnstore index on it (unrelated to the current post, but more an FYI)<\/li>\n<li>My plan runs in parallel when unconstrained<\/li>\n<li>The total cost of the aforementioned query is 57.0931<\/li>\n<\/ul>\n<p>Within the query execution plan, NonParallelPlanReason does not show up when the query runs in parallel:<\/p>\n<blockquote><p>&lt;QueryPlan DegreeOfParallelism=&#8221;8&#8243; MemoryGrant=&#8221;52808&#8243; CachedPlanSize=&#8221;64&#8243; CompileTime=&#8221;10&#8243; CompileCPU=&#8221;10&#8243; CompileMemory=&#8221;424&#8243;&gt;<\/p><\/blockquote>\n<p>What happens if I add OPTION (MAXDOP 1) to the query?\u00a0\u00a0 I see the following in the actual plan:<\/p>\n<blockquote><p>&lt;QueryPlan DegreeOfParallelism=&#8221;0&#8243; <span style=\"background-color: #ffff00;\">NonParallelPlanReason=&#8221;MaxDOPSetToOne&#8221;<\/span> MemoryGrant=&#8221;1024&#8243; CachedPlanSize=&#8221;32&#8243; CompileTime=&#8221;7&#8243; CompileCPU=&#8221;7&#8243; CompileMemory=&#8221;384&#8243;&gt;<\/p><\/blockquote>\n<p>If I remove the hint and use EXEC sp_configure &#8216;max degree of parallelism&#8217;, 1, I see the same attribute value:<\/p>\n<blockquote><p>&lt;QueryPlan DegreeOfParallelism=&#8221;0&#8243; <span style=\"background-color: #ffff00;\">NonParallelPlanReason=&#8221;MaxDOPSetToOne&#8221;<\/span> MemoryGrant=&#8221;1024&#8243; CachedPlanSize=&#8221;32&#8243; CompileTime=&#8221;7&#8243; CompileCPU=&#8221;7&#8243; CompileMemory=&#8221;384&#8243;&gt;<\/p><\/blockquote>\n<p>Next I was curious what would happen if I set max degree of parallelism back to 0 and bumped up the cost threshold for parallelism to a higher value, causing the query to execute with a serial plan? This time there was no NonParallelPlanReason attribute and a DegreeOfParallelism=\u201d1\u201d:<\/p>\n<blockquote><p>&lt;QueryPlan <span style=\"background-color: #ffffff;\">DegreeOfParallelism=&#8221;1&#8243;<\/span> MemoryGrant=&#8221;1024&#8243; CachedPlanSize=&#8221;24&#8243; CompileTime=&#8221;6&#8243; CompileCPU=&#8221;6&#8243; CompileMemory=&#8221;384&#8243;&gt;<\/p><\/blockquote>\n<p>What about setting processor affinity to just one core?\u00a0 This time we see a new NonParallelPlanReason:<\/p>\n<p>&lt;QueryPlan DegreeOfParallelism=&#8221;0&#8243; <span style=\"background-color: #ffff00;\">NonParallelPlanReason=&#8221;EstimatedDOPIsOne&#8221;<\/span> MemoryGrant=&#8221;1024&#8243; CachedPlanSize=&#8221;24&#8243; CompileTime=&#8221;8&#8243; CompileCPU=&#8221;8&#8243; CompileMemory=&#8221;384&#8243;&gt;<\/p>\n<p>I\u2019m sure there are other scenarios to explore and if you\u2019ve seen other examples of values for NonParallelPlanReason, please post them in the comments.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>SQL Server 2012 introduces a \u201cNonParallelPlanReason\u201d attribute in the QueryPlan element in a query execution plan.\u00a0 This attribute is not officially documented as of this writing, and so I was curious which scenarios this new functionality covered. Take the following example query which runs in parallel on my test SQL Server instance: SELECT\u00a0\u00a0\u00a0\u00a0 p.ProductLine, SUM(f.SalesAmount) [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28],"tags":[],"class_list":["post-492","post","type-post","status-publish","format-standard","hentry","category-performance"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>SQL Server 2012 Execution Plan\u2019s NonParallelPlanReason - 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\/sql-server-2012-execution-plans-nonparallelplanreason\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server 2012 Execution Plan\u2019s NonParallelPlanReason - Joe Sack\" \/>\n<meta property=\"og:description\" content=\"SQL Server 2012 introduces a \u201cNonParallelPlanReason\u201d attribute in the QueryPlan element in a query execution plan.\u00a0 This attribute is not officially documented as of this writing, and so I was curious which scenarios this new functionality covered. Take the following example query which runs in parallel on my test SQL Server instance: SELECT\u00a0\u00a0\u00a0\u00a0 p.ProductLine, SUM(f.SalesAmount) [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/sql-server-2012-execution-plans-nonparallelplanreason\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2012-06-12T10:17:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-02-07T23:49:48+00:00\" \/>\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\/sql-server-2012-execution-plans-nonparallelplanreason\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/sql-server-2012-execution-plans-nonparallelplanreason\/\",\"name\":\"SQL Server 2012 Execution Plan\u2019s NonParallelPlanReason - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2012-06-12T10:17:00+00:00\",\"dateModified\":\"2013-02-07T23:49:48+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/sql-server-2012-execution-plans-nonparallelplanreason\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/sql-server-2012-execution-plans-nonparallelplanreason\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/sql-server-2012-execution-plans-nonparallelplanreason\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Performance\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/performance\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"SQL Server 2012 Execution Plan\u2019s NonParallelPlanReason\"}]},{\"@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":"SQL Server 2012 Execution Plan\u2019s NonParallelPlanReason - 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\/sql-server-2012-execution-plans-nonparallelplanreason\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server 2012 Execution Plan\u2019s NonParallelPlanReason - Joe Sack","og_description":"SQL Server 2012 introduces a \u201cNonParallelPlanReason\u201d attribute in the QueryPlan element in a query execution plan.\u00a0 This attribute is not officially documented as of this writing, and so I was curious which scenarios this new functionality covered. Take the following example query which runs in parallel on my test SQL Server instance: SELECT\u00a0\u00a0\u00a0\u00a0 p.ProductLine, SUM(f.SalesAmount) [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/sql-server-2012-execution-plans-nonparallelplanreason\/","og_site_name":"Joe Sack","article_published_time":"2012-06-12T10:17:00+00:00","article_modified_time":"2013-02-07T23:49:48+00:00","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\/sql-server-2012-execution-plans-nonparallelplanreason\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/sql-server-2012-execution-plans-nonparallelplanreason\/","name":"SQL Server 2012 Execution Plan\u2019s NonParallelPlanReason - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2012-06-12T10:17:00+00:00","dateModified":"2013-02-07T23:49:48+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/sql-server-2012-execution-plans-nonparallelplanreason\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/sql-server-2012-execution-plans-nonparallelplanreason\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/sql-server-2012-execution-plans-nonparallelplanreason\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/"},{"@type":"ListItem","position":2,"name":"Performance","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/performance\/"},{"@type":"ListItem","position":3,"name":"SQL Server 2012 Execution Plan\u2019s NonParallelPlanReason"}]},{"@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\/492","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=492"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/492\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=492"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=492"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=492"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}