SQL Server Management Studio Standard Reports – Index Physical Statistics: [DatabaseName]

I'm continuing my series on the Standard Reports in SQL Server Management Studio, and today we’re in the database reports. You can find these reports by starting SQL Server Management Studio and right-clicking a database name. From the menu that appears, click the Standard Reports, and then select the title at the top of this post.

This is another useful index report, this time using physical indicators– you can use it to tune your system, plan your space layout and your maintenance. It shows information about the indexes on your tables and their physical layout. If you want more information about indexes in general, check here.

The first band in the report groups the information by the Table Name:

Column	Description
Index Name	The name of the index. System-named indexes usually start with either IX_ or PK_
Index Type	Whether the index is CLUSTERED, which means it is physically arranged in the order defined in the index, or UNCLUSTERED, meaning that the index is not physically arranged by the order defined in the index. You can have one clustered index per table.
# Partitions	The number of partitions the index uses.
Depth	Number of index levels. If this value is NULL then it is an XML index or input is not valid. 1 = Heap, or LOB_DATA or ROW_OVERFLOW_DATA allocation unit.
Operation Recommended	The type of operation recommended on the index.
Partition No.	The partition number on disk where the index is located.
Avg. Fragmentation (%)	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. 0 for LOB_DATA and ROW_OVERFLOW_DATA allocation units, NULL for heaps when mode = SAMPLED.
# Fragments	How many fragments are on this index. See above.
Avg. Pages Per 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.
# Pages	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.