SQL Server Indexing best practices

Filed under SQL Server Best Practices

1)   Periodically identify potentially missing indexes.
2)   Drop indexes that are never used.
3)   Don’t create redundant indexes.
4)   Generally every table should have at least a clustered index, but not always. The clustered index should be on a column that monotonically increases and value is unique.
5)   You can only create one clustered index per table, think carefully how it will be used.
6)   When you create a composite index, make the most selective column the first column of the index.
7)   Keep the “width” of your indexes as narrow as possible. It’ll reduce the size of the index and reduces the number of disk I/O to improve the performance.
8)   Avoid adding a clustered index on VARCHAR & NVARCHAR column with large width, which makes the index larger, it’ll increase I/O reads, and can hurt performance.
9)   Indexes should be considered on those columns which are frequently accessed by the JOIN, WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses.
10) Add indexes if you know that they will be used by queries run against the table.
11) When creating indexes, try to make them unique indexes if possible. SQL Server can often search through a unique index faster than a non-unique index.
12) Joins between two or more tables in your queries, performance will be optimized if each of the joined columns has proper indexes.
13) A high FILL FACTOR is good for seldom changed data, so consider carefully index FILL FACTOR.
14) Don’t over index your OLTP tables.