I have a long running query (fact table with 100 million rows joining a number of small dim tables then group by) that's spilling to tempdb, even though (after some tweaking) the CE is very close to the actual number of rows, see plan:
Looking for an explanation, I noticed the following memory grant info:
Environment: SQL Server 2012 SP1 Enterprise, server RAM 256 GB, SQL Server max memory 200 GB, buffer pool size 42 GB, workspace max size 156 GB (GrantedMemory = 156 * 25% ~= 38 GB)
- does that mean no matter how good the CE is, the query has no chance of not spilling over? since the query max ram is hard capped at 38 GB
- does query optimizer not take max query ram into consideration when building the plan? (forcing a Hash Match aggregate would eliminate the sort step and significantly improve query performance, unfortunately, the actual query is coming from Cognos and we have no control of it)
- will increase the 25% cap to close to 100% be a sensible option here? (assuming that the said server access can be controlled to limit the number of concurrent query requests)
Anonymized query plan at Paste The Plan
When forcing a hash match aggregate (instead of a sort + stream aggregate), the query consistently finishes 3 - 4 times quicker. Unfortunately, the actual query is coming from Cognos, and we have no way of changing it.
There is no hash spill in the hash aggregate plan. The query optimizer won't pick hash match aggregate because if I look at the operator cost for hash vs stream aggregate, the CPU cost of hash group is 2 - 3 times higher than doing stream aggregate.
In both stream and hash aggregate, the estimated output rows is exactly the same as the input (~100 million rows).
The query uses a single NC columnstore index, and column stats are all regularly updated.