My prior post demonstrated the influence of row and page counts on estimated CPU and I/O cost.  Now in this post I’m going to step through the influence of row and page counts on memory grants for a Sort operator.  I’ll be using the MemoryGrantInfo element from the query execution plan to measure the influence.  I wrote about this element in my “Memory Grant Execution Plan Statistics” blog post.

Starting State

I’ll be querying the member table which has 142 data pages and 10,000 rows.  The query itself will have an ORDER BY curr_balance.  The curr_balance column is currently not supported by an existing index and will require a Sort operator in the plan, which then means that a memory grant will be needed:

SELECT  [member].[member_no],
[member].[lastname],
[curr_balance]
FROM    [dbo].[member]
ORDER BY [curr_balance] ASC
OPTION  (RECOMPILE, MAXDOP 1);

Using an actual execution plan, I see the following MemoryGrantInfo data for the baseline query:

<MemoryGrantInfo
SerialRequiredMemory=”512″
SerialDesiredMemory=”1904″
RequiredMemory=”512″
DesiredMemory=”1904″
RequestedMemory=”1904″
GrantWaitTime=”0″
GrantedMemory=”1904″
MaxUsedMemory=”680″ />

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).  I used this hint for this blog post to maintain an apples-to-apples comparison (serial plan vs. serial plan).

The Sort operator has the following CPU and I/O costs (not including subtree costs):

  • Estimated CPU Cost = 0.60342
  • Estimated I/O Cost = 0.0112613

Inflating the page count while maintaining the row count

What happens if we inflate the page count while maintaining the row count?

-- Please don't use this in production - for demonstration purposes only!
UPDATE STATISTICS dbo.[member]
WITH ROWCOUNT = 10000, PAGECOUNT = 149000;

Re-executing the query, I see the following memory grant statistics:

<MemoryGrantInfo
SerialRequiredMemory=”512″
SerialDesiredMemory=”1904″
RequiredMemory=”512″
DesiredMemory=”1904″
RequestedMemory=”1904″
GrantWaitTime=”0″
GrantedMemory=”1904″
MaxUsedMemory=”680″ />

The memory grant statistics for the 149000 page plan are identical to the 142 page plan.

The Sort operator has the following CPU and I/O costs (excluding subtree costs):

  • Estimated CPU Cost = 0.60342
  • Estimated I/O Cost = 0.0112613

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).

Inflating the row count while maintaining the page count

What happens if I inflate the row count while maintaining the page count (at the original 142 pages)?

  
UPDATE STATISTICS dbo.[member]
WITH ROWCOUNT = 100000000, PAGECOUNT = 142;

This time we see a difference:

<MemoryGrantInfo
SerialRequiredMemory=”512″
SerialDesiredMemory=”8564592″
RequiredMemory=”512″
DesiredMemory=”8564592″
RequestedMemory=”4579608″
GrantWaitTime=”0″
GrantedMemory=”4579608″
MaxUsedMemory=”680″ />

SerialRequiredMemory and RequiredMemory are the same as prior scenarios (512) – but we see a big spike in SerialDesiredMemory, DesiredMemory, RequestedMemory and GrantedMemory.

The Sort operator has the following CPU and I/O costs (excluding subtree costs):

  • Estimated CPU Cost = 5003.06
  • Estimated I/O Cost = 7875.01

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).

Summary

So recapping what we see when manipulating row and page counts for a query that uses a Sort operator:

  • The memory grant introduced by the Sort operator was sensitive to row counts, but not page counts.
  • The Sort operator’s estimated CPU and I/O costs were both sensitive to row counts, but not page counts.

Related Posts