The SQL Server Query Optimizer

To understand how to write SQL code for SQL Server that performs well, it is important to appreciate how the query optimizer works. Ben Nevarez explains the essentials, in a broad sweep through a complex subject, in an article taken from his new book 'Inside the SQL Server Query Optimizer'.

Summary

This article has covered a lot of ground in a relatively short space, but by now you should have an understanding (or at least an appreciation) of the concepts that are tackled in the book. 免费电子书可以下载

We’ve been introduced to the fundamental operations of the SQL Server Query Optimizer, from parsing the initial query to how the Query Optimizer tries to find the best possible execution plan for every query submitted to SQL Server. We’ve also looked at the complexity of the optimization process, including the challenges it faces in exploring the potentially vast search space and accurately estimating cardinality and the cost of candidate execution plans.

As a result of the research that has gone into solving some of those challenges, the Query Optimizer implemented in SQL Server is based on the extensible Cascades Framework architecture, which facilitates the addition of new functionality to the query optimizer, including new operators and transformation rules. Finally, we touched upon the problem of finding an efficient join order in a multi join query, which is still a fundamental challenge in query optimization.