8

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:

enter image description here

Looking for an explanation, I noticed the following memory grant info:

enter image description here

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)

Questions

  1. 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
  2. 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)
  3. 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.

  • Since this is memory grant related I suggest you first apply Sp3, there was a bug fix related to memory grant in SP2 CU4. – Shanky Nov 19 '16 at 10:40
  • @Shanky we have 2012 SP1 though, not SP2 (we are going to install SP3 at some point, but not sure when) – user107507 Nov 21 '16 at 19:38
8
  1. 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

The overall memory grant for your query appears capped at 37GB given your current hardware and SQL Server configuration.

If the Sort cannot be performed within the Memory Fraction (0.860743 in that plan) of the query memory grant, it will spill to tempdb. Note also that this parallel Sort splits its fraction of the query memory grant equally across 12 threads, and this allocation cannot be rebalanced at runtime.

  1. 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)

Yes, it does, but only as an input to the general costing framework. The optimizer chooses the plan that looks cheapest according to its model. If the numbers are wrong, the plan choice is not likely to be optimal.

In your case, the actual number of rows produced by the Stream Aggregate is significantly less than estimated:

Stream Aggregate output

The optimizer favours Hash Aggregate when fewer, larger groups are expected (since each group occupies a slot in the hash table). The misinformation about the density leads to an incorrect choice of Sort + Stream Aggregate.

The best plan would likely be a hash join instead of the nested loops join, and a hash aggregate. This should be able to extend batch mode processing to the important aggregation step.

SQL Server 2012 was quite limited in its transitions between row and batch mode. The execution engine never returns to batch mode once row mode processing has begun (so row-batch-row is ok, but batch-row-batch is not).

  1. 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)

If you want to increase the amount of memory available for this query, you could certainly do so by changing your Resource Governor setup. Increase the limit by degrees to see if you can locate a good compromise. I would be wary of going too close to 100%.

If the query is suitable for a plan guide, try a HASH GROUP hint.

Longer term, upgrading to SQL Server 2016 will pay dividends since more operators can execute in batch mode (including Sort), dynamic memory grant increases are possible, and ... about a thousand other improvements in columnstore/batch mode processing in general.

5

I can partially answer your questions.

1) I'm not sure that I exactly understand your question. It's not true that SQL server will only spill to tempdb because a cardinality estimate is wrong. Sometimes SQL Server expects that a good enough plan will spill to tempdb.

2) The query optimizer does take memory on the server into account when building the plan. A useful exercise may be to change the amount of memory available to your query to see how the query plan changes. You can do that by changing memory settings on the server, using resource governor, or the undocumented command DBCC OPTIMIZER WHAT_IF(). WHAT_IF is useful if you want to see what the query plan looks like with more memory than 200 GB.

As you pointed out, the query optimizer does not use a hash match aggregate because it thinks that the CPU cost of that operator will be much higher than the sort. One of the criteria that makes a hash match aggregate attractive to the optimizer is when SQL Server estimates there won't be many distinct rows returned. For your query SQL Server thinks that it won't eliminate any rows with the GROUP BY.

How close are the estimated costs for the plans and how do they change as you change memory available to the query?

3) I don't know, but it's definitely something that you should test carefully. Safer options would be to increase SQL Server max ram (200 seems a bit low but maybe there are other applications installed on the server or this is beyond your control) or to improve tempdb performance. I can think of a few other ideas for improving performance but all of them are longshots.

Try running a simpler query that just does a GROUP BY on the fact table. Is there any way to get a better estimate for the number of distinct values? Could creating multi column statistics help?

If you can't change the query you could try replacing the table referenced by a view that selects the data that you need but in a way that changes the plan. This can help in some cases but I can't think of a way to apply the technique here.

It sounds like you have quite a bit of control over this server so you could try creating a plan guide. I have never done this and have never heard anyone say anything positive about plan guides.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.