sqlskills-logo-2015-white.png

Row and Page Count Influence on Sort Memory Grants

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.

3 thoughts on “Row and Page Count Influence on Sort Memory Grants

  1. This seems backwards to me. It would seem to me that rows shouldn’t matter, only pages. I have this argument with developers when they say “oh no! it’s going to be 70 million rows”, but then I point out that it’s only 3 or 4 INT columns, that’s not really that big. The inverse is true. If a table is ‘only’ 10000 rows, but the row size is very large, that’s actually a big table.

    It seems to me if you had 10000 rows in a table, it could be 10 data pages or it could be 10000 data pages (assuming one row per page,ick). It’s really the row size or number of pages that should determine memory usage I’d think.

    I wonder why doesn’t the optimizer see the actual (average or estimated) data size (row size) vs. the row count.

    1. Hi John,

      Glad you found these posts interesting! 🙂

      Regarding only pages mattering being a preferred option… One thought is that the memory grant considers row counts and row size (I didn’t raise the discussion of row size in this post – but it is certainly relevant to the point you raise). So the number of pages may be less helpful in that context compared to just needing to know what will be needed for the memory grant based on estimated rows and their associated size. Not defending the model though – just thinking about why it may be the way it is today.

      Thanks!

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.