Anatomy of an Optimizer [DBA-309]
Enterprise Database Administration & Deployment
In this session, we look in depth at how the query processor works and show you what operations it performs during query optimization to generate equivalent query plans and find a “good enough” plan with limited time during query optimization.
We demonstrate how to use documented trace flags to look at the transformation rules that the optimizer has available.
With each SQL Server version, new transformation rules are introduced, changing the way the query optimization process works and sometimes dramatically improving performance.
We look in depth at some of the most important transformation rules introduced in the SQL Server 2014 optimizer, explain their importance, and demonstrate how they work while comparing the results with older versions of SQL Server.
We explain why query optimization complexity increases exponentially with the number of tables included in the plan, and how to diagnose and remedy issues that relate to bad query plans.
Basic understanding of Query plans and Query optimization