Index statistics

This sample output is printed for each nonclustered index, a clustered index on a data-only-locked table, and for a clustered index for an all-pages locked table. Information for clustered indexes on allpages-locked tables is reported as part of the table statistics. Table 2-3 describes the output.

Statistics for index:               "title_id_ix" (nonclustered)
Index column list:                   "title_id"
     Leaf count:                      45
     Empty leaf page count:           0
     Data page CR count:              4952.0000000000000000
     Index page CR count:             6.0000000000000000
     Data row CR count:               4989.0000000000000000
     First extent leaf pages:         0
     Leaf row size:                   17.8905999999999992
     Index height:                    1
     

  Derived statistics:
     Data page cluster ratio:         0.0075819672131148
     Index page cluster ratio:        1.0000000000000000
     Data row cluster ratio:           0.0026634382566586

NoteParallel join degree, Unused page count, and OAM page count, which are not included in this sample, appear only for all-pages locked tables with a clustered index.

Table 2-3: Index statistics

Row label

Information provided

Statistics for index

Index name and type.

Index column list

List of columns in the index.

Leaf count

Number of leaf-level pages in the index.

Empty leaf page count

Number of empty leaf pages in the index.

Data page CR count

A counter used to compute the data page cluster ratio for accessing a table using the index.

See “Index-level derived statistics”.

Index page CR count

A counter used to compute the index page cluster ratio.

See “Index-level derived statistics”.

Data row CR count

A counter used to compute the data row cluster ratio

See “Index-level derived statistics”.

First extent leaf pages

The number of leaf pages in the index stored in the first extent in an allocation unit. These pages need to be read using 2K I/O, rather than large I/O.

This information is maintained only for indexes on data-only-locked tables.

Leaf row size

Average size of a leaf-level row in the index. This value is only updated by update statistics, create index, and alter table...lock.

Index height

Height of the index, not counting the leaf level. This row is included in the table-level output only for clustered indexes on allpages-locked tables. For all other indexes, the index height appears in the index-level output.

This value does not apply to heap tables.