Optimizing Queries

The SQL Server optimizer automatically performs query optimization by analyzing queries and determining the most efficient execution plan. The optimizer analyzes a query to determine which clauses in the query can be optimized and then selects the useful indexes for the clauses that can be optimized. Finally, the optimizer compares all possible execution plans and selects the most efficient one for execution.

Use the WHERE clause to restrict the number of rows that must be processed to execute a query. Unless absolutely necessary, avoid unrestricted queries that must read and process all the rows of a table. For example, the following restricted query:

SELECT qty FROM sales
WHERE stor_id = 7131

is more efficient than the following unrestricted query:

SELECT qty FROM sales

Avoid returning a large results set to the client for final data selection through browsing. It is much more efficient to restrict the size of the results set, allowing SQL Server to perform the function for which it was intended. This also reduces network I/O and improves concurrency-related performance as the application scales upward to more users.

Because the WHERE clause of a query is the primary focus of the optimizer, use queries that can take advantage of a useful index. Each index on the table includes columns that are possible candidates for including in the WHERE clause. For optimal performance with one of the following indexes on a given column1:

consider a query with a WHERE clause that includes column1. Avoid using a WHERE clause that includes only the second or later columns of a multicolumn index, and not column1. The multicolumn index would not be useful in this case.

For example, given a multicolumn index on the au_lname, au_fname columns of the authors table in the pubs database, the following queries would be likely to use the index:

SELECT au_id, au_lname, au_fname FROM authors
WHERE au_lname = 'White'
AND au_fname = 'Johnson'
SELECT au_id, au_lname, au_fname FROM authors
WHERE au_lname = 'White'

and the following query would not be able to use the index:

SELECT au_id, au_lname, au_fname FROM authors
WHERE au_fname = 'Johnson'