locked
Why these two queries require the same amount of memory? RRS feed

  • Question

  • USE
     AdventureWorks
    GO
    --query 1
    
    SELECT
     CustomerID,MAX
    (SubTotal) AS
     subtotal 
    FROM
     Sales.SalesOrderHeader 
    WHERE
     SubTotal>10000
    GROUP BY CustomerID --query 2 SELECT CustomerID,MAX (SubTotal) AS subtotal FROM Sales.SalesOrderHeader WHERE SubTotal>100000000 GROUP BY CustomerID
    both of two queries can get correct estimations and use sort operator before stream aggregate: estimated number of rows for query 1 is 1917, the value for query 2 is 1. I think the memory required to sort  is proportioned to the estimated number of rows,  but why the memory grant values for these two queries  are same (3168K)?

    I am using sql server 2008 dev edition 10.0.1600 on XP.

    Thanks.
    Lidong
    Thursday, May 14, 2009 5:18 AM

Answers

  • The amount of memory required for a query is determined by the size of the working sets SQL must allocate, not the number of actual rows returned.   The query plan determines the memory usage -- to build a hash table for a join, for instance, requires an amount of memory based on the size of the input, not the size of the output. 

    There can be cases, especially when intermediate values must be generated from subquery-type expressions, that the output size determines memory usage, but that should be considered a special case.


    Michael Asher
    • Marked as answer by Kalman Toth Saturday, June 20, 2009 10:36 AM
    Friday, June 12, 2009 7:37 PM

All replies

  • let me ask you this: why do you think they should be different?
    I mean you have the same feild to display (and do operations on) from the same data ... only the where condition changes so the server will have to look through all the table anyway. It makes sense to me that the memory requirement is identical. The execution plans are identical and my guess is the memory grant is determined from these. I'm not an expert so it'd be nice to have the exact way sql-server determine the memory grant
    • Edited by Po134 Friday, June 12, 2009 7:51 PM
    Friday, June 12, 2009 7:23 PM
  • The amount of memory required for a query is determined by the size of the working sets SQL must allocate, not the number of actual rows returned.   The query plan determines the memory usage -- to build a hash table for a join, for instance, requires an amount of memory based on the size of the input, not the size of the output. 

    There can be cases, especially when intermediate values must be generated from subquery-type expressions, that the output size determines memory usage, but that should be considered a special case.


    Michael Asher
    • Marked as answer by Kalman Toth Saturday, June 20, 2009 10:36 AM
    Friday, June 12, 2009 7:37 PM
  • Hi Michael,

    I was not saying the actual rows returned, both queries used sort operator which is the only memory-bind operator in queries, query plan determines sort's memory usage base on the size of its input, it turn to be the estimated number of rows of data access operator, so the less the number of rows is, the less the memory is needed.

    since these two quryies have big different estimated numter of rows as input of sort operator, I think they should need different memory usage.

    Thanks.
    Lidong Zhao
    • Edited by lidong zhao Saturday, June 13, 2009 8:30 AM
    Saturday, June 13, 2009 1:34 AM
  • The sort operator does't have to involve allocating a huge chunk of memory; in fact one of SQL Server's sort operator exists soly to transform random i/os into sequential ones.  You can read about it here:

    http://blogs.msdn.com/craigfr/archive/2009/02/25/optimizing-i-o-performance-by-sorting-part-1.aspx

    Michael Asher
    Saturday, June 13, 2009 1:51 AM
  • Hi Michael,

    This is really a interesting post, I think the purpose of sort operator in my queries is not to transform random i/o, it is nessesary to stream aggregate operator.
    Saturday, June 13, 2009 8:27 AM
  • BTW, You can look at the actual amount of memory the operator is allocating (or rather, the fraction it uses from the total memory allocation for the query) by setting SHOWPLAN_XML on, then looking for the node:

    <MemoryFractions Input="xxx" Output="xxx" />

    Michael Asher
    Saturday, June 13, 2009 12:28 PM