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

Examples

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.

Related reference
data_pages
object_id