> If you use ORDER BY / GROUP BY / DISTINCT clauses and the engine

chooses a

> SORT plan then the external sort happens. First, it tries to sort

the entire

> amount of data in the internal buffer of 1MB. If your recordset

doesn't fit,

> the engine starts allocating new sort memory blocks of size

SortMemBlockSize

> (allocation granularity) until it's able to sort the entire

recordset or

> SortMemUpperLimit of bytes becomes allocated. If the recordset

still doesn't

> fit, the rest of the sort data goes to the temp files on disk.
>
> So, your understanding is correct, but you'll see such a maximum

memory

> usage during quite big sorts only.
>
>
> Dmitry

That's what I thought, when I use DISTINCT I don't see the memory
usage go up like it should.

Example:
mytable has ~2.1 million records.

SELECT DISTINCT store_no FROM mytable;
PLAN(SORT (MYTABLE NATURAL)) Max memory used is ~80MB


SELECT store_no FROM mytable ORDER by onhand;
PLAN(SORT (MYTABLE NATURAL)) Max memory used is ~280MB

Can you check this out?

Thanks