Tip

SQL Server index tuning for peak performance

Ezine

This article can also be found in the Premium Editorial Download "SQL Server Insider: Selling a SQL Server 2008 R2 upgrade."

Download it now to read this article plus other related content.

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

    Requires Free Membership to View

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
(DepartmentId)
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
(DepartmentId)
INCLUDE (LastName, FirstName, TeamId)

CREATE INDEX IX_Team_TeamId ON dbo.Team
(TeamId)
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
(DepartmentId)
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
(DepartmentId)
INCLUDE (LastName, FirstName)
WHERE DepartmentID = 4
WITH (FILLFACTOR=70)

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)

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,
    sys.indexes.name,
    sys.dm_db_index_usage_stats.user_seeks,
    sys.dm_db_index_usage_stats.user_scans,
    sys.dm_db_index_usage_stats.user_lookups,
    sys.dm_db_index_usage_stats.user_updates
fromsys.dm_db_index_usage_stats
joinsys.indexesonsys.dm_db_index_usage_stats.object_id=sys.indexes.object_id
    AND  sys.dm_db_index_usage_stats. index_id =sys.indexes.index_id
    ANDsys.indexes.name notlike'PK%'
    ANDOBJECT_NAME(sys.indexes.object_id)<>'sysdiagrams'
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)
ORDERBYOBJECT_NAME(sys.indexes.object_id),
    sys.indexes.name

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, but they 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.

ABOUT THE AUTHOR
Denny Cherry has over a decade of experience managing SQL Server, including MySpace.com'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.

This was first published in August 2010

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.