Using sp_spaceused to display object size

The system procedure sp_spaceused reads values stored on an object’s OAM page to provide a quick report on the space used by the object.

sp_spaceused titles
name         rowtotal reserved   data      index_size  unused   
------------ -------- ---------- --------- ----------- --------
titles       5000        1756 KB   1242 KB     440 KB     74 KB

The rowtotal value may be inaccurate at times; not all Adaptive Server processes update this value on the OAM page. The commands update statistics, dbcc checktable, and dbcc checkdb correct the rowtotal value on the OAM page. Table 11-1 explains the headings in sp_spaceused output.

Table 11-1: sp_spaceused output

Column

Meaning

rowtotal

Reports an estimate of the number of rows. The value is read from the OAM page. Though not always exact, this estimate is much quicker and leads to less contention than select count(*).

reserved

Reports pages reserved for use by the table and its indexes. It includes both the used and unused pages in extents allocated to the objects. It is the sum of data, index_size, and unused.

data

Reports the kilobytes on pages used by the table.

index_size

Reports the total kilobytes on pages used by the indexes.

unused

Reports the kilobytes of unused pages in extents allocated to the object, including the unused pages for the object’s indexes.

To report index sizes separately, use:

sp_spaceused titles, 1
 index_name           size       reserved   unused 
 -------------------- ---------- ---------- ----------
 title_id_cix         14 KB      1294 KB    38 KB
 title_ix             256 KB     272 KB     16 KB
 type_price_ix        170 KB     190 KB     20 KB

name         rowtotal reserved   data      index_size  unused   
------------ -------- ---------- --------- ----------- --------
titles       5000        1756 KB   1242 KB     440 KB     74 KB

For clustered indexes on allpages-locked tables, the size value represents the space used for the root and intermediate index pages. The reserved value includes the index size and the reserved and used data pages.

The “1” in the sp_spaceused syntax indicates that detailed index information should be printed. It has no relation to index IDs or other information.