Query memory grant and tempdb spill
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.


  
  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:



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


  
  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.