About fragmented indexes

The Fragmented index recommendation is raised when SQL Response determines that the percentage of logical fragmentation of any one index in a monitored database, based on running the DBCC SHOWCONTIG command, exceeds a specified percentage.

Causes of fragmentation

In the normal operation of any production SQL Server, as INSERTs, UPDATEs, and DELETEs are made to tables (and their indexes), rows become fragmented (scattered throughout the database, in addition to leaving empty spaces). Fragmentation requires SQL Server to perform extra work when accessing indexes, which in turn can lead to slower performance. Because of this, it is a best practice to remove this fragmentation on a regular basis.

If you have created an Index Rebuild or Reorganize job, and are still seeing this recommendation, it is possible the jobs are failing. If this is the case, a Job failure or Job did not start alert for that job will also be raised by SQL Response.

Resolving a fragmented index

There are two ways to resolve index fragmentation:

• Use the ALTER INDEX command with the REBUILD option
• Use the ALTER INDEX command with the REORGANIZE option

One or the other of these commands should be executed as part of a regular index defragmentation plan, either executed as a SQL Server Agent job or via a Maintenance Plan.

If an index is heavily fragmented (>30%), then the REBUILD option should be used because it physically rebuilds all of the indexes. However, this is a resource-intensive process that can affect your server's performance and temporarily block users from accessing their data. If you have the Enterprise version of SQL Server, the REBUILD option has an ONLINE option that reduces a lot of these problems.

If an index is not heavily fragmented (between 5% and 30%), then you can use the REORGANIZE option instead. This option does not do as thorough a job as the REBUILD option does, but is it much more lightweight and has minimal impact on an active production database.

Ideally, you should only REBUILD or REORGANIZE indexes that are fragmented. Not all indexes become fragmented (for example, because they are read-only or little used), and defragmenting them uses unnecessary resources. Unfortunately, it is not all that easy to write Transact-SQL code that goes through every index, determines its level of fragmentation, and then performs the appropriate type of defragmentation only on those indexes that need it. Because of this, many DBAs run either REBUILD or REORGANIZE on all of a database's indexes at the same time.

Links to more information

Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

SQL Server Performance.com : Understanding SQL Server's DBCC SHOWCONTIG
http://www.sql-server-performance.com/articles/dba/dt_dbcc_showcontig_p1.aspx

SQL Server Performance.com : Tips for rebuilding indexes
http://www.sql-server-performance.com/tips/rebuilding_indexes_p1.aspx

MS SQL City : Reducing SQL Server Index Fragmentation
http://www.mssqlcity.com/Articles/Adm/index_fragmentation.htm