I believe all the DBA know that how important is “Index” in the database if you would like to optimize the database server performance. It maybe a pain for your server if you create the index wrongly.
Here are some check lists on the index optimization that you should bear in mind and should go through. This is the foundation of the index optimization but it mean and help you a lot, if you already follow and apply all the checklist below, then you may need to try out more experiment to get more optimize:
Index optimization checklist:
- Create Index on frequently used columns in T-SQL Code. Columns used in WHERE, ORDER BY and GROUP BY are good candidate for Indexes. Create Index on columns which are used in JOIN Condition.
- Remove any un-necessary Indexes. As Index occupies hard drive space as well as it decreases performance of the entire insert, updates, deletes to the table.
- Smaller Index Key gives better performance than Index key which covers large data or many columns
- Index on Integer Columns performs better than varchar columns.
- Clustered Index must exist before creating Non-Clustered Index.
- Clustered Index must be created on Single Column which is not changing and narrow in size. Best candidate is primary key.
- Non-clustered Indexes increases performance of the query that returns fewer rows and rows has wide selectivity spectrum.
- Each table must have one Clustered Index.
- If column have low selectivity avoid creating Index on that column as it slow down the rows modification and system will not get benefit from Index.
- Multiple Columns Index or Covered Index should be ordered as Most Selective column on left and gradually decreasing selectivity as they go right.
- Use SORT_IN_TEMPDB option when table is created if tempdb is on different disk. This will increase the performance to create Index.
- Rebuild Index frequently using ALTER INDEX and De-fragment Index to keep performance optimal for Indexes.