We’ll end with a discussion of join ordering, one of the most complex problems in query optimization, and shows how joining tables in an efficient order improves the performance of a query but, at the same time, can exponentially increase the number of execution plans that should be analyzed by the Query Optimizer.
Note: This article contains a large number of example SQL queries, all of which are based on the Adventure Works database.
After all, selecting the right (or wrong) execution plan could mean the difference between a query execution time of milliseconds, and one of minutes or even hours.
Naturally, a better understanding of how the Query Optimizer works can help both database administrators and developers to write better queries and to provide the Query Optimizer with the information it needs to produce efficient execution plans.
All code has been tested for these databases on both SQL Server 2008 and SQL Server 2008 R2.
Note that these sample databases are not included in your SQL Server installation by default, but can be downloaded from the Code Plex web site.
This article starts with an overview on how the SQL Server Query Optimizer works and introduces the concepts that I cover in more detail in my book.We’ll also cover some of the background and challenges of query optimization, such as cardinality and cost estimations, and a section on how to read and understand them is included as well.