Introduction

When we execute a query, whether by typing a Transact-SQL statement or by using a tool such as Microsoft Access, it is highly likely we will require that rows are read from one or more database tables. Suppose we require that SQL Server performs a join of two tables, table A containing a dozen rows and table B containing a million rows. How should SQL Server access the required data in the most efficient fashion? Should it access table A looking for rows that meet the selection criteria and then read matching rows from table B or should it access table B first? Should it use indexes if any are present or do a table scan? If indexes are present and there is a choice of index, which one should SQL Server choose?

The good news is that SQL Server contains a component known as the query optimizer which will automatically take a query passed to it and attempt to execute the query in the most efficient way. The bad news is that it is not magic and it does not always come up with the best solution. A database administrator should be aware of the factors that govern query optimization, what pitfalls there are and how the query optimizer can be assisted in its job. Database administrators who know their data well can often influence the optimizer with the judicious use of indexes to choose the most efficient solution.

What do we...