Summary

We explored the internals of SQL Server query optimization and defined many important concepts that any database professional who writes T-SQL queries will keep coming back to, especially when troubleshooting query performance issues. The CE is a fundamental part of SQL Server'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 to avoid pitfalls that hurt query performance. We will see these pitfalls in much more detail in Chapter 6, Easily-Identified T-SQL Anti-Patterns, and Chapter 7, Discovering T-SQL Anti-Patterns in Depth.

If, at the end of the optimization process, we still have a plan that is perceived to be inefficient, some avenues of investigation can help us determine the potential reasons for this inefficiency:

• Is it a bad CE? Analyze the execution plan to find the ratio between estimated and actual rows in costly operators. Perhaps the statistics are stalled and need to be updated?
• Is it a parameter-sensitive plan? Is it a dynamic unparameterized T-SQL statement? Or perhaps parameter-sniffing has caused a skewed query plan? The importance of parameters was discussed in Chapter 2, Understanding Query Processing, in the The importance of parameters section.
• Is it an inadequate physical database design? Are there missing indexes? Are data types for keys inadequate, thus leading to unwarranted conversions that affect estimations? Is referential integrity enforced by triggers instead of indexed foreign keys?

These are some of the aspects we must investigate as a potential source of plan inefficiency. In the next chapter, we will learn how to identify these inefficiencies by investigating the various aspects of query execution plans.