Need guidelines for INDEXING database table

**Basanth** · Aug 7 '13, 06:25 AM

I hope these guidelines may help you.

Always look at the query plan first. It will show you the optimal current execution plan from the query engine's point of view. Find the most expensive part of the execution plan and start optimizing from there.

If you see table scan, optimize. Table scan is the slowest possible way of execution. Table scan means not only that no index is used, but that there is no clustered index for this table at all. Even if you can only replace table scan with clustered index scan, it is still worth it.
If you see clustered index scan, find out whether it can be replaced with index seek. For that, find what conditions are applied to this table. Usually, conditions exist for two or three fields of the table.

Find out the most selective condition (that is, the condition that would produce the smallest number of records if applied alone), and see whether an index on this field exists.

Any index that lists this field first will qualify. If there is no such index, create it and see whether the query engine picks it up.
If the query engine is not picking up the existing index (that is, if it is still doing a clustered index scan), check the output list. It is possible that seek on your index is faster than clustered index scan, but involves bookmark lookup that makes the combined cost greater than use of a clustered index.
Clustered index operations (scan or seek) never need bookmark lookup, since a clustered index already contains all the data.

If the output list is not big, add those fields to the index, and see whether the query engine picks it up. Please remember that the combined size is more important than the number of fields. Adding three integer fields to the index is less expensive than adding one varchar field with an average data length of 20.

Try to make your index covering, and see whether it works better than clustered index scan. Please note that it is not always possible to make the query engine pick up your index automatically. A small table or a low-selectivity index will produce clustered index scan, even if your index is covering.
If you see bookmark lookup, it means that your index is not covering. Try to make it covering if it makes sense.

The execution plan selected by the query engine may be not the best one. The query engine makes certain assumptions about disk subsystem and CPU cost versus IO cost. These assumptions sometimes can be incorrect. If you don't believe that the query engine's selection is the best one, run a query in the loop for 10 to 15 minutes with automatic selection, change the query to use your index (you will have to use index hint to force it), and then run it for 10 to 15 minutes again. Compare the results to see which one works better.

Regards
Basanth

**ck9663** · Aug 7 '13, 04:24 PM

Good advice Basanth...

Always remember, if you will create one index on a table, make it a clustered one.

Happy Coding!!!

~~ CK

**SwapnilD** · Aug 10 '13, 06:39 AM

Hi, thanks for answer, much depreciated.

Need guidelines for INDEXING database table

Need guidelines for INDEXING database table

Comment

Comment

Comment