Paul White goes deep on grouped aggregation pushdown, and explains examples where this optimization is unsuccessful or simply not attempted, using the undocumented command DBCC CSINDEX.
Paul White explains why the IGNORE_DUP_KEY index option on a clustered index can result in much poorer performance than for a nonclustered unique index.
