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 Adaptive Server earlier than 15.0.
used_pages(dbid, object_id[, indid[, ptnid]])
is the database id where target object resides.
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.
is the index id of interest.
is the partition id of interest.
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)
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)
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)
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)
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.
ANSI SQL – Compliance level: Transact-SQL extension.
Any user can execute used_pgs.
Functions data_pages, object_id
