reserved_pages

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 SAP ASE versions earlier than 15.0.

Syntax

reserved_pages(dbid, object_id[, indid[, ptnid]])

Parameters

dbid – is the database ID of the database where the target object resides.
object_id – is an object ID for a table.
indid – is the index ID of target index.
ptnid – is the partition ID of target partition.

Examples

Example 1 – 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)
```
Example 2 – 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)
```
Example 3 – 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)
```
Example 4 – 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)
```

Example 5 – 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

Usage

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.
reserved_pages replaces the reserved_pgs function from versions of SAP ASE earlier than 15.0. These are the differences between reserved_pages and reserved_pgs.
- In SAP ASE versions 12.5 and earlier, the SAP ASE server stored OAM pages for the data and index in sysindexes. In SAP ASE 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 affect the sum.

See also update statistics in Reference Manual: Commands.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute reserved_pgs.