Dynamic Management Views and Dynamic Management Functions - Microsoft SQL Server 2012 Performance Tuning - page 157

Column name Description

index_id This column represents the ID of the index.

partition_number This column represents the partition number of a

table, view, or index.

index_type_desc This column represents the type of the index.

avg_fragmentation_in_percent This column represents the percentage of the

logical fragmentation for indexes or the extent
fragmentation for HEAP in IN_ROW_DATA
allocation unit.

This column represents the number of fragments
in leaf-level pages belonging to the IN_ROW_DATA
allocation unit.

fragment_count

avg_fragment_size_in_pages This column represents the average number of

pages in a fragment belonging to the IN_ROW_
DATA allocation unit.

page_count This column represents the total number of pages in

an index.

This column represents the percentage of average
space used by all pages. This column is very
important as it tells you how much of your pages are
filled, on average.

avg_page_space_used_in_
percent

record_count This column represents total number of records in

an index or HEAP .

There are two more index-related dynamic management functions that provide useful index-
related statistics that you would like to explore. These views are as follows:

F sys.dm_db_index_operational_stats (DMF)
F sys.dm_db_missing_index_columns (DMF)