Summary

In this chapter, we explored the internals of the SQL Database Engine’s query optimization process and defined many important concepts that any database professional writing T-SQL queries will keep coming back to, especially when troubleshooting query performance issues. The CE is a fundamental part of the SQL Database Engine’s Query Optimizer: knowing how it uses statistics and the importance of keeping updated and relevant statistics for the overall query optimization process empowers database professionals to write good queries – queries that both drive and leverage good database schema designs. But also, understanding the main estimation model assumptions allows us to account for these when writing queries and avoid pitfalls that hurt query performance. We will see these pitfalls in much more detail in Chapter 5, Writing Elegant T-SQL Queries, and Chapter 6, Discovering T-SQL Anti-Patterns in Depth.

If, at the end of the optimization process, we...