1.34 Index/Table Fragmentation Metrics

The metrics in this category provide information of the defragment level of Microsoft SQL Server . To retrieve data for this metric, DMV "sys.dm_db_index_physical_stats" is queried for each database (for all objects, indices and partitions). The scan level mode to obtain the statistics is DEFAULT or NULL (that is, equivalent to LIMITED), which is the fastest mode and scans the smallest number of pages.

Evaluation and Collection Frequency — Every SUN

Table 1-42 Index/Table Fragmentation Metrics

Metric Description

Database ID (key column)

Database ID number

Object ID (key column)

Object ID number

Index ID (key column)

Index ID number

Partition Number (key column)

Partition number

Index Type Description (key column)

Index type description, such as CLUSTERED INDEX, and HEAP.

Allocation Unit type Description

Description of the allocation unit type:

  • IN_ROW_DATA

  • LOB_DATA

  • ROW_OVERFLOW_DAT

The LOB_DATA allocation unit contains the data that is stored in columns of type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), and xml. For more information, see Data Types (Transact-SQL).

The ROW_OVERFLOW_DATA allocation unit contains the data that is stored in columns of type varchar(n), nvarchar(n), varbinary(n), and sql_variant that have been pushed off-row. For more information, see Row-Overflow Data Exceeding 8 KB.

Number of Index levels

1 = Heap, or LOB_DATA or ROW_OVERFLOW_DATA allocation unit.

Current Level of Index

Zero for index leaf levels, heaps, and LOB_DATA or ROW_OVERFLOW_DATA allocation units.

Greater than zero for nonleaf index levels. index_level will be the highest at the root level of an index.

The nonleaf levels of indexes are only processed when mode = DETAILED.

Average Fragmentation in Percent

Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit. The value is measured as a percentage and takes into account multiple files. For definitions of logical and extent fragmentation, see Remarks.

Zero for LOB_DATA and ROW_OVERFLOW_DATA allocation units.

NULL for heaps when mode = SAMPLED.

Upload Frequency - After every sample.

Alert Text - Average fragmentation for %indexid% is %avg_fragmentation_in_percent%%% where Database ID is %database_id%, Object ID is %object_id% ,Index ID is %index_id%,Partition Number is %partition_number% and Index type Description is %index_type_desc%. It has crossed warning (%warning_threshold%%%) or critical (%critical_threshold%%%) threshold.

Multiple Thresholds - For this metric you can set different warning and critical threshold values for each unique combination of " Database ID ", " Object ID ", " Index ID ", " Partition Number ", and " Index type Description " objects.

If warning or critical threshold values are currently set for any unique combination of " Database ID ", " Object ID ", " Index ID ", " Partition Number ", and " Index type Description " objects, those thresholds can be viewed on the Metric Detail page for this metric.

To specify or change warning or critical threshold values for each unique combination of "Database ID", "Object ID", "Index ID", "Partition Number", and "Index type Description" objects, use the Edit Thresholds page.

Number of fragments in the leaf level

Number of fragments in the leaf level of an IN_ROW_DATA allocation unit. For more information about fragments, see Remarks.

NULL for nonleaf levels of an index, and LOB_DATA or ROW_OVERFLOW_DATA allocation units.

NULL for heaps when mode = SAMPLED.

Average number of pages in one fragment

Average number of pages in one fragment in the leaf level of an IN_ROW_DATA allocation unit.

NULL for nonleaf levels of an index, and LOB_DATA or ROW_OVERFLOW_DATA allocation units.

NULL for heaps when mode = SAMPLED.

Total number of index or data pages

For an index, the total number of index pages in the current level of the b-tree in the IN_ROW_DATA allocation unit.

For a heap, the total number of data pages in the IN_ROW_DATA allocation unit.

For LOB_DATA or ROW_OVERFLOW_DATA allocation units, total number of pages in the allocation unit.

Average(%) of available data storage space used

Average percentage of available data storage space used in all pages.

For an index, average applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

For a heap, the average of all data pages in the IN_ROW_DATA allocation unit.

For LOB_DATA or ROW_OVERFLOW DATA allocation units, the average of all pages in the allocation unit.

NULL when mode = LIMITED.

Upload Frequency - After every sample.

Alert Text - Average percentage of available data storage space used in all pages for %indexid% is %avg_page_space_used_in_percent%%% where Database ID is %database_id%, Object ID is %object_id% , Partition Number is %partition_number% and Index type Description is %index_type_desc%.It has fallen below warning (%warning_threshold%%%) or critical (%critical_threshold%%%) threshold.

Multiple Thresholds - For this metric you can set different warning and critical threshold values for each unique combiation of "Database ID", "Object ID", "Index ID", "Partition Number", and "Index type Description " objects.

If warning or critical threshold values are currently set for any unique combination of "Database ID", "Object ID", "Index ID", "Partition Number", and "Index type Description" objects, those thresholds can be viewed on the Metric Detail page for this metric.

To specify or change warning or critical threshold values for each unique combination of "Database ID", " Object ID ", " Index ID ", "Partition Number", and "Index type Description" objects, use the Edit Thresholds page.

Total number of records

Total number of records. For an index, total number of records applies to the current level of the b-tree in the IN_ROW_DATA allocation unit. For a heap, the total number of records in the IN_ROW_DATA allocation unit.

Note: For a heap, the number of records returned from this function might not match the number of rows that are returned by running a SELECT COUNT(*) against the heap. This is because a row may contain multiple records. For example, under some update situations, a single heap row may have a forwarding record and a forwarded record as a result of the update operation. Also, most large LOB rows are split into multiple records in LOB_DATA storage.

For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the total number of records in the complete allocation unit. NULL when mode = LIMITED.

Number of ghost records ready for removal

Number of ghost records ready for removal by the ghost cleanup task in the allocation unit.

Zero for nonleaf levels of an index in the IN_ROW_DATA allocation unit.

NULL when mode = LIMITED.

Number of ghost records retained in an allocation unit

Number of ghost records retained by an outstanding snapshot isolation transaction in an allocation unit.

Zero for nonleaf levels of an index in the IN_ROW_DATA allocation unit.

NULL when mode = LIMITED.

Minimum record size in bytes

For an index, minimum record size applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

For a heap, the minimum record size in the IN_ROW_DATA allocation unit.

For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the minimum record size in the complete allocation unit.

NULL when mode = LIMITED.

Maximum record size in bytes

For an index, the maximum record size applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

For a heap, the maximum record size in the IN_ROW_DATA allocation unit.

For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the maximum record size in the complete allocation unit.

NULL when mode = LIMITED.

Average record size in bytes

For an index, the average record size applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

For a heap, the average record size in the IN_ROW_DATA allocation unit.

For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the average record size in the complete allocation unit.

NULL when mode = LIMITED.

Number of records in a heap that have forward pointers

Number of records in a heap that have forward pointers to another data location. (This state occurs during an update, when there is not enough room to store the new row in the original location.)

NULL for any allocation unit other than the IN_ROW_DATA allocation units for a heap.

NULL for heaps when mode = LIMITED.

Database Name

The name of the database.

Object Name

The name of the object.

Index Name

The name of the index.