Index optimization is always interesting subject to me. Every time I receive request to help optimize query or query on any specific table. I always ask Jr.DBA to go over following list first before I take a look at it. Most of the time the Query Speed is optimized just following basic rules mentioned below. Once following checklist applied interesting optimization part begins which only experiment and experience can resolve.
- 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 column which are used in JOIN Condition.
- Remove any un-necessary Indexes. As Index occupies hard drive space as well as it decreases performance of all the 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.
Reference : Pinal Dave (https://blog.sqlauthority.com) , Various References