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

Examples

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.

Related reference
data_pages
reserved_pages
row_count
used_pages