systabstats

All databases

Description

systabstats contains one row for each clustered index, one row for each nonclustered index, and one row for each table that has no clustered index. Uses datarow locking.

Columns

The columns for systabstats are:

Name

Datatype

Description

indid

smallint

0 if a table; 1 if a clustered index on an allpages-locked table; >1 if a nonclustered index or a clustered index on a data-only-locked table; statistics on text or image objects (255) are not maintained in systabstats.

id

int

ID of table to which index belongs

activestatid

smallint

Reserved

indexheight

smallint

Height of the index; maintained if indid is greater than 1

leafcnt

int

Number of leaf pages in the index; maintained if indid is greater than 1

pagecnt

int

Number of pages in the table or index

rowcnt

float

Number of rows in the table; maintained for indid of 0 or 1

forwrowcnt

float

Number of forwarded rows; maintained for indid of 0 or 1

delrowcnt

float

Number of deleted rows

dpagecrcnt

float

Number of extent I/Os that need to be performed to read the entire table

ipagecrcnt

float

Number of extent I/Os that need to be performed to read the entire leaf level of a nonclustered index

drowcrcnt

float

Number of page I/Os that need to be performed to read an entire table

oamapgcnt

int

Number of OAM pages for the table, plus the number of allocation pages that store information about the table

extent0pgcnt

int

Count of pages that are on the same extent as the allocation page

datarowsize

float

Average size of the data row

leafrowsize

float

Average size of a leaf row for nonclustered indexes and clustered indexes data-only-locked tables

status

int

Internal system status information (see Table 12-20)

spare1

int

Reserved

spare2

float

Reserved

rslastoam

int

Last OAM page visited by a reorg reclaim_space or reorg compact command

rslastpage

int

Last data or leaf page visited by a reorg reclaim_space or reorg compact command

frlastoam

int

Last OAM page visited by the reorg forwarded_rows command

frlastpage

int

Last data page visited by the reorg forwarded_rows command

conopt_thld

smallint

Concurrency optimization threshold

spare3

smallint

Reserved

emptypgcnt

int

Number of empty pages in extents allocated to the table or index

spare4

float

Reserved

Table 12-20 lists the bit representations for the status column:

Table 12-20: status bits in the systabstats table

Decimal

Hex

Status

1

0x1

Statistics are the result of upgrade (not update statistics)

Indexes

Unique clustered index On id, indid