used_pages

Reports the number of pages used by a table, an index, or a specific partition. Unlike data_pages, used_pages does include pages used for internal structures. This function replaces the used_pgs function used in versions of SAP ASE earlier than 15.0.

Syntax

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

Parameters

dbid – is the database id where target object resides.
object_id – is the object ID of the table for which you want to see the used pages. To see the pages used by an index, specify the object ID of the table to which the index belongs.
indid – is the index id of interest.
ptnid – is the partition id of interest.

Examples

Example 1 – Returns the number of pages used by the object with a object ID of 31000114 in the specified database (including any indexes):
```
select used_pages(5, 31000114)
```
Example 2 – Returns the number of pages used by the object in the data layer, regardless of whether or not a clustered index exists:
```
select used_pages(5, 31000114, 0)
```
Example 3 – Returns the number of pages used by the object in the index layer for an index with index ID 2. This does not include the pages used by the data layer (See the first bullet in the Usage section for an exception):
```
select used_pages(5, 31000114, 2)
```
Example 4 – Returns the number of pages used by the object in the data layer of the specific partition, which in this case is 2323242432:
```
select used_pages(5, 31000114, 0, 2323242432)
```

Usage

In an all-pages locked table with a clustered index, the value of the last parameter determines which pages used are returned:
- used_pages(dbid, objid, 0) – which explicitly passes 0 as the index ID, returns only the pages used by the data layer.
- used_pages(dbid, objid, 1) – returns the pages used by the index layer as well as the pages used by the data layer.
To obtain the index layer used pages for an all-pages locked table with a clustered index, subtract used_pages(dbid, objid, 0) from used_pages(dbid, objid, 1).
Instead of consuming resources, used_pages discards the descriptor for an object that is not already in the cache.
In in an all-pages-locked table with a clustered index, used_pages is passed only the used pages in the data layer, for a value of indid = 0. When indid=1 is passed, the used pages at the data layer and at the clustered index layer are returned, as in previous versions.
used_pages is similar to the old used_pgs(objid, doampg, ioampg) function.
All erroneous conditions result in a return value of zero.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute used_pgs.