"buppcpp" <buppcpp@...> wrote:

>
> I have SortMemUpperLimit = 67108864 in my configuration, but my memory
> usage never goes above my DefaultDbCachePages settings.
>
> I expected the max memory usage to be (DefaultDbCachePages * Page
> Size) + SortMemUpperLimit when a SORT is involved. But I'm not seeing
> it, so I'm assuming my understanding of SortMemUperLimit is incorrect.

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