Problem solve Get help with specific problems with your technologies, process and projects.

SQL Server index tuning for peak performance

Without the correct table indexes, SQL Server performance will quickly suffer. Here are a few tricks every DBA should know.

You can’t just create your indexes randomly. They need to contain the right number of the right types of columns. It’s a slippery slope. The wider the index is, the larger it is. The larger the index is, the slower it is. Slower indexes take longer to load from the disk, which means it will take SQL Server longer to process data in the index.

Make sure that the only columns you index are those used to filter data, such as columns found in the WHERE clause of your query. Any columns that are being returned as part of the SELECT statement, or used to JOIN to child tables, should be added to the index as INCLUDE columns. For example, the following index query contains the correct columns.

SELECT LastName, FirstName
FROM dbo.Employee
WHERE DepartmentId = 4

CREATE INDEX IX_Employee_DepartmentId ON dbo.Employee
INCLUDE (LastName, FirstName)

Below is a more complex query that includes the JOIN column in order to get a better view into columns that should be incorporated as either indexed columns or included columns.

SELECT e.LastName, e.FirstName, t.TeamName
FROM dbo.Employee e
JOIN dbo.Team t ON e.TeamId = t.TeamId
WHERE e.DepartmentId = 4

CREATE INDEX IX_Employee_DepartmentId ON dbo.Employee
INCLUDE (LastName, FirstName, TeamId)

CREATE INDEX IX_Team_TeamId ON dbo.Team
INCLUDE (TeamName)

In this example, the index was created on the DepartmentId column because that is the column that we’re filtering against. We then included the LastName and FirstName columns as the INCLUDE column because the SELECT statement returns those columns. The TeamId column was included because it’s being used as part of the JOIN statement.

On the Team table, the TeamId column is the indexed column and the TeamName column was included so that this column can be returned. While this query could be easily handled by the clustered index that is created on the Primary key, it might be more efficient to create a non-clustered index on the TeamId column and include the TeamName column. This will create a narrower index than the clustered index, which would decrease the number of data pages that would have to be loaded from the data table.

Controlling index length in SQL Server 2008

Including a WHERE clause when you create the index allows you to control the index length and creates a filtered index with just a subset of the data in the table. This filtered index creates a much smaller index that loads from the disk faster than an index containing all of the records on the table. This is especially helpful with larger tables. Below is an example of the query and index.

SELECT LastName, FirstName
FROM dbo.Employee
WHERE DepartmentId = 4

CREATE INDEX IX_Employee_DepartmentId ON dbo.Employee
INCLUDE (LastName, FirstName)
WHERE DepartmentID = 4

When you execute this query, the SQL Server engine will detect that the index is not only a column match for the query but that it is also a filter match, which would give this index priority over one without the filter. While having filtered indexes will increase your database storage requirements, the performance improvement for queries that would otherwise have to query large indexes should be sufficient enough to warrant the additional costs.

Understanding the fill factor setting

The fill factor setting tells SQL Server to leave a certain amount of white space in the data page when you are building, rebuilding or defragmenting an index. If you leave this space free when the index is created, SQL Server can reduce or eliminate index fragmentation by using the white space to put new values in while new records are added or as existing records are updated in the index. However, SQL Server doesn’t maintain this amount of free space on its own. Moving the data around to open up this white space requires the indexes to be rebuilt or reorganized on a regular basis.

But nothing is free, especially in SQL Server. The downside to setting the fill factor is that, because of the white space requirement, the index requires more space on the disk than it actually needs. If your index should be 10 MB, a fill factor of 10% means your index will actually be about 11 MB.

You can set the fill factor setting when the index is being created using the CREATE INDEX statement (shown below) or while rebuilding the index using the ALTER INDEX statement.

CREATE INDEX IX_Employee_DepartmentId ON dbo.Employee
INCLUDE (LastName, FirstName)
WHERE DepartmentID = 4

The importance of index maintenance

Not performing index maintenance is almost as bad as not having indexes on your table at all. Without database maintenance, the physical data pages that make up the index end up fragmented.

The data will always be stored in the correct order within each data page, though over time data pages will become full and SQL Server will have to pick the next available data page to write data to. After SQL Server has filled a data page, the next page that it selects can be located anywhere on the disk, including the far side of the disk (Figure 1).

Figure 1 (click to enlarge)
After SQL Server has filled a data page, the next page that it selects can be located anywhere on the disk, including the far side of the disk

The table data has been written to the outer rings of the disk and the index has been written to the inner rings of the disk. But SQL Server had to allocate a new data page to the disk, and this new data page is on the far outer edge of the disk, which can cause a slowdown while that data page is loaded from the disk.

Failure to rebuild the page will lead to index fragmentation and will slow SQL Server’s ability to move data around the index. Because of this erratic data movement, data will be spread all over the data file, slowing SQL Server even further while attempting to load the index data from the disk.

Remove indexes to boost performance

Having indexes on a database table negatively affects the database. You can see this negative performance when writing changes to the table through INSERT, UPDATE and DELETE statements. Each time you write a change to a table, every index that contains the rows in question must also be updated to reflect this change. Before the introduction of filtered indexes, this meant that every change impacted every index.

With filtered indexes in place, fewer indexes need to be updated. However, the indexes that contain the records still must be updated each time the rows change.

Because of this work, which must be done for each data change, you should remove indexes from the database that aren’t used to query data from the tables. That will reduce the database’s I/O requirements as well the size of the database.

To find the indexes that aren’t being used, query the sys.dm_db_index_usage_stats dynamic management view using a query such as the one shown below.

SelectOBJECT_NAME(sys.indexes.object_id) TableName,,
    AND  sys.dm_db_index_usage_stats. index_id =sys.indexes.index_id notlike'PK%'
wheresys.dm_db_index_usage_stats.database_id =DB_ID()
    and user_scans = 0
    and user_scans = 0
    and user_lookups = 0
    and user_seeks = 0
    andsys.dm_db_index_usage_stats.index_id NOTIN(0,1)

Creating file groups

One great way to improve index performance is to create one or more file groups where you can store non-clustered indexes. Only non-clustered indexes can be stored in a different file group than the table that the index is built on. If you attempt to create a clustered index, whether or not it’s the primary key, on a different file group from the table, then SQL Server will move the table to this new file group.

Separating non-clustered indexes from the base table will offload storage and I/O requirements of the indexes from the tables that the indexes are based on to the other file group. This can decrease the amount of time it takes to make changes to your indexes. Having multiple file groups in your database increases the management overhead of the database, but, in the end, the improvement is worth the work.

When creating the physical file for this secondary file group, be sure to place the files on another set of physical disks so that the workload is actually moved to different spindles. If both physical files are placed on the same physical spindles, then you won’t see much performance gain, if any at all.

Most people believe indexes are generally beneficial, butthey do have an effect on system performance—both good (increase response time for finding rows through select operations) and bad (inserting, updating or deleting rows). Indexes also require maintenance to keep them working to their fullest potential or eventually they will slow down. If you wait long enough, it may actually be slower to process a query with the index than without it.

This article originally appeared in the May 2010 issue of our SQL Server INSIDER e-zine.

Denny Cherry has over a decade of experience managing SQL Server, including's over 175-million-user installation, one of the largest in the world. Denny's areas of expertise include system architecture, performance tuning, replication and troubleshooting. He currently holds several Microsoft certifications related to SQL Server and is a Microsoft MVP. Check out his blog at SQL Server with Mr. Denny.

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning