Index Selection

Determining which indexes to define on a table involves performing a detailed query analysis. This involves examining the search clauses to see what columns are referenced, knowing the bias of the data to determine the usefulness of the index, and ranking the queries in order of importance and frequency of execution. You have to be careful not to examine individual queries and develop indexes to support one query, without considering the other queries that are executed on the table as well. You need to come up with a set of indexes that works for the best cross-section of your queries.

TIP

A useful tool to help you identify your frequently executed and critical queries is SQL Profiler. I've found SQL Profiler to be invaluable when going into a new client site and having to identify the problem queries that need tuning. SQL Profiler allows you to trace the procedures and queries being executed in SQL Server and capture the runtime, reads and writes , execution plans, and other processing information. This information can help you identify which queries are providing substandard performance, which ones are being executed most often, which indexes are being used by the queries, and so on.

You can analyze this information yourself manually or save a trace to analyze with the Index Tuning Wizard. The features of SQL Profiler are covered in more detail in Chapter 7, "Using the SQL Server Profiler." The Index Tuning Wizard is discussed later in this chapter.

Because it's usually not possible to index for everything, index first for the queries most critical to your applications or those run frequently by many users. If you have a query that's run only once a month, is it worth creating an index to support only that query and having to maintain it throughout the rest of the month? The sum of the additional processing time throughout the month could conceivably exceed the time required to perform a table scan to satisfy that one query.

If, due to processing requirements, you must have the index in place when the query is run, consider creating the index only when you run the query and then drop the index for the remainder of the month. This is a feasible approach as long as the time needed to create the index and run the query that uses the index doesn't exceed the time needed to run the query without the index in place.