Index Fragmentation–“If it isn’t broken, don’t fix it”
Indexes get fragmented. It’s a fact of database life and something that every DBA has to deal with but we do get a choice on HOW we deal with it. The phrase “If it isn’t broken, don’t fix it” is made in reference to SQL Server Maintenance Plans and more specifically the Index Rebuild Task. People without much SQL Server experience will usually setup a maintenance plan job to perform basic database tasks like rebuilding indexes daily or weekly. If most of your tables have clustered indexes, this rewrites your entire database every day or week, creating a lot of unnecessary I/O and transaction log activity. The worst part of all of this is the fact that your indexes at that point in time might not even need to be rebuilt at all. The good news is that you do have a different choice available.
Wouldn’t it be great to be able to look at all your indexes and only maintain the ones that require attention?
The following query will return all the indexes and their fragmentation statistics in the current database:
SELECT
OBJECT_NAME(A.[object_id]) as 'TableName',
B.[name] as 'IndexName',
A.[index_id],
A.[page_count],
A.[index_type_desc],
A.[avg_fragmentation_in_percent],
A.[fragment_count]
FROM
sys.dm_db_index_physical_stats(db_id(),NULL,NULL,NULL,'LIMITED') A INNER JOIN
sys.indexes B ON A.[object_id] = B.[object_id] and A.index_id = B.index_id
For more Information on the Dynamic Management View (DMV), see the following MSDN website: sys.dm_db_index_physical_stats
The following table indicates general guidelines for interpreting the avg_fragmentation_in_percent value.
| Fragmentation | Recommended Action |
| < 5 percent | Do Nothing |
| 5 to 30 percent | Reorganize with ALTER INDEX REORGANIZE |
| > 30 percent | Rebuild with ALTER INDEX REBUILD WITH (ONLINE = ON) or CREATE INDEX with DROP_EXISTING=ON |
Reorganizing an index does not block user access to the index while the reorganization is underway. However, rebuilding or re-creating the index does prevent user access to the index. The exception to this is if the ALTER INDEX REBUILD statement is used with the ONLINE = ON option.
Note: Online index rebuilding requires Enterprise Edition (SQL Server 2005, SQL Server 2008, SQL Server 2008 R2)
For more information on the ALTER INDEX statement, see the following MSDN website: ALTER INDEX (Transact-SQL)
It takes a bit more effort and know-how on your part to be “selective” when you talk about Index Maintenance but what you’ll save in time, processing, and unnecessary I/O is well worth the attention. You should be periodically checking the index fragmentation and taking any necessary corrective action. The rate at which fragmentation may occur depends on the level of user activity but as a general rule I like to check index fragmentation once a week.
For more information about reorganizing and rebuilding indexes, see the following MSDN website: Reorganizing and Rebuilding Indexes
_____________________________________________________________________________________________
Colin Stasiuk (MCP, MCTS SQL 2005/2008, MCITP DBDEV, and MCITP DBA) is an accomplished Microsoft SQL Server DBA who has been working with SQL Server since 1996. He is the founder of Benchmark IT Consulting and his specialties include SQL Server Administration, Performance Tuning, Security, Best Practice / Standards, Upgrades, and Consolidation. Colin is a proud PASS member, President of EDMPASS (The Edmonton Chapter of PASS), and has recently co-authored a book on SQL Server 2008 Policy Based Management.