Output Columns from sp_spaceusage

The set of columns that appear in the sp_spaceusage output depend on the action and entity type.

By default, only a standard set of columns are displayed. However, you can include others with the select_list parameter, and you can view them all with the * wildcard in the select list.The following two lists provide the set of all output column names and their description for the entity types table, index and tranlog, respectively. Column names in the select_list, where_clause, orderby_clause parameters must belong to the set listed in these tables.

Output columns for table or index entity types are:
  • ArchiveDateTime – Timestamp of the data
  • ServerName – Server name
  • MaxPageSize – Logical page size, in @@maxpagesize
  • DBName – Object’s database name
  • OwnerName – Object’s owner name
  • TableName – Table name
  • Id – ID of the table
  • IndId – ID of the index
  • IndexName – Index name
  • PtnId – ID of the partition
  • PtnName – Partition name
  • DataPtnID – ID of the data partition with data that the index covers
  • RowSize –

    • For tables – Average size of the data row.

    • For indexes – Average size of a leaf row for nonclustered and clustered indexes (data-only-locked tables)

  • RowCount_ts – Number of rows in the partition as per the systabstats table
  • NumFwdRows – Number of forwarded rows in the partition
  • NumDelRows – Number of deleted rows in the partition
  • NumlRows – Number of rows in the partition
  • PctFwdRows – Percentage of rows that were forwarded in the partition
  • NonLeafRowSize – Average non-leaf row size in the partition
  • FF – Fill factor in the partition
  • MRPP – Maximum number of rows per page in the partition
  • ERS – Expected row size in the partition
  • RPG – Reserve page gap in the partition
  • IndexHeight – Height of the index tree in the partition
  • OAMAPageCount – Number of OAM and AP pages (in pages)
  • Extent0PageCount – Number of pages in the extent 0 (in pages)
  • Status – status from sysindexes table
  • Sysstat – sysstat from sysobjects table
  • Sysstat2 – sysstat2 from sysobjects table
  • LockScheme – Lock scheme of the table
  • NumVarCols – Number of variable columns the table has
  • HasAPLCI – Indicates whether the table has an APL CI
  • SpUtil – Space utilization derived statistic for the partition
  • DPCR – Data page cluster ratio derived statistic for the partition
  • DRCR – Data row cluster ratio derived statistic for the partition
  • IPCR – Index page cluster ratio derived statistic for the partition
  • LGIO – Large IO efficiency derived statistic for the partition
  • ExtentUtil – Extent utilization for the partition
  • EmptyPages – Number of empty pages in the partition (in units)
  • DataPages – Number of data pages in the partition (in units)
  • UsedPages – Number of used pages in the partition (in units)
  • RsvdPages – Number of pages reserved in the partition (in units)
  • LeafPages – Number of leaf pages in the partition (in units)
  • ExpDataPages – Expected number of data pages in the partition had the data been compact (in units)
  • ExpUsedPages – Expected number of used pages in the partition had the data been compact (in units)
  • ExpRsvdPages – Expected number of reserved pages in the partition had the data been compact (in units)
  • ExpLeafPages – Expected height of the index tree in the partition had the data been compact
  • PctBloatUsedPages – Percentage bloat in the used pages in the partition
  • PctBloatRsvdPages – Percentage bloat in the reserved pages in the partition
  • PctBloatLeafPages – Percentage bloat in the leaf pages in the partition
  • PctEmptyPages – Percentage of data pages that were empty in the partition
The parameters available for the tranlog entity type are:
ColumnDescription
ArchiveDateTime

The timestamp of the data

ServerName

The server name

MaxPageSize

The logical page size set in @@maxpagesize

DBName

The object’s database name

OwnerName

The object’s owner name

TableName

The name of the transaction log, for example, syslogs

Id

ID of the syslogs table

IsMLD

Is it “mixed log and data” transaction log?

IsLogFull

Is the transaction log full?

LCTPages

The “last chance threshold” value of the log (in units)

TotalPages

Total number of log pages (in units)

UsedPages

The number of log pages already used (in units)

CLRPages

The number of log pages reserved for rollbacks (in units)

FreePages

The number of log pages that has not been used yet (in units)

PctUsedPages

The percentage of log pages that are in use already

PctFreePages

The percentage of log pages that are free

Parent topic: sp_spaceusage