SQL Index Monitor

Overview

The Index Fragmentation form provides a quick snapshot of the indexes in the Database that are fragmented more than the chosen value. The Microsoft SQL recommendation is to not allow fragmentation over 30%, so the default value is set to 30. This value can be altered.

Table Column Meanings:

Table Name : This is the table name that the index belongs to.
Index Name: This is the name of the index.
Type: This is the description of the index type.
Frag %: This is the logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit.
Index Sz: This is the actual size of the index.
Defrag: Click the appropriate button to reorganize or rebuild the index.

How to use the Index Fragmentation Table

Double Clicking a Row

Double clicking a row on the Index Fragmentation Table will display a popup box with more detailed information about how that index is used. This data is reset anytime the database is restarted for any reason. This can be used to help evaluate possible index clean up if the database has been running for a long period of time.

Table Color Meanings for Frag% > Fragmentation limit

Dark Purple: This color represents a table that has no indexes but a a high fragmentation. It is recommended that a clustered index be assign to this table.
Red: This color represents a table with fragmentation greater than 30% and an index size greater than 5MB. It is recommended to rebuild this index if you are experiencing slowness issues.
Light Red: This color represents a table with fragmentation greater than 30% and an index size between 2-5MB. It is recommended to rebuild this index if you are experiencing slowness issues.
Yellow: This color represents a table with fragmentation greater than 30% and an index size between 1-2MB. It is recommended to rebuild this index.
Light Yellow: This color represents a table with fragmentation greater than 30% and an index size between 500-1000KB. It is recommended to watch or rebuild this index.

Table Color Meanings for Frag% < Fragmentation limit

Dark Green: This color represents a table with fragmentation between 20-30% and an index size size greater than 1MB. It is recommended to rebuild this index.
Light Purple: This color represents a table that has no indexes.
Green: This color represents a table with fragmentation between 10-20% and an index size size greater than 1MB. It is recommended to watch or rebuild this index.
Light Green: This color represents a table with fragmentation between 5-10% and an index size greater than 1MB. It is recommended to watch or rebuild this index.

Rebuild Versus Reorganize

The decision to rebuild, reorganize or just leave an index alone will vary from case to case. It may be necessary to experiment to determine the best threshold for the environment.

Note

Fragmentation on small indexes is usually not controllable, so small indexes may not realize any benefits from rebuilding or reorganizing.

REBUILD	REORGANIZE
This process drops the existing index and recreates it	This process does not drop the index, but physically reorganizes it
Will lock the database resources	Does not lock the database resources
Can be executed while the database is online or offline (If contains LOB* data it must be excuted offline)	Only executes while the database online
Settings can be changed that affect the index, such as fill factor	Settings stay the same
Index-column statistics are updated	Statistics are not updated
Reclaims disk space by compacting the pages in new index	Works in two phases, compaction and defragmentation
Requires extra space (the size of the current index)	Does not require free space
Fixes extent fragmentation	Fixes mainly logical fragmentation
If the index spans multiple data files, it will be addressed	If the index spans multiple files, it will only reorder pages within the same file

When to do which?

It is commonly recommended to rebuild indexes that are over 40% and to reorganize indexes that are between 5-40% fragmented. This also depends on the size of the table and how often that index is depended upon. Smaller tables will have high fragment sizes, but not benefit from reorganization or rebuilding. Tables with LOB* data that are rebuilt will need to be rebuilt offline, so performing some of these tasks should be scheduled as part of a regular maintenance plan with database backups.

*LOB data (text, ntext, varchar(max), nvarchar(max), varbinary(max), image, and xml data type columns)