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 larger, requiring more disk I/O to read it, hurting performance.

The main reason composite indexes (not covering composite indexes) are used is to make an index more selective. A better way to ensure selectivity is good database design, not creating composite indexes. [2000, 2005, 2008] Updated 1-29-2009

*****

If you have no choice but to use a composite index, keep the “width” of it as narrow as possible. This reduces the size of the index and reduces the number of disk I/O reads required to read the index, boosting performance. [2000, 2005, 2008] Updated 1-29-2009

*****

A composite index is generally only useful to a query if the WHERE clause of the query matches the column(s) that are leftmost in the index. So if you create a composite index, such as “City, State”, then a query such as “WHERE City = ‘Springfield’” will use the index, but the query “WHERE STATE = ‘MO’” will not use the index. [2000, 2005, 2008] Updated 1-29-2009

*****

Even if the WHERE clause in a query does not specify the first column of an available index (which normally disqualifies the index from being used), if the index is a composite index and contains all of the columns referenced in the query, the query optimizer can still use the index, because the index is a covering index. [2000, 2005, 2008] Updated 1-29-2009

*****

When you create an index with a composite key, the order of the columns of the key is important. Try to order the columns in the key to enhance selectivity, with the most selective columns to the left most of the key. If you don’t do this, and put a non-selective column as the first part of the key, you risk having the Query Optimizer not use the index at all. [2000, 2005, 2008] Updated 1-29-2009

*****

Sometimes, it is a good idea to split a composite index into multiple single-column indexes. This is because only the first column in a composite index has statistics stored for it. And if this first column is not very selective, it may not be used by the Query Optimizer. But if you were to break up the composite index into multiple indexes, then statistics will be kept for each column, and the Query Optimizer will have better information to make better decisions on how to use indexes. SQL Server has the ability to join two or more individual indexes and intersect them, just as if you were using a composite index, giving you the best benefits of single and composite indexes.

This is not to say that multiple single indexes are always better than a single composite index. Only through testing will you know for sure which strategy will offer the best performance in your particular circumstance. [2000, 2005, 2008] Updated 1-29-2009

*****

As you may know, an index is automatically created for column(s) in your table that you specify as a PRIMARY KEY or as UNIQUE. If two or more columns are involved, and a composite index is created, you should choose how the columns will be ordered in the composite index, instead of accepting the default choices offered by SQL Server.

This is because you always want to use the most selective columns at the left of the key to ensure that the composite index is selective enough to be used by the Query Optimizer. If you don’t do this, then the default order of the columns in the composite index may not be very selective, and the index may not be used by Query Optimizer. [2000, 2005, 2008] Updated 1-29-2009




Related Articles :

  • No Related Articles Found

One Response to “Tips on Optimizing SQL Server Composite Indexes”

  1. Darton Williams Reply May 10, 2012 at 2:51 pm

    I’ve found that correct composite indexes can make a huge difference in performance when joining multiple tables and filtering on several columns. I have a query joining three tables, two of which contain only foreign keys. The largest one has ~32000 rows, so not a huge database.

    This query, returning 600 rows, took over 7 minutes to run when I first began testing. The execution plan did not show any table scans and all single-key indexes were being used. Rewriting the query helped, but it still took minutes. Adding two composite indexes on each foreign key table decreased execution time to < 1 second.

    Strangely, the execution plan did not show much of a change in relative time spent on the formerly slow join to the rest of the query.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |