Indexing Tips for Optimal Database Performance

Indexing Tips for Optimal Database Performance

By : Kasim Wirama, MCDBA

I would like to give some tips how to do proper indexing that gives balance between SELECT query and DML (INSERT/UPDATE/DELETE) query.

Here are the tips:

1. If you have surrogate key as primary key, make sure you create UNIQUE constraints.
2. Add non clustered index as needed by testing it to make sure it will give significant performance on frequently executed query.
3. If parent table has tight relationship with child table such as SalesOrderHeader and SalesOrderDetail, it is better to create indexing on foreign key column at SalesOrderDetail.
4. Create “thin” index, “thin” here means that you not include so many columns, if you would like to have covered columns into the index, put them inside INCLUDE() keyword of an index. (see BOL about CREATE/ALTER INDEX in SQL 2005).
5. If your user queries table with different search predicate, it is better to implement some thin indexes rather one “fat” index.
6. Index gives SELECT responsiveness, but only cost to INSERT, partial cost and benefit to UPDATE and DELETE.
7. Apply unique indexes if there is only unique constraints, because unique indexes would tell the optimizer that there is only one unique record in equality search predicate.

So manage your indexes wisely.

Share this post: | | | |