SQL Server

New Tools to Diagnose Index Health

Randy Dyess

At a Glance:

• Index fragmentation
• Index usage
• Index operational activity

Download the code for this article: DyessSQLIndex2007_03.exe (151KB)

One of the top items in any SQL Server performance-tuning checklist is tweaking the indexes in a database. The ability of the SQL Server query optimizer to make proper use of indexes during the execution of a query depends not only on the creation of effective indexes but

also on the health of the indexes. A series of dynamic management views (DMVs) and functions (DMFs), introduced in SQL Server™ 2005, can help database administrators determine the effectiveness of their indexes and discover any performance problems.

DMVs and DMFs allow you to look into a server and return information from the server state that helps you monitor the server instance's health and performance and diagnose problems. Database administrators familiar with previous versions of SQL Server will find that these DMVs and DMFs will replace using DBCC commands, executing certain system stored procedures, querying many system tables, and capturing events with SQL Profiler.

Three key functions and views-namely, sys.dm_db_index_physical_stats, sys.dm_db_index_usage_stats, and sys.dm_db_index_operational_stats-are available to help you understand whether your indexes are working as expected. They let you review the I/O and locking patterns of your indexes, and understand whether your indexes are being utilized by the query optimizer in a manner that does not lead to unnecessary contention in the database.

Index Fragmentation

The sys.dm_db_index_physical_stats DMF has been created as a replacement to DBCC SHOWCONTIG and will show an index's fragmentation. However, unlike DBCC SHOWCONTIG, which places a shared lock (S) on the table containing the index, sys.dm_db_index_physical_stats only places an intent shared lock (IS), which greatly reduces blocking of the table during the execution of the function.

To determine the fragmentation of an index when using sys.dm_db_index_physical_stats, you examine a combination of three columns in the output of the function. Logical fragmentation of indexes (extent fragmentation of heaps) can be determined by looking at the value returned in the avg_fragmentation_in_percent column. Logical fragmentation is the percentage of pages that are out of order in the leaf level of an index, while extent fragmentation is the percentage of out-of-order extents in the leaf level of an index. Logical and extent fragmentation can affect the performance of an index by requiring additional I/O and disk head movement because the disk head must skip around to read pages in order. You should strive to keep both logical and extent fragmentation as close to zero as possible.

Internal fragmentation of an index is the percentage of page fullness. Of course, you want the index page as full as possible, but you also need to balance fullness against the number of inserts into index pages in order to keep the number of page splits to the absolute minimum.

The avg_page_space_used_in_percent argument of sys.dm_db_index_physical_stats should be examined to determine index page fullness. To properly configure how close this number is to 100 percent, adjust the fillfactor of an index while watching the number of page splits that are occurring. At some point, the number of page splits will start increasing dramatically, indicating that you have set the fillfactor of the index higher than it should be. Adjusting an index's fillfactor takes time and testing and should not be done without proper planning. (Indexes that do not have random inserts into an index can have their fillfactors set to 100 without worrying about increased page splits.)

To determine the fragmentation levels of all indexes in the AdventureWorks.HumanResources.Employee table, you could use a statement like this:

SELECT * 
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,OBJECT_ID('HumanResources.Employee')
,NULL 
-- NULL to view all indexes; 
-- otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') -- We want all information

Make use of this DMF to automatically determine which indexes need to be rebuilt, which need to be reorganized, and which do not need maintenance. Examining the values of the avg_page_space_used_in_percent and avg_fragmentation_in_percent columns of this DMF for index fragmentation that falls outside of accepted logical and density threshold can help you determine which operation to perform on the index.

Depending on the state of your indexes, the example shown in Figure 1 may not return data in your copy of the AdventureWorks sample database, but it can easily be adapted to other databases.

Figure 1 Examining page fullness and fragmentation

--Reorganize the following indexes in the AdventureWorks database
USE AdventureWorks
GO

SELECT OBJECT_NAME([object_id]) AS 'Table Name',
index_id AS 'Index ID'
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,NULL -- NULL to view all tables
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') --We want all information
WHERE ((avg_fragmentation_in_percent > 10 
AND avg_fragmentation_in_percent < 15) -- Logical fragmentation
OR (avg_page_space_used_in_percent < 75 
AND avg_page_space_used_in_percent > 60)) --Page density
AND page_count > 8 -- We do not want indexes less than 1 extent in size
AND index_id NOT IN (0) --Only clustered and nonclustered indexes

--Rebuild the following indexes in the AdventureWorks database
USE AdventureWorks
GO

SELECT OBJECT_NAME([object_id]) AS 'Table Name',
index_id AS 'Index ID'
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,NULL -- NULL to view all tables
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') --We want all information
WHERE ((avg_fragmentation_in_percent > 15) -- Logical fragmentation
OR (avg_page_space_used_in_percent < 60)) --Page density
AND page_count > 8 -- We do not want indexes less than 1 extent in size
AND index_id NOT IN (0) --Only clustered and nonclustered indexes

It would be easy to store the results of the queries into a table variable and then loop through the table variable to build a dynamic string for the correct ALTER INDEX statement (see Figure 2).

