Overview
The Index Fragmentation form provides a quick snapshot of the indexes in the Database that are fragmented more than the chosen value. The MS SQL recommendation is to not allow fragmentation over 30%, so the default value is set to 30. This value can be altered.
SQL Index Monitor User Interface
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
| 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)