Reports the number of pages reserved for a database, object, or index. The result includes pages used for internal structures.
This function replaces the reserved_pgs function used in Adaptive Server versions earlier than 15.0.
reserved_pages(dbid, object_id[, indid[, ptnid]])
is the database ID of the database where the target object resides.
is an object ID for a table.
is the index ID of target index.
is the partition ID of target partition.
Returns the number of pages reserved by the object with a object ID of 31000114 in the specified database (including any indexes):
select reserved_pages(5, 31000114)
Returns the number of pages reserved by the object in the data layer, regardless of whether or not a clustered index exists:
select reserved_pages(5, 31000114, 0)
Returns the number of pages reserved by the object in the index layer for a clustered index. This does not include the pages used by the data layer:
select reserved_pages(5, 31000114, 1)
Returns the number of pages reserved by the object in the data layer of the specific partition, which in this case is 2323242432:
select reserved_pages(5, 31000114, 0, 2323242432)
Use one of the following three methods to calculate space in a database with reserved_pages:
Use case expressions to select a value appropriate for the index you are inspecting, selecting all non-log indexes in sysindexes for this database. In this query:
The data has a value of “index 0”,
and is available when you include the statements when
sysindexes.indid = 0 or sysindexes.indid = 1.
indid values greater than 1 for are indexes. Because this query does not sum the data space into the index count, it does not include a page count for indid of 0.
Each object has an index entry for index of 0 or 1, never both.
This query counts index 0 exactly once per table.
select 'data rsvd' = sum( case when indid > 1 then 0 else reserved_pages(db_id(), id, 0) end ), 'index rsvd' = sum( case when indid = 0 then 0 else reserved_pages(db_id(), id, indid) end ) from sysindexes where id != 8
data rsvd index rsvd ---------- ----------- 812 1044
Query sysindexes multiple times to display results after all queries are complete:
declare @data int, @dbsize int, @dataused int, @indices int, @indused int select @data = sum( reserved_pages(db_id(), id, 0) ), @dataused = sum( used_pages(db_id(), id, 0) ) from sysindexes where id != 8 and indid <= 1 select @indices = sum( reserved_pages(db_id(), id, indid) ), @indused = sum( used_pages(db_id(), id, indid) ) from sysindexes where id != 8 and indid > 0 select @dbsize as 'db size', @data as 'data rsvd'
db size data rsvd ----------- ----------- NULL 820
Query sysobjects for data space information and sysindexes for index information. From sysobjects, select table objects: [S]ystem or [U]ser:
declare @data int,
@dbsize int,
@dataused int,
@indices int,
@indused int
select @data = sum( reserved_pages(db_id(), id, 0) ),
@dataused = sum( used_pages(db_id(), id, 0) )
from sysobjects
where id != 8
and type in ('S', 'U')
select @indices = sum( reserved_pages(db_id(), id, indid) ),
@indused = sum( used_pages(db_id(), id, indid) )
from sysindexes
where id != 8
and indid > 0
select @dbsize as 'db size',
@data as 'data rsvd',
@dataused as 'data used',
@indices as 'index rsvd',
@indused as 'index used'
db size data rsvd data used index rsvd index used --------- ----------- ----------- ----------- ---------- NULL 812 499 1044 381
If a clustered index exists on an all-pages locked table, passing an index ID of 0 reports the reserved data pages, and passing an index ID of 1 reports the reserved index pages. All erroneous conditions result in a value of zero being returned.
reserved_pages counts whatever you specify; if you supply a valid database, object, index (data is “index 0” for every table), it returns the reserved space for this database, object, or index. However, it can also count a database, object, or index multiple times. If you have it count the data space for every index in a table with multiple indexes, you get it counts the data space once for every index. If you sum these results, you get the number of indexes multiplied by the total data space, not the total number of data pages in the object.
Instead of consuming resources, reserved_pages discards the descriptor for an object that is not already in the cache.
For Adaptive Server version 15.0 and later, reserved_pages replaces the reserved_pgs function. These are the differences between reserved_pages and reserved_pgs.
In Adaptive Server versions 12.5 and earlier, Adaptive Server stored OAM pages for the data and index in sysindexes. In Adaptive Server versions 15.0 and later, this information is stored per-partition in sysparitions. Because this information is stored differently, reserved_pages and reserved_pgs require different parameters and have different result sets.
reserved_pgs required a page ID. If you supplied a value that did not have a matching sysindexes row, the supplied page ID was 0 (for example, the data OAM page of a nonclustered index row). Because 0 was never a valid OAM page, if you supplied a page ID of 0, reserved_pgs returned 0; because the input value is invalid, reserved_pgs could not count anything.
However, reserved_pages requires an index ID, and 0 is a valid index ID (for example, data is “index 0” for every table). Because reserved_pages can not tell from the context that you do not require it to recount the data space for any index row except indid 0 or 1, it counts the data space every time you pass 0 as an index ID. Because reserved_pages counts this data space once per row, its yields a sum many times the true value.
These differences are described as:
reserved_pgs does not affect the sum if you supply 0 as a value for the page ID for the OAM page input; it just returns a value of 0.
If you supply reserved_pages with a value of 0 as the index ID, it counts the data space. Issue reserved_pages only when you want to count the data or you will affect the sum.
ANSI SQL – Compliance level: Transact-SQL extension.
Any user can execute reserved_pgs.
Command update statistics
Function data_pages, reserved_pages, row_count, used_pages
