Table statistics

This is sample optdiag output for table statistics:

Table owner:                        "dbo"
Table name:                         "authors" 

Statistics for table:               "authors"

     Partition count:               3

Statistics for partition:           "authors_1376004902"
     Data page count:               74
     Empty data page count:         0
     Data row count:                1666.0000000000000000
     Forwarded row count:           0.0000000000000000
     Deleted row count:             0.0000000000000000
     Data page CR count:            10.0000000000000000
     OAM + allocation page count:   3
     First extent data pages:       0
     Data row size:                 85.2623049219687914
     Parallel join degree:          0.0000000000000000
     Unused page count:             5
     OAM page count:                1

  Derived statistics:
     Data page cluster ratio:       1.0000000000000000
     Space utilization:             0.9521597490347491
     Large I/O efficiency:          1.0000000000000000

Table 2-2: Table statistics

Row label

Information provided

Table owner

Name of the table owner. You can omit owner names on the command line by specifying dbname..tablename. If multiple tables have the same name and different owners, optdiag prints information for each table with that name.

Table name

Name of the table.

Statistics for table

Name of the table for which statistics are printed.

Partition count

Number of partitions.

Statistics for partition

Name of the partition for which the statistics are shown.

Data page count

Number of data pages in the table.

Empty data page count

Count of pages that have only deleted rows.

Data row count

Number of data rows in the table.

Forwarded row count

Number of forwarded rows in the table. This value is always 0 for an allpages-locked table.

Deleted row count

Number of rows that have been deleted from the table. These are committed deletes where the space has not been reclaimed by one of the functions that clears deleted rows.

This value is always 0 for an allpages-locked table.

Data page CR count

A counter used to derive the data page cluster ratio. Computes the data page cluster ratio, which can help determine the effectiveness of large I/O for table scans and range scans. This value is only updated when you run update statistics

OAM + allocation page count

Number of OAM pages for the table, plus the number of allocation units in which the table occupies space. These statistics are used to estimate the cost of OAM scans on data-only-locked tables.

The value is maintained only on data-only-locked tables.

First extent data pages

Number of pages that share the first extent in an allocation unit with the allocation page. These pages need to be read using 2K I/O, rather than large I/O.

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

Data row size

Average length of a data row, in bytes. The size includes row overhead.

This value is updated only by update statistics, create index, and alter table...lock.

Parallel join degree

An integer value that indicates the degree of parallelism used for a nested-loop join.

Unused page count

Number of unused pages in the extent.

OAM page count

Number of OAM pages.

Derived statistics

Group of statistics for which optdiag derives information.

Data page cluster ratio

See “Data page cluster ratio”, below.

Space utilization

See “Space utilization” below.

Large I/O efficiency

See “Large I/O efficiency” below.