Additional Tuning Opportunities
At this point, I have the query tuned from 3 minutes to 2 minutes. The table being summarized contains 81 fields per record. I tried a test and partitioned the data, then added a columnstore index to each partition, which brought the query down from an average of 2 minutes to less than 20 seconds.
Looking that this original query, can anyone provide some suggestions as to things to look at to try and improve the processing time, other than the partitions and columnstore indexes?
A columnstore compresses data much better (less IO), and allows you to read only the columns used in this query (yet less IO). If you look at STATISTICS IO, you should see a huge decline in the logical reads.
A columnstore also enables the query to execute in batch mode, which make things go orders of magnitude faster.
(I don't think that the partitioning you added did anything for performance)