Returns derived statistics for the specified object and index.
derived_stat({object_name | object_id}, {index_name | index_id}, “statistic”)
is the name of the object you are interested in. If you do not specify a fully qualified object name, derived_stat searches the current database.
is an alternative to object_name, and is the object id of the object you are interested in. This must be in the current database
is the name of the index, belonging to the specified object that you are interested in.
is an alternative to index_name, and is the index id of the specified object that you are interested in
the derived statistic to be returned. Available statistics are:
Value |
Returns |
|---|---|
data page cluster ratio or dpcr |
The data page cluster ratio for the object/index pair |
index page cluster ratio or ipcr |
The index page cluster ratio for the object/index pair |
data row cluster ratio or drcr |
The data row cluster ratio for the object/index pair |
large io efficiency or lgio |
The large io efficiency for the object/index pair |
space utilization or sput |
The space utilization for the object/index pair |
Selects the space utilization for the titleidind index of the titles table:
select derived_stat("titles", "titleidind", "space utilization")
Selects the data page cluster ratio for index id
2 of the titles table. Note that you can use
either "dpcr" or "data
page cluster ratio":
select derived_stat("titles", 2, "dpcr")
derived_stat returns a double precision value.
The values returned by derived_stat match the values presented by the optdiag utility.
If the specified object or index does not exist, derived_stat returns NULL.
Specifying an invalid statistic type results in an error message.
ANSI SQL – Compliance level: Transact-SQL extension.
Only the table owner can execute derived_stat.
Documents Performance and Tuning Guide for:
“Access Methods and Query Costing for Single Tables”
“Statistics Tables and Displaying Statistics with optdiag”
