SQL Server 6.5 Performance Optimization and Tuning Handbook
An explanation of fundamental performance tuning concepts for SQL Server targeted to intermediate level database developers and administrators.
TABLE OF CONTENTS SQL Server 6.5 Performance Optimization and Tuning Handbook
Section 4.2.2: The Second Step: Index Selection
4.2.2 The Second Step: Index Selection
Having identified the search arguments in the query the next step that the query optimizer performs during the query optimization phase is index selection. In this step the query optimizer takes each search argument and checks to see if it is supported by one or more indexes on the table. The selectivity of the indexes is taken into consideration and based on this the query optimizer can calculate the cost of a strategy that uses that index in terms of logical and physical I/Os. This cost is used to compare strategies that use different indexes and a strategy that uses a table scan.
4.2.2.1 Does A Useful Index Exist?
To obtain information on the indexes present on a table and their characteristics, SQL Server can check the sysindexes system table. From the sysindexes table the query optimizer can quickly establish the indexes present on the table by checking the rows that have a value in the id column equal to the object id of the table (as defined in the sysobjects system table) and an indid column value > 0 and < 255. The keycnt, keys1 and keys2 columns help the query optimizer determine on which columns the index is based.
The query optimizer will look for an index based on the same column as the search argument. If the index is a composite index the query optimizer determines if the first column in the index is...
Copyright Butterworth-Heinemann 1997 under license agreement with Books24x7
