Using an actual execution plan, I see the following MemoryGrantInfo data for the baseline query:<\/p>\n

<MemoryGrantInfo
\nSerialRequiredMemory=”512″
\nSerialDesiredMemory=”1904″
\nRequiredMemory=”512″
\nDesiredMemory=”1904″
\nRequestedMemory=”1904″
\nGrantWaitTime=”0″
\nGrantedMemory=”1904″
\nMaxUsedMemory=”680″ \/><\/p>\n

I’m forcing a serial plan, so you’ll see that the SerialRequiredMemory matches the RequiredMemory attribute and the SerialDesiredMemory matches the DesiredMemory attribute (and others as well).\u00a0 I used this hint for this blog post to maintain an apples-to-apples comparison (serial plan vs. serial plan).<\/p>\n

The Sort operator has the following CPU and I\/O costs (not including subtree costs):<\/p>\n

\n
• Estimated CPU Cost = 0.60342<\/li>\n
• Estimated I\/O Cost = 0.0112613<\/li>\n<\/ul>\n

  Inflating the page count while maintaining the row count<\/strong><\/p>\n

  What happens if we inflate the page count while maintaining the row count?<\/p>\n

  \r\n-- Please don't use this in production - for demonstration purposes only!\r\nUPDATE STATISTICS dbo.[member]\r\nWITH ROWCOUNT = 10000, PAGECOUNT = 149000;\r\n<\/pre>\n
  Re-executing the query, I see the following memory grant statistics:<\/p>\n
  <MemoryGrantInfo
  \nSerialRequiredMemory=”512″
  \nSerialDesiredMemory=”1904″
  \nRequiredMemory=”512″
  \nDesiredMemory=”1904″
  \nRequestedMemory=”1904″
  \nGrantWaitTime=”0″
  \nGrantedMemory=”1904″
  \nMaxUsedMemory=”680″ \/><\/p>\n
  The memory grant statistics for the 149000 page plan are identical to the 142 page plan.<\/p>\n
  The Sort operator has the following CPU and I\/O costs (excluding subtree costs):<\/p>\n
  \n
  Estimated CPU Cost = 0.60342<\/li>\n
  Estimated I\/O Cost = 0.0112613<\/li>\n<\/ul>\n
  So we see identical CPU and I\/O costs for the Sort operator even with inflated page counts – even though the leaf-level Clustered Index Scan itself has an inflated estimated I\/O cost (110.373).<\/p>\n
  Inflating the row count while maintaining the page count<\/strong><\/p>\n
  What happens if I inflate the row count while maintaining the page count (at the original 142 pages)?<\/p>\n
    \r\nUPDATE STATISTICS dbo.[member]\r\nWITH ROWCOUNT = 100000000, PAGECOUNT = 142;\r\n<\/pre>\n
  This time we\u00a0see a difference:<\/p>\n
  <MemoryGrantInfo
  \nSerialRequiredMemory=”512″
  \nSerialDesiredMemory=”8564592″<\/span>
  \nRequiredMemory=”512″
  \nDesiredMemory=”8564592″<\/span>
  \nRequestedMemory=”4579608″<\/span>
  \nGrantWaitTime=”0″
  \nGrantedMemory=”4579608″<\/span>
  \nMaxUsedMemory=”680″ \/><\/p>\n
  SerialRequiredMemory and RequiredMemory are the same as prior scenarios (512) – but we see a big spike in SerialDesiredMemory, DesiredMemory, RequestedMemory and GrantedMemory.<\/p>\n
  The Sort operator has the following CPU and I\/O costs (excluding subtree costs):<\/p>\n
  \n
  Estimated CPU Cost = 5003.06<\/li>\n
  Estimated I\/O Cost = 7875.01<\/li>\n<\/ul>\n
  So we see that the Sort operator CPU and I\/O cost is sensitive to row counts, but not data page counts (and the leaf-level Clustered Index Scan shows a 0.107569 estimated I\/O cost and a 110 estimated CPU cost).<\/p>\n
  Summary<\/strong><\/p>\n
  So recapping what we see when manipulating row and page counts for a query that uses a Sort operator:<\/p>\n
  \n
  The memory grant introduced by the Sort operator was sensitive to row counts, but not<\/em> page counts.<\/li>\n
  The Sort operator’s estimated CPU and I\/O costs were both sensitive to row counts, but not page counts.<\/li>\n<\/ul>\n
  \n","protected":false},"excerpt":{"rendered":"
  My prior post demonstrated the influence of row and page counts on estimated CPU and I\/O cost.\u00a0 Now in this post I’m going to step through the influence of row and page counts on memory grants for a Sort operator.\u00a0 I’ll be using the MemoryGrantInfo element from the query execution plan to measure the influence.\u00a0\u00a0I […]<\/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-831","post","type-post","status-publish","format-standard","hentry","category-performance"],"yoast_head":"\n