spaceusage

Syntax

spaceusage(database_id [, object_id [, index_id [, partition_id ] ] ] )

Parameters

database_id – a numeric expression that is the ID for a database. These are stored in the dbid column of sysdatabases.
object_id – a numeric expression that is an object ID for a table object. These are stored in the id column of sysobjects.
index_id – is the index ID of the object you are investigating. Depending on the index_id you use, spaceusage reports:
- index_id = 0 – returns the space metrics for only the data layer of an object, including all its data partitions.
- index_id = 1 – is applicable only for allpages-locked tables with a clustered index and returns the space metrics for only the index layer of the clustered index.
- index_id > 1 – returns the space metrics for the index layer of the corresponding index.
- index_id = 255 – returns the space metrics for off-row, large object page chains.
partition_id – the ID of the partition for which space usage metrics are to be retrieved.

Examples

Example 1 – returns space usage information for the entire database:

select spaceusage()
"reserved pages=1163, used pages=494, data pages=411, index pages=78,
oam pages=83, allocation units=94, row count=50529, tables=33, 
LOB pages=3, syslog pages=8"

Example 2 – returns space metrics for all the indexes on the object specified by object_id, including all partitions, if any, on each index, and the space used by off-row large object page chains:
```
select spaceusage(dbid, objid)
```
Example 3 – returns space metrics for the specified partition for the listed object_id and index_id:
```
select spaceusage(database_id, object_id, index_id)
```
The output from spaceusage run against a database containing numerous user objects is shown below. spaceusage reports the space metrics for the data layer and all the indexes on this table.
```
select spaceusage(db_id(), object_id('syspartitions'))
----------------------------------------------------------------------
reserved pages=2220, used pages=2104, data pages=2100, index pages=1096, oam pages=4, allocation units=373, row count=174522, tables=1, LOB pages=0 
```
In this result, the reserved pages, used pages, and data pages values report the respective page counts for data and index pages. Because index pages reports the page counts for only the index pages of the three indexes on syspartitions, determine the number of data pages for only the data layer of this table by subtracting the value for index pages from the value for the data pages: 2100 - 1096 = 1004 pages.
Confirm the number of data pages for only the data layer of this table by executing spaceusage with a value for the index_id parameter of 0:
```
select spaceusage(db_id(), object_id('syspartitions'), 0)
----------------------------------------------------------------------
reserved pages=1064, used pages=1005, data pages=1004, index pages=0, oam pages=1, allocation units=229, row count=174522, tables=1, LOB pages=0 
```
spaceusage reports a value for data pages (1004), which is consistent with the equation above, and because the query requests space metrics for only the data layer, it returns a value of 0 for the index pages.
Example 4 – returns the aggregate space metrics for all objects, including user and system catalogs, that occupy space in the database:
```
select spaceusage(database_id)
```
However, spaceusage does not report on tables that do not occupy space (for example, fake and proxy tables). Currently, spaceusage also does not report on syslogs.

Usage

Depending on which parameters you include, spaceusage may report on any or all of:

reserved pages – number of pages reserved for an object, which may include index pages if you selected index IDs based on the input parameters.
used pages – number of pages used by the object, which may include index pages if you selected index IDs based on the input parameters.
The value for used pages that spaceusage returns when you specify index_id = 1 (that is, for all-pages clustered indexes) is the used page count for the index layer of the clustered index. However, the value the used_pages function returns when you specify index_id = 1 includes the used page counts for the data and the index layers.
data pages – number of data pages used by the object, which may include index pages if you selected index IDs based on the input parameters.
index pages – number of index-only pages, if the input parameters specified processing indexes on the objects. To determine the number of pages used for only the index-level pages, subtract the number of large object (LOB) pages from the number of index pages.
oam pages – number of OAM pages for all OAM chains, as selected by the input parameters.
For example, if you specify:
```
spaceusage(database_id, object_id, index_id)
```
oam pages indicates the number of OAM pages found for this index and any of its local index partitions. If you run spaceusage against a specific object, oam pages returns the amount of overhead for the extra pages used for this object’s space management.

When you execute spaceusage for an entire database, oam pages returns the total overhead for the number of OAM pages needed to track space across all objects, and their off-row LOB columns.
allocation units – number of allocation units that hold one or more extents for the specified object, index, or partition. allocation units indicates how many allocation units (or pages) Adaptive Server must scan while accessing all the pages of that object, index, or partition.
When you run spaceusage against the entire database, allocation units returns the total number of allocation units reserving space for an object. However, because Adaptive Server can share allocation units across objects, this field might show a number greater than the total number of allocation units in the entire database.
row count – number of rows in the object or partition. spaceusage reports this row count as 0 when you specify the index_id parameter.
tables – total number of tables processed when you execute spaceusage and include only the database_id parameter (that is, when you are investigating space metrics for the entire database).
LOB pages – number of off-row large object pages for which the index ID is 255.
LOB pages returns a nonzero value only when you use spaceusage to determine the space metrics for all indexes, or only the LOB index, on objects that contain off-row LOB data. LOB pages returns 0 when you use spaceusage to examine the space metrics only for tables (which have index IDs of 0).

When you run spaceusage against the entire database, LOB pages displays the aggregate page counts for all LOB columns occupying off-row storage in all objects.