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.
Jonathan Kehayias shares insight about new UDF information in showplan, and shows that only some tools will show it (SSMS 17+ & SentryOne Plan Explorer).
