Reports the space usage for a table, index, or transaction log and estimates the amount of fragmentation for tables and indexes in a database. The estimates are computed using an average row-length for data and index rows, and the number of rows in a table. You can archive the space usage and fragmentation data for future reporting and trends analysis. sp_spaceusage supports a number of actions, including help, display, archive and report, to indicate the current Adaptive Server space usage.
sp_spaceusage supports a variety of operations.
sp_spaceusage
The “help” action syntax:
sp_spaceusage 'help'[, 'all']
sp_spaceusage 'help' [, {'display' | 'display summary'
| 'report' | 'report summary' | 'archive'}
[, {'table' | 'index' | 'tranlog'}]]
The “display” action syntax:
sp_spaceusage 'display summary [using unit= {KB | MB | GB | PAGES} ]',
{'table' | 'index'}, name
[,where_clause [,order_by[,command ] ] ]
sp_spaceusage 'display [using unit= {KB | MB | GB | PAGES} ]',
{'table' | 'index'}, name
[,select_list
[,where_clause [,order_by[,command] ] ] ]
sp_spaceusage 'display [using unit={KB | MB | GB | PAGES} ]',
'tranlog' [, name[,select_list[,where_clause [,order_by]]]]
The “archive” action syntax:
sp_spaceusage 'archive [ using_clause ]',
{'table' | 'index'}, name[,where_clause[,command] ]
sp_spaceusage 'archive [ using_clause ]', 'tranlog' [,name[,where_clause] ]
The “report” action syntax:
sp_spaceusage 'report summary [ using_clause ]',
{'table' | 'index'}, name
[,where_clause [,order_by[,from_date [,to_date]]]]
sp_spaceusage 'report [ using_clause ]',
{'table' | 'index'}, name
[,select_list[,where_clause [,order_by[,from_date [,to_date]]]]]
sp_spaceusage 'report [ using_clause ]', 'tranlog' [, name [,select_list[,where_clause [,order_by [,from_date [,to_date]]]]]] using_clause = USING using_item [, using_item ...]
using_item = { unit={ KB | MB | GB | PAGES }
| dbname=database_name | prefix=string }
help – displays the entire sp_spaceusage syntax. help action displays the syntax for individual actions supported..
display – displays current space usage information for the specified objects.
display summary – displays a summary of current space usage information for the specified objects.
archive – archives the space usage report to a table. If the archive table does not already exist, sp_spaceusage creates one. New data is appended to existing data. You can specify a prefix for the archive table name and the database in which the archive table resides with the using clause.
report – reports the space usage information for the specified objecs from previously archived data. The output is same as the display action. Include the optional using clause to specify the archive table.
report summary – reports a summary of space usage information for the specified objecs from previously archived data. The output is same as the display summary action. Include the optional using clause to specify the archive table.
using_item – specifies the unit, archive database name, and prefix string for the archive table. You can use a unit size of kilobytes (KB), megabytes (MB), gigabytes (GB), and pages. By default unit size is KB, the current database is the archive database, and no prefix string is assumed.
name – is the name of the entity. Depending on the entity type, you can include multipart names such as owner_name.table_name, or owner_name.table_name.index_name. For the entity type tranlog, the name must be syslogs or NULL. Pattern specifiers are allowed for each part of a multipart name to support reporting on multiple objects in one pass.
select_list – is
the comma-separated list of columns to select in the output columns
for the display and report actions.
Use * to include
all columns in the output. Columns can be renamed using the alias=name notation.
where_clause – is the filter to apply to the result set. Use with the display, report, or archive actions to selectively filter unnecessary data.
order_by – returns query results in the specified columns in sorted order.
command – command run on the entity selected (table, column, or so on) prior to gathering the space usage information for qualifying objects. The following commands are supported: update statistics, update table statistics, and update index statistics.
from_date – specifies beginning of the time range you are interested in.
to_date – specifies end of the time range you are interested in.
Example 1 Displays a brief description, syntax, and usage information for the display action:
sp_spaceusage 'help', 'display'Display the space usage information for an entity in the current database.
Usage:
sp_spaceusage 'display', {'table'|'index'}, <name>
[,<select_list> [,<where_clause> [,<order_by> [,<command>]]]]
sp_spaceusage 'display summary', {'table'|'index'}, <name>
[,<where_clause> [,<order_by> [,<command>]]]
sp_spaceusage 'display', 'tranlog' [,{'syslogs'|NULL}
[,<select_list> [,<where_clause> [,<order_by>]]]]
For more information, use:
sp_spaceusage 'help', 'display', 'table'
sp_spaceusage 'help', 'display', 'index'
sp_spaceusage 'help', 'display', 'tranlog'
Example 2 Displays a summary of the space usage on the titles table:
sp_spaceusage 'display summary', 'table', 'titles'
All the page counts in the result set are in the unit 'KB'. OwnerName TableName Type UsedPages RsvdPages ExpRsvdPages PctBloatRsvdPages ----------- ----------- ------ ----------- ---------- -------------- ---------- dbo titles DATA 6.0 30.0 16.0 87.50 dbo titles INDEX 8.0 64.0 32.0 50.00
Example 3 Displays the space usage information for the titles table:
sp_spaceusage 'display', 'table', 'titles'
All the page counts in the result set are in the unit 'KB'. OwnerName TableName IndId NumRows UsedPages RsvdPages ExtentUtil ExpRsvdPages PctBloatUsePages PctBloatRsvdPages ----------- ----------- ------ ----------- ---------- -------------- ---------- ------------ ---------------- ----------------- dbo titles 0 18.0 6.0 30.0 20.00 16.0 0.0 87.50 dbo titles 1 NULL 4.0 32.0 12.50 16.0 0.00 100.00 dbo titles 2 NULL 4.0 32.0 12.50 16.0 0.00 100.00
Example 4 Displays the space usage information, in megabytes, for all indexes on the titles table whose names start with title:
sp_spaceusage 'display using unit-MB', 'index', 'titles.title%'
All the page counts in the result set are in the unit 'MB'. OwnerName TableName IndId IndexName UsedPages RsvdPages ExtentUtil ExpRsvdPages PctBloatUsedPages PctBloatRsvdPages ---------- -------- ------ ------------ ---------- ----------- ------------ ---------------- ------------------ -------------------- dbo titles 0 titles .005859375 .029296875 20.00 .015625 0.00 87.50 dbo titles 1 titleidind .00390625 .03125 12.50 .015625 0.00 100.00 dbo titles 2 titleind .00390625 .03125 12.50 .015625 0.00 100.00 (1 row affected) (return status = 0)
Example 5 Displays a summary of the space usage for all index names starting with title in the titles table:
sp_spaceusage 'display summary', 'index', 'titles.title%'
All the page counts in the result set are in the unit 'KB'. OwnerName TableName IndexName IndId UsedPages RsvdPages ExpRsvdPages PctBloatRsvdPages-------- ----------- ---------- ------- -------------------- ------------------ -------------- -------------- dbo titles titles 0 6.0 30.0 16.0 46.67 dbo titles titleidind 1 4.0 32.0 16.0 50.00 dbo titles titleind 2 4.0 32.0 16.0 50.00
Example 6 Displays a summary of the space usage for all indexes starting with title in the titles table where the value of PctBloatRsvdPages is less than 50:
sp_spaceusage 'display summary', 'index', 'titles.title%', 'where PctBloatRsvdPages < 50’
All the page counts in the result set are in the unit 'KB'. OwnerName TableName IndexName IndId UsedPages RsvdPages ExpRsvdPages PctBloatRsvdPages -------- ----------- ---------- ------- -------------------- ------------------ -------------- -------------- dbo titles titles 0 6.0 30.0 16.0 46.67
Example 7 Displays a summary of the space usage for all indexes in the titles table in descending order of PctBloatRsvdPages where the value of PctBloatRsvdPages is greater than 30:
1> sp_spaceusage 'display summary', 'index', 'titles.title%', 'where PctBloatRsvdPages > 30', 'order by PctBloatRsvdPages desc'
All the page counts in the result set are in the unit 'KB'. OwnerName TableName IndexName IndId UsedPages RsvdPages ExpRsvdPages PctBloatRsvdPages-------- ----------- ---------- ------- -------------------- ------------------ -------------- -------------- dbo titles titleidind 1 4.0 32.0 16.0 50.00 dbo titles titleind 2 4.0 32.0 16.0 50.00 dbo titles titles 0 6.0 30.0 16.0 46.67
Example 8 Runs update table statistics on the authors table and summarizes its space usage information in the unit pages:
sp_spaceusage 'display summary using unit=pages', 'table', 'authors', null, null, null, 'update table statistics'All the page counts in the result set are in the unit 'pages'. OwnerName TableName Type UsedPages RsvdPages ExpRsvdPages PctBloatRsvdPages --------- --------- ---- --------- --------- ------------ ----------------- dbo authors DATA 2.0 16.0 8.0 100.00 dbo authors INDEX 4.0 32.0 16.0 50.00
Example 9 Displays the space usage information for the transaction log of the current database (pubs2):
sp_spaceusage 'display', 'tranlog'
TableName TotalPages UsedPages CLRPagesFreePages PctUsedPages PctFreePages ------------ ------------ ----------- -----------------------
------------- ------------ syslogs 4096.0 18.0 0.0 1482.0 0.43 36.18
Example 10 Archives the space usage information for the authors table in the currrent database into the default table (spaceusage_object for tables and indexes):
sp_spaceusage 'archive', 'table', 'authors'
Data was successfully archived into table 'pubs2.dbo.spaceusage_object'.
Example 11 Archives the space usage information for the authors table into the default table (spaceusage_object for tables and indexes) in the pubs3 database, :
sp_spaceusage 'archive using dbname = pubs3', 'table', 'authors'
Data was successfully archived into table 'pubs3.dbo.spaceusage_object'.
Example 12 Runs update table statistics on the authors table and archives its space usage information into a table in the current database with the prefix monday_ (for this example, monday_spaceusage_object)
1> sp_spaceusage 'archive using dbname = pubs2, prefix=monday_', 'table','authors', null, 'update table statistics'
Example 13 Archives the space usage information for the transaction log of the current database into the default table (spaceusage_tranlog for transaction logs) in the pubs3 database, :
sp_spaceusage 'archive using dbname=pubs3', 'tranlog'
Data was successfully archived into table 'pubs3.dbo.spaceusage_tranlog'.
Example 14 Reports in detail the last archived space usage information for the authors table from the default table (spaceusage_object for table or index) in the current database:
sp_spaceusage 'report', 'table', 'authors'
All the page counts in the result set are in the unit 'KB'. All the data in the result set are dated 'Jun 15 2007 11:50PM'. OwnerName TableName IndId NumRows UsedPages RsvdPages ExtentUtil ExpRsvdPages PctBloatUsedPages PctBloatRsvdPages --------- --------- ----- ------- ------ --------- ------------- ------------ ----------------- -------------- dbo authors 0 23.0 4.0 32.0 12.50 16.0 0.00 100.00 dbo authors 1 NULL 4.0 32.0 12.50 16.0 0.00 100.00 dbo authors 2 NULL 4.0 32.0 12.50 16.0 0.00 100.00 (1 row affected)(return status = 0)
Example15 Reports in summary the last archived space usage information for the authors table from the default table in the pubs3 database:
sp_spaceusage 'report summary using dbname=pubs3', 'table', 'authors'
All the page counts in the result set are in the unit 'KB'. All the data in the result set are dated 'Jan 17 2007 11:29AM'. OwnerName TableName Type UsedPages RsvdPages ExpRsvdPages PctBloatRsvdPages} --------- --------- ---- ---------- -------- ------------ ------------------ dbo authors DATA 4.0 32.0 16.0 100.00 dbo authors INDEX 8.0 64.0 32.0 50.00
Example 16 Reports a summary from the monday_spaceusage_object table in the current database the last archived space usage information (in megabytes) for the authors table:
sp_spaceusage 'report summary using prefix=monday_, unit=MB', 'table', 'authors'
All the page counts in the result set are in the unit 'MB'. All the data in the result set are dated 'Jan 17 2007 11:29AM'. OwnerName TableName Type UsedPages RsvdPages ExpRsvdPages PctBloatRsvdPages --------- --------- ---- --------- ------- ------------ ----------- dbo authors DATA .00390625 .03125 .015625 100.00 dbo authors INDEX .0078125 .0625 .03125 50.00
Example 17 Reports the space usage information from the default table in the current database for all the indexes on the authors table archived on Jun 9, 2007 or later:
sp_spaceusage 'report', 'index', 'authors.%', null, null, null, 'Jun 9 2007'
All the page counts in the result set are in the unit 'KB'. ArchiveDateTime OwnerName TableName IndId IndexName UsedPages RsvdPages ExtentUtil ExpRsvdPages PctBloatUsedPages PctBloatRsvdPages ------------------- --------- --------- ----- --------- ------- --------- ---------- ------------ ----------------- ----------------- Jun 9 2007 12:06AM dbo authors 0 authors 4.0 32.0 12.50 16.0 0.00 100.00 Jun 10 2007 12:05AM dbo authors 0 authors 4.0 32.0 12.50 16.0 0.00 100.00 Jun 11 2007 11:35PM dbo authors 0 authors 4.0 32.0 12.50 16.0 0.00 100.00 Jun 9 2007 12:06AM dbo authors 1 auidind 4.0 32.0 12.50 16.0 0.00 100.00 Jun 10 2007 12:05AM dbo authors 1 auidind 4.0 32.0 12.50 16.0 0.00 100.00 Jun 11 2007 11:35PM dbo authors 1 auidind 4.0 32.0 12.50 16.0 0.00 100. Jun 9 2007 12:06AM dbo authors 2 aunmind 4.0 32.0 12.50 16.0 0.00 100.00 Jun 10 2007 12:05AM dbo authors 2 aunmind 4.0 32.0 12.50 16.0 0.00 100.00 Jun 11 2007 11:35PM dbo authors 2 aunmind 4.0 32.0 12.50 16.0 0.00 100.00 (1 row affected) (return status = 0)
Example 18 Reports the space usage information for the authors table from the default table in the current database archived between Jun 10 2007 and Jun 15 2007:
sp_spaceusage 'report', 'table', 'authors', null, null, null, 'Jun 10 2007', 'Jun 15 2007'
All the page counts in the result set are in the unit 'KB'. ArchiveDateTime OwnerName TableName IndId NumRows UsedPages RsvdPages ExtentUtil ExpRsvdPages PctBloatUsedPages PctBloatRsvdPages ------------------ -------- --------- ----- ------- --------- --------- ---------- ------------ ----------------- ----------------- Jun 10 2007 12:05AM dbo authors 0 23.0 4.0 32.0 12.50 16.0 0.00 100.00 Jun 11 2007 11:35PM dbo authors 0 23.0 4.0 32.0 12.50 16.0 0.00 100.00 Jun 13 2007 11:46PM dbo authors 0 23.0 4.0 32.0 12.50 16.0 0.00 100.00 Jun 14 2007 11:46PM dbo authors 0 23.0 4.0 32.0 12.50 16.0 0.00 100.00 Jun 14 2007 11:46PM dbo authors 0 23.0 4.0 32.0 12.50 16.0 0.00 100.00 Jun 10 2007 12:05AM dbo authors 1 NULL 4.0 32.0 12.50 16.0 0.00 100.00 Jun 11 2007 11:35PM dbo authors 1 NULL 4.0 32.0 12.50 16.0 0.00 100.00 Jun 13 2007 11:46PM dbo authors 1 NULL 4.0 32.0 12.50 16.0 0.00 100.00 Jun 14 2007 11:46PM dbo authors 1 NULL 4.0 32.0 12.50 16.0 0.00 100.00 Jun 14 2007 11:46PM dbo authors 1 NULL 4.0 32.0 12.50 16.0 0.00 100.00 Jun 10 2007 12:05AM dbo authors 2 NULL 4.0 32.0 12.50 16.0 0.00 100.00 Jun 11 2007 11:35PM dbo authors 2 NULL 4.0 32.0 12.50 16.0 0.00 100.00 Jun 13 2007 11:46PM dbo authors 2 NULL 4.0 32.0 12.50 16.0 0.00 100.00 Jun 14 2007 11:46PM dbo authors 2 NULL 4.0 32.0 12.50 16.0 0.00 100.00 Jun 14 2007 11:46PM dbo authors 2 NULL 4.0 32.0 12.50 16.0 0.00 100.00 (1 row affected) (return status = 0)
sp_spaceusage provides space usage information for tables, indexes, and the transaction log of the current database.
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.Table 20-1 and Table 20-2 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..
Column name |
Description |
|---|---|
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 whose data the index covers |
RowSize |
Number of rows in the partition |
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 |
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 |
Column Name |
Description |
|---|---|
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 |
The PctBloatUsedPages and PctBloatReservedPages columns give an estimate of how many more pages than the minimum the table is using and reserving, respectively. These values indicate how beneficial it may be for you to run reorg rebuild on the table.
ExtentUtil is the ratio of the number of pages that are actually being used against the number of pages that are reserved for the object. Values closer to 100 indicate that most of the pages in the extents reserved for the object are currently used. Table 20-3 gives a synopsis of the measurements.
PctBloatUsedPages value |
PctBloatReservedPages value |
Interpretation |
|---|---|---|
Close to 0, low value |
Close to 0, low value |
Indicates the table is well compacted, and all allocated pages and allocation units are used completely. ExtentUtil should be close to 1.0 |
Close to 0, low value |
Not close to 0, high value |
Indicates the used pages are well compacted, but the table’s extents are under-utilized, and there is a large degree of interpage fragmentation, possibly due to large-scale deletions or empty pages. The unusedpgcnt in systabstats is probably also high. The high value of PctBloatReservedPages suggests that ExtentUtil to is probably much less than 1.0. You can probably resolve most issues by running reorg rebuild. |
Not close to 0, high value |
Close to 0, low value |
Indicates a large degree of intrapage fragmentation, but a smaller degree of inter-page fragmentation. Because the extent utilization is probably high, ExtentUtil value should be close to 1.0. Running reorg compact will probably help resolve these issues. |
Not close to 0, high value |
Not close to 0, high value |
A high value for PctBloatUsedPages indicates a large degree of intrapage fragmentation, where data rows in used pages are not fully compacted (the used pages contain most of the free space). Because interpage and intrapage fragmentation may cause he high value of PctBloatReservedPages, the value of Extent_Util may still be less than 1.0. Running reorg compact and reorg rebuild may resolve these issues. |
The database in which you are archiving the space usage data must have sp_dboption ... select into enabled.
The archive tables are created if they do not already exist at the time of archiving, otherwise the results are appended to the current table. Because of this, any user running sp_spaceusage must have create table permission in the archive database.
While archiving or reporting data, only tables owned by the user running sp_spaceusage are considered for the archive table. Tables with the same name but owned by another user are ignored. By default, the results are archived to or reported from the spaceusage_object table for tables or indexes and spaceusage_tranlog for the transaction log. .
You can use the from_date and to_date arguments only for the report action when reporting from archived data. Adaptive Server uses only the data in the archive table that falls within the specified time-range when generating the report. If you do not include a from_date or a NULL, Adaptive Server uses all archived data prior to the to_date. If you do not include a to_date or NULL, Adaptive Server uses the current date as the value for to_date. If you do not include either the from_date or to_date, Adaptive Server uses the most recent data in the archive table to generate the report.
sp_spaceusage results are estimated based on statistical data. These estimates are only as good the statistics provided. You can run update statistics to improve the accuracy of the results.
Any user can run sp_spaceusage. However, they may not be able to view certain information about tables that they do not have persmissions to view.
