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.
Paul White (@SQL_Kiwi) walks through a new trace flag in SQL Server 2016 designed to yield better execution plans (and performance) for queries involving computed columns.