Figure 2 Building a dynamic ALTER INDEX string

--Rebuild the following indexes in the AdventureWorks database
USE AdventureWorks
GO

--Table to hold results
DECLARE @tablevar TABLE(lngid INT IDENTITY(1,1), objectid INT,
index_id INT)

INSERT INTO @tablevar (objectid, index_id)
SELECT [object_id],index_id
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,NULL -- NULL to view all tables
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') --We want all information
WHERE ((avg_fragmentation_in_percent > 15) -- Logical fragmentation
OR (avg_page_space_used_in_percent < 60)) --Page density
AND page_count > 8 -- We do not want indexes less than 1 extent in size
AND index_id NOT IN (0) --Only clustered and nonclustered indexes

SELECT 'ALTER INDEX ' + ind.[name] + ' ON ' + sc.[name] + '.'
+ OBJECT_NAME(objectid) + ' REBUILD' 
FROM @tablevar tv
INNER JOIN sys.indexes ind
ON tv.objectid = ind.[object_id]
AND tv.index_id = ind.index_id
INNER JOIN sys.objects ob
ON tv.objectid = ob.[object_id]
INNER JOIN sys.schemas sc
ON sc.schema_id = ob.schema_id

Index Usage

While sys.dm_db_index_physical_stats is a powerful replacement for DBCC SHOWCONTIG and can help reveal the health of an index, you will often face the more complex issue of determining which indexes are useful for the queries executed against a table. Often database developers or administrators build indexes on a table that they think will be used by the query optimizer during the execution of a query. In previous releases of SQL Server, knowing if these indexes were actually being used was far more difficult. One either had to drop the index and see if the performance of queries was affected or capture the execution plans of the queries and scan for the usage of the index.

There's a new dynamic management view-sys.dm_db_index_usage_stats-that makes it easy to understand how indexes are being utilized by the query optimizer and the queries executed against a table. This view can be examined to determine the usefulness of an index, allowing you to drop any indexes that are not being used by the query optimizer. No longer do you need to worry that an index is simply wasting storage space or that the maintenance of unused indexes is degrading database performance.

By examining the output of this DMV for indexes with zero seeks and scans, you can determine whether an index has been used since the last time SQL Server was started. Keep in mind, though, that many DMVs and DMFs are not persisted and will reset themselves back to zero once SQL Server is restarted. Take this into account when using a DMV or DMF to determine the usage of an index. The index may simply not have been needed since the last time the service was restarted but is needed by week-end, month-end, or quarterly report queries.

To view all indexes in an instance that have not been used since the last restart of the SQL Server service, you can use the following statement:

SELECT DB_NAME(database_id),OBJECT_NAME([object_id])
FROM sys.dm_db_index_usage_stats
WHERE user_seeks = 0
AND user_scans = 0
AND user_lookups = 0
AND system_seeks = 0
AND system_scans = 0
AND system_lookups = 0

Index Operational Activity

If you want to understand the operational activity of your indexes, the sys.dm_db_index_operational_stats DMF will be very useful. You can use it to view the I/O, locking, latching, and access method activity on each index in a database, which can help you understand how your indexes are being used and diagnose index locking issues due to extensive I/O activity or the existence of a "hot spot" in the index.

Use the latch wait columns of this DMF to help establish the amount of time that READ and WRITE operations take to acquire access to an index's resources. This can help you determine whether the disk subsystem used to store the index is adequate for the index's I/O activity. It can also indicate whether the design and usage of the index have introduced a hot spot where extensive activity in one or more pages in an index causes contention for the data contained in those pages. Such contention often leads to excessive blocking of operations trying to READ or WRITE to this area.

Figure 3 shows how to determine the locking and I/O patterns for all indexes in the AdventureWorks.HumanResources.Employee table.

Figure 3 Determining locking and I/O patterns

SELECT page_latch_wait_count --page latch counts
,page_latch_wait_in_ms --page latch wait times
,row_lock_wait_in_ms --row lock wait times
,page_lock_wait_in_ms --page lock wait times
,row_lock_count --row lock counts
,page_lock_count --page lock counts
,page_io_latch_wait_count --I/O wait counts
,page_io_latch_wait_in_ms --I/O wait times
FROM sys.dm_db_index_operational_stats (DB_ID('AdventureWorks')
,OBJECT_ID('HumanResources.Employee')
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
)

Find Out More

The DMVs and DMFs introduced in this article have many uses besides the ones described here. Take the time to review the SQL Server Books Online articles describing these functions and views pointed to in the "Additional Resources" sidebar in order to understand the wide range of information that can be returned and examined using them.

To learn about some additional index DMFs and DMVs that are not discussed in this article, see the blog posting by the SQL Server query optimization team at blogs.msdn.com/queryoptteam/570176.aspx.

Additional Resources

• sys.dm_db_index_physical_stats
• sys.dm_db_index_usage_stats
• sys.dm_db_index_operational_stats

Randy Dyess is a mentor with Solid Quality Learning where he specializes in SQL Server OLTP systems. Randy is the author of numerous books and articles about SQL Server. He is the founder and principle author of www.TransactSQL.Com and www.Database-Security.Info.

© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.