Index Optimization Tips

General concepts

In this article I want to show how you can improve the speed of your queries by choosing the proper indexes, what kinds of indexes MS SQL supports and what is the advantage and disadvantage of using indexes in particular situation.

There are clustered and nonclustered indexes. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf nodes of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
When you create clustered or nonclustered index you can set the fill factor option to specify how full SQL Server should make each index page. When there is no free space to insert new row on the index page, SQL Server will create new index page and transfer some rows from the previous page to the new one. This operation is called page splits.
You can reduce the number of page splits by setting the appropriate fill factor option to reserve free space on each page of the index. The fill factor is a value from 1 through 100 that specifies the percentage of the index page to be left empty.
The default value for fill factor is 0. It is treated similarly to a fill factor value of 100, the difference in that SQL Server leaves some space within the upper level of the index tree for FILLFACTOR = 0. The fill factor percentage is used only at the time the index is created.
A covering index is a index, which includes all of the columns referenced in the query. So the index contains the data you are looking for and SQL Server does not have to look up the actual data in the table.
When you perform many data modification operations (INSERT, UPDATE or DELETE statements), then the table fragmentation occurs. If you want to determine the level of fragmentation, you can run the DBCC SHOWCONTIG command.

Optimization tips

· Every index increases the time in takes to perform INSERTS, UPDATES and DELETES, so the number of indexes should not be very much. Try to use maximum 4-5 indexes on one table, not more. If you have read-only table, then the number of indexes may be increased.

· Keep your indexes as narrow as possible. This reduces the size of the index and reduces the number of reads required to read the index.

· Try to create indexes on columns that have integer values rather than character values.

· If you create a composite (multi-column) index, the order of the columns in the key are very important. Try to order the columns in the key as to enhance selectivity, with the most selective columns to the leftmost of the key.

· If you want to join several tables, try to create surrogate integer keys for this purpose and create indexes on their columns.

· Create surrogate integer primary key (identity for example) if your table will not have many insert operations.

· Clustered indexes are more preferable than nonclustered, if you need to select by a range of values or you need to sort results set with GROUP BY or ORDER BY.

· If your application will be performing the same query over and over on the same table, consider creating a covering index on the table.

· You can use the SQL Server Profiler Create Trace Wizard with "Identify Scans of Large Tables" trace to determine which tables in your database may need indexes. This trace will show which tables are being scanned by queries instead of using an index.

· You can use sp_MSforeachtable undocumented stored procedure to rebuild all indexes in your database. Try to schedule it to execute during CPU idle time and slow production periods.

sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"