The query optimizer is one of the most complex pieces of code in the SQL Server database engine. The optimizer's job is to generate a query plan, which is a set of steps that SQL Server will take to carry out a user query. The optimizer makes decisions such as which indexes to use for each table, which join method to use and in which order to process the joined tables, whether to build an internal worktable to hold intermediate results, whether to perform an internal sort, and whether to run the query on multiple processors.

In ...