Chapter 1: System Tables

systabstats

All databases

Description

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

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 systabstats does not maintain statistics on text or image objects (255)
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 1-23)
plljoindegree	int	The degree of parallelism used for a nested loop join operation, plljoindegree is the parallel scan degree of the table (whose systabstats has this field) that is the inner table in a nested loop join.
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
plldegree	int16	Maximum degree of parallelism possible on table or index for data manipulation languages (DMLs). A value of 0 (zero) indicates a nonexistent maximum; the query processor configures maximum degree of parallelism.
emptypgcnt	int	Number of empty pages in extents allocated to the table or index
spare4	float	Reserved
partitionid	int	Partition ID
spare5	int	Spare field for alignment
statmoddate	datetime	Last time the row was flushed to disk
unusedpgcnt	int	Number of unused pages
oampagecnt	int	Number of allocation pages listed in the object allocation map

Table 1-23 lists the bit representations for the status column:

**Table 1-23: 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, partitionid

View this book as PDF