SQL Server Index

General Tips on Optimizing SQL Server Indexes

All the tips provided on this website about indexing are general guidelines. As with any general guideline, there are exceptions. Because of this, it is a good idea to test out various indexing strategies for the most common queries run against your database. Only by testing different indexing strategies can you be sure that you […]

Tips on Optimizing SQL Server Clustered Indexes

As a rule of thumb, every table should have a clustered index. Generally, but not always, the clustered index should be on a column that monotonically increases–such as an identity column, or some other column where the value is increasing–and is unique. In many cases, the primary key is the ideal column for a clustered […]

SQL Server Index Tuning Wizard Tips

The Index Tuning Wizard is a powerful tool designed to help you identify existing indexes that aren’t being used, along with recommending new indexes that can be used to help speed up queries. It uses the actual queries you are running in your database, so its recommendations are based on how your database is really being […]

Tips for Rebuilding Indexes

Periodically (daily, weekly, or monthly) perform a database reorganization on all the indexes on all the tables in your database. This will rebuild the indexes so that the data is no longer fragmented. Fragmented data can cause SQL Server to perform unnecessary data reads, slowing down SQL Server’s performance. It will also update column statistics. […]

Tips on Optimizing Covering Indexes

If you have to use a non-clustered index (because your single clustered index can be used better elsewhere in a table), and if you know that your application will be performing the same query over and over on the same table, consider creating a covering index on the table for the query. A covering index, […]

Tips on Optimizing SQL Server Non-Clustered Indexes

Non-clustered indexes are best for queries: That return few rows (including just one row) and where the index has good selectivity (generally above 95%). That retrieve small ranges of data (not large ranges). Clustered indexes perform better for large range queries. Where both the WHERE clause and the ORDER BY clause are both specified for […]

Tips on Optimizing SQL Server Composite Indexes

A composite index is an index that is made up of more than one column. In some cases, a composite index is also a covering index. Generally speaking, composite indexes (with the exception of covering indexes) should be avoided. This is because composite indexes tend to be wide, which means that the index will be […]