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 (@SQL_Kiwi) follows up on his thorough breakdown of indexed view maintenance with the details of a bug that can render an indexed view incorrect.
Paul White (@SQL_kiwi) talks about a regression in the optimizer's handling of aggregates against partitioned tables when moving from SQL Server 2005 to SQL Server 2008+.
Paul White (@SQL_Kiwi) continues his discussion about optimization and how recognizing transformations can help you make better decisions about potential query re-writes.