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 16-1 explains the headings in 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.
