{"id":484,"date":"2012-07-31T09:41:21","date_gmt":"2012-07-31T09:41:21","guid":{"rendered":"\/blogs\/joe\/post\/e2809cMemoryGrantInfoe2809d-Execution-Plan-Statistics.aspx"},"modified":"2013-01-02T20:31:50","modified_gmt":"2013-01-03T04:31:50","slug":"memory-grant-execution-plan-statistics","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/memory-grant-execution-plan-statistics\/","title":{"rendered":"Memory Grant Execution Plan Statistics"},"content":{"rendered":"<p>SQL Server 2008 execution plans include an optional MemoryGrant attribute in the QueryPlan element \u2013 but SQL Server 2012 significantly expands on memory grant related statistics with the new MemoryGrantInfo element and associated attributes. <\/p>\n<p>Here is an example of MemoryGrantInfo from an actual SQL Server 2012 execution plan:<\/p>\n<blockquote>\n<p>&lt;MemoryGrantInfo SerialRequiredMemory=&quot;5632&quot; SerialDesiredMemory=&quot;11016&quot; RequiredMemory=&quot;47368&quot; DesiredMemory=&quot;52808&quot; RequestedMemory=&quot;52808&quot; GrantWaitTime=&quot;0&quot; GrantedMemory=&quot;52808&quot; MaxUsedMemory=&quot;4312&quot; \/&gt;<\/p>\n<\/blockquote>\n<p>The unit of measurement is in KB \u2013 and in the below perfmon screenshot you can see that the \u201c52808\u201d value in the GrantedMemory attribute matches the Granted Workspace Memory (KB) performance monitor counter:<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/memorygrantinfo\/46fb034e\/snaghtmla4ae6cb.png\"><img decoding=\"async\" style=\"background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px\" title=\"SNAGHTMLa4ae6cb\" border=\"0\" alt=\"SNAGHTMLa4ae6cb\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/memorygrantinfo\/259b5db2\/snaghtmla4ae6cb_thumb.png\" width=\"570\" height=\"84\" \/><\/a><\/p>\n<p>Examining the latest <a href=\"http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan\/\" target=\"_blank\">Showplan Schema<\/a> (last updated March 2012) under the MemoryGrantType section, you\u2019ll find a documentation element which states the following:<\/p>\n<blockquote>\n<p>\u201c<a><\/a>Provide memory grant estimate as well as actual runtime memory grant information. Serial required\/desired memory attributes are estimated during query compile time for serial execution. The rest of attributes provide estimates and counters for query execution time considering actual degree of parallelism.\u201d<\/p>\n<\/blockquote>\n<p>To test the serial plan related memory stats, I re-ran my original query using MAXDOP 1 and got the following results in my actual execution plan:<\/p>\n<blockquote>\n<p>&lt;MemoryGrantInfo SerialRequiredMemory=&quot;1536&quot; SerialDesiredMemory=&quot;1600&quot; RequiredMemory=&quot;1536&quot; DesiredMemory=&quot;1600&quot; RequestedMemory=&quot;1600&quot; GrantWaitTime=&quot;0&quot; GrantedMemory=&quot;1600&quot; MaxUsedMemory=&quot;328&quot; \/&gt;<\/p>\n<\/blockquote>\n<p>The serial memory (desired and required) numbers shifted downwards.&#160; My original MemoryGrantInfo was for a query referencing a columnstore index executing in <em>batch <\/em>mode (with parallelism, as required).&#160; <\/p>\n<p>Here is the original plan:<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/memorygrantinfo\/0c4f4b13\/image.png\"><img fetchpriority=\"high\" decoding=\"async\" style=\"background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px\" title=\"image\" border=\"0\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/memorygrantinfo\/21111dc6\/image_thumb.png\" width=\"877\" height=\"153\" \/><\/a><\/p>\n<p>By capping the parallelism to a MAXDOP 1, the QO still chose to reference the columnstore index scan operator, but the plan operator requirements changed and thus so did the overall memory requirements (including a change from a Hash Match to a Merge Join):<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/memorygrantinfo\/066481ad\/image.png\"><img decoding=\"async\" style=\"background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px\" title=\"image\" border=\"0\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/memorygrantinfo\/69e78fcc\/image_thumb.png\" width=\"886\" height=\"202\" \/><\/a><\/p>\n<p>The Granted Workspace Memory (KB) perfmon counter \u2013 which showed 1600 KB for the serial plan \u2013 matched the 1600 value for SerialDesiredMemory, DesiredMemory and RequestedMemory.<\/p>\n<p>While the MemoryGrantInfo element doesn\u2019t replace the need for the sys.dm_exec_query_memory_grants DMV when trying to evaluate request level concurrent memory grant activity, having this additional information in the execution plan is much more convenient when you\u2019re trying to identify requirements scoped to a specific query. <\/p>\n","protected":false},"excerpt":{"rendered":"<p>SQL Server 2008 execution plans include an optional MemoryGrant attribute in the QueryPlan element \u2013 but SQL Server 2012 significantly expands on memory grant related statistics with the new MemoryGrantInfo element and associated attributes. Here is an example of MemoryGrantInfo from an actual SQL Server 2012 execution plan: &lt;MemoryGrantInfo SerialRequiredMemory=&quot;5632&quot; SerialDesiredMemory=&quot;11016&quot; RequiredMemory=&quot;47368&quot; DesiredMemory=&quot;52808&quot; RequestedMemory=&quot;52808&quot; GrantWaitTime=&quot;0&quot; [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22,28],"tags":[],"class_list":["post-484","post","type-post","status-publish","format-standard","hentry","category-execution-plan","category-performance"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Memory Grant Execution Plan Statistics - 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\/memory-grant-execution-plan-statistics\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Memory Grant Execution Plan Statistics - Joe Sack\" \/>\n<meta property=\"og:description\" content=\"SQL Server 2008 execution plans include an optional MemoryGrant attribute in the QueryPlan element \u2013 but SQL Server 2012 significantly expands on memory grant related statistics with the new MemoryGrantInfo element and associated attributes. Here is an example of MemoryGrantInfo from an actual SQL Server 2012 execution plan: &lt;MemoryGrantInfo SerialRequiredMemory=&quot;5632&quot; SerialDesiredMemory=&quot;11016&quot; RequiredMemory=&quot;47368&quot; DesiredMemory=&quot;52808&quot; RequestedMemory=&quot;52808&quot; GrantWaitTime=&quot;0&quot; [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/memory-grant-execution-plan-statistics\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2012-07-31T09:41:21+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-03T04:31:50+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/memorygrantinfo\/259b5db2\/snaghtmla4ae6cb_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=\"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\/memory-grant-execution-plan-statistics\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/memory-grant-execution-plan-statistics\/\",\"name\":\"Memory Grant Execution Plan Statistics - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2012-07-31T09:41:21+00:00\",\"dateModified\":\"2013-01-03T04:31:50+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/memory-grant-execution-plan-statistics\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/memory-grant-execution-plan-statistics\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/memory-grant-execution-plan-statistics\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Execution Plan\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/execution-plan\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Memory Grant Execution Plan Statistics\"}]},{\"@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":"Memory Grant Execution Plan Statistics - 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\/memory-grant-execution-plan-statistics\/","og_locale":"en_US","og_type":"article","og_title":"Memory Grant Execution Plan Statistics - Joe Sack","og_description":"SQL Server 2008 execution plans include an optional MemoryGrant attribute in the QueryPlan element \u2013 but SQL Server 2012 significantly expands on memory grant related statistics with the new MemoryGrantInfo element and associated attributes. Here is an example of MemoryGrantInfo from an actual SQL Server 2012 execution plan: &lt;MemoryGrantInfo SerialRequiredMemory=&quot;5632&quot; SerialDesiredMemory=&quot;11016&quot; RequiredMemory=&quot;47368&quot; DesiredMemory=&quot;52808&quot; RequestedMemory=&quot;52808&quot; GrantWaitTime=&quot;0&quot; [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/memory-grant-execution-plan-statistics\/","og_site_name":"Joe Sack","article_published_time":"2012-07-31T09:41:21+00:00","article_modified_time":"2013-01-03T04:31:50+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/memorygrantinfo\/259b5db2\/snaghtmla4ae6cb_thumb.png"}],"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\/memory-grant-execution-plan-statistics\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/memory-grant-execution-plan-statistics\/","name":"Memory Grant Execution Plan Statistics - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2012-07-31T09:41:21+00:00","dateModified":"2013-01-03T04:31:50+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/memory-grant-execution-plan-statistics\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/memory-grant-execution-plan-statistics\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/memory-grant-execution-plan-statistics\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/"},{"@type":"ListItem","position":2,"name":"Execution Plan","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/execution-plan\/"},{"@type":"ListItem","position":3,"name":"Memory Grant Execution Plan Statistics"}]},{"@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\/484","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=484"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/484\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=484"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=484"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=484"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}