How to determine the memory need of a query?
I am trying to configure a MAXMEMORYSIZE for a resource pool used for application queries. For this, I profiled a set of representative queries to figure out maximum memory need for these queries. While profiling, I noticed that the memory acquired by the query changes based on which pool I am using.
If I used general pool for profiling the query, it uses more than 3 GB and completes in less than 3 seconds. However, if I used another pool with '3G' MEMORYSIZE and planned concurrency of 9, the query completes in 20 seconds and uses 231 MB. GROUP BY HASH is the most expensive operation in this query and no other queries were runing when the query was profiled.
Because I see different values of "Total memory required by the query" when profling the query using different pools, I am wondering whay would be the best way to estimate MAXMEMORYSIZE parameter for the pool.