SQL Server 2000 Performance Guidelines - Indexes
From Guidance Share
- J.D. Meier, Srinath Vasireddy, Ashish Babbar, Sharon Bjeletich and Alex Mackman
Create Indexes Based on Use
Indexes come at a cost that must be balanced between write and read operations. Write operations may be negatively and positively affected by indexes. Read operations are mostly benefited by indexes. You have to understand the way that your system is used to find the optimal indexes. Esoteric discussions about the degree to which insert operation performance is affected by indexes are of limited value if the number of insert operations is small, and your system performs intensive read operations. Spend time evaluating indexes for the most commonly queried tables, the most commonly used queries, and the most problematic queries. Design indexes to support these tables and queries in a systemic manner. As mentioned previously, designing indexes is an art, not a science. It takes knowledge of your system to create effective indexes.
Do not create indexes if a table is rarely queried, or if a table does not ever seem to be used by the optimizer. Avoid indexes on bit, text, ntext, or image data types because they are rarely used. Avoid very wide indexes and indexes that are not selective.
Keep Clustered Index Keys As Small As Possible
Because non-clustered indexes store clustered index keys as their row locators. The row locators reference the actual data row. Therefore, keep clustered index keys as small as possible.
Consider Range Data for Clustered Indexes
If you frequently query the database for ranges of rows by using clauses such as BETWEEN, or operators such as > and <, consider a clustered index on the column specified by the WHERE clause. Generally, clustered indexes are not as effective for primary keys in transaction tables, but they are very effective for primary keys in domain or lookup tables that may never be used other than in joins. In general, every table should have a clustered index unless there is a demonstrated performance reason not to have one.
Create an Index on All Foreign Keys
Be sure to create an index on any foreign key. Because foreign keys are used in joins, foreign keys almost always benefit from having an index.
Create Highly Selective Indexes
Create indexes that exhibit high selectivity. In other words, create indexes that have many distinct values. For example, an index on a region column may have a small number of distinct values. Therefore, there may not be enough distinct values for the optimizer to use. Another example of an item that may not have enough distinct values is a bit column. Since there are only two values, an index cannot be very selective and as a result, the index may not be used.
Use the DBCC SHOW_STATISTICS command on a table or index to better understand the statistics on columns in an index. In the output of this command, density is used to indicate selectivity. Density is calculated as one divided by the number of distinct values. Therefore, a unique index has a density of 1/number of rows. For example, a table with 1,000 rows would have a density of 0.001. An index on a bit column has a density of 0.5 because you divide one by the only two possible unique values in a bit column. The smaller the density number is, the greater the selectivity.
The best numbers to use for density are the All Density numbers in the DBCC SHOW_STATISTICS command output, not the Density number in the first result that is produced.
Figure 14.10 shows the DBCC SHOW_STATISTICS command output for the PK_Orders index on an orders table. The output shows a very selective density because it uses the primary key.
Figure 14.10: DBCC SHOW_STATISTICS output
For more information about statistics, see "Statistics Used by the Query Optimizer in SQL Server 2000" at http://msdn.microsoft.com/library/en-us/dnsql2k/html/statquery.asp.
Consider a Covering Index for Often-Used, High-Impact Queries
Queries that are frequently called, problematic queries, or queries that use lots of resources are good candidates for a covering index. A covering index is an index that includes all the columns that are referenced in the WHERE and SELECT clauses. The index "covers" the query, and can completely service the query without going to the base data. This is in effect a materialized view of the query. The covering index performs well because the data is in one place and in the required order. A covering index may improve scalability by removing contention and access from the main table.
Use Multiple Narrow Indexes Rather than a Few Wide Indexes
SQL Server can use multiple indexes per table, and it can intersect indexes. As a result, you should use multiple narrow indexes that consist of only one column because narrow indexes tend to provide more options than wide composite indexes.
Also, statistics are only kept for the first column in a composite index. Multiple single column indexes ensure statistics are kept for those columns. Composite indexes are of greatest value as covering indexes. Because the first column is the column with statistics, you typically use composite indexes if that column is also a reference in the WHERE clause.
A side consideration of creating smaller indexes is the use of the CHECKSUM function to create a hash index on a very wide column. This allows you to create smaller indexes. It is a good approach when you need an index on long character columns where you also need to limit your use of space.
Create Composite Indexes with the Most Restrictive Column First
When you create a composite index, remember that only the first column stores statistics. Try to make that column the most restrictive column. If the composite index is not selective enough, the optimizer may not use it. Also, a WHERE clause that does not use all the columns included in the composite index may cause the index not to be used. For example, a WHERE clause that skips a column in the middle of the composite index may cause the index not to be used.
Consider Indexes on Columns Used in WHERE, ORDER BY, GROUP BY, and DISTINCT Clauses
Consider creating an index on columns that are used in WHERE clauses and in aggregate operations such as GROUP BY, DISTINCT, MAX, MIN, or ORDER BY. These generally benefit from an index, although you need to measure and test to validate that there is a benefit in your scenario.
Remove Unused Indexes
Be sure to remove all unused or out-of-date indexes. Unused or out-of-date indexes continue to impact write operations because they need to be maintained even though they are not used. They are still used by the optimizer in execution plan considerations. You can use SQL Profiler to determine the indexes that are used.
Use the Index Tuning Wizard
The Index Tuning Wizard (ITW) uses the same information and statistics that the optimizer uses to create an execution plan. You should use this tool to obtain guidance and tips on index options that might otherwise be overlooked. However, it is not the only tool, and system knowledge is still the best way to create efficient indexes. Capture a representative trace by using SQL Profiler as input to the ITW for more system-wide index suggestions.