The information for the derived_stat built-in function in Reference Manual: Building Blocks is incomplete. Replace the entire reference section for derived_stat with the following:
Returns derived statistics for the specified object and index.
derived_stat("object_name" | object_id, index_name | index_id, ["partition_name" | partition_id,] “statistic”)
object_name – 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.
object_id – is an alternative to object_name, and is the object ID of the object you are interested in. object_id must be in the current database
index_name – is the name of the index, belonging to the specified object that you are interested in.
index_id – is an alternative to index_name, and is the index ID of the specified object that you are interested in.
partition_name – is the name of the partition, belonging to the specific partition that you are interested in. partition_name is optional. When you use partition_name or partition_id, Adaptive Server returns statistics for the target partition, instead of for the entire object.
partition_id – is an alternative to partition_name, and is the partition ID of the specified object that you are interested in. partition_id is optional.
“statistic” – 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 I/O efficiency for the object/index pair |
space utilization or sput |
The space utilization for the object/index pair |
Example 1 Selects the space utilization for the titleidind index of the titles table:
select derived_stat("titles", "titleidind", "space utilization")
Example 2 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")
Example 3 Statistics are reported for the entire object, as neither the partition ID nor name is not specified:
1> select derived_stat(object_id("t1"), 2, "drcr") 2> go
--------------------------- 0.576923
Example 4 Reports the statistic for the partition tl_928003396:
1> select derived_stat(object_id("t1"), 0, "t1_928003306", "drcr") 2> go
--------------------------- 1.000000 (1 row affected)
Example 5 Selects derived statistics for all indexes of a given table, using data from syspartitions:
select convert(varchar(30), name) as name, indid, convert(decimal(5, 3), derived_stat(id, indid, 'sput')) as 'sput', convert(decimal(5, 3), derived_stat(id, indid, 'dpcr')) as 'dpcr', convert(decimal(5, 3), derived_stat(id, indid, 'drcr')) as 'drcr', convert(decimal(5, 3), derived_stat(id, indid, 'lgio')) as 'lgio' from syspartitions where id = object_id('titles') go
name indid sput dpcr drcr lgio ------------------------------ ------ -------- -------- -------- -------- titleidind_2133579608 1 0.895 1.000 1.000 1.000 titleind_2133579608 2 0.000 1.000 0.688 1.000 (2 rows affected)
Example 6 Selects derived statistics for all indexes and partitions of a partitioned table. Here, mymsgs_rr4 is a roundrobin partitioned table that is created with a global index and a local index.
1> select * into mymsgs_rr4 partition by roundrobin 4 lock datarows 2> from master..sysmessages 3> go
(7597 rows affected)
1> create clustered index mymsgs_rr4_clustind on mymsgs_rr4(error, severity) 2> go 1> create index mymsgs_rr4_ncind1 on mymsgs_rr4(severity) 2> go 1> create index mymsgs_rr4_ncind2 on mymsgs_rr4(langid, dlevel) local index 2> go 2> update statistics mymsgs_rr4 1> 2> select convert(varchar(10), object_name(id)) as name, 3> (select convert(varchar(20), i.name) from sysindexes i 4> where i.id = p.id and i.indid = p.indid), 5> convert(varchar(30), name) as ptnname, indid, 6> convert(decimal(5, 3), derived_stat(id, indid, partitionid, 'sput')) as 'sput', 7> convert(decimal(5, 3), derived_stat(id, indid, partitionid, 'dpcr')) as 'dpcr', 8> convert(decimal(5, 3), derived_stat(id, indid, partitionid, 'drcr')) as 'drcr', 9> convert(decimal(5, 3), derived_stat(id, indid, partitionid, 'lgio')) as 'lgio' 10> from syspartitions p 11> where id = object_id('mymsgs_rr4')
name ptnname indid sput dpcr drcr lgio ----------------------------- -------------------------- ----- ---- ----- ---- ---- mymsgs_rr4 mymsgs_rr4 mymsgs_rr4_786098810 0 0.90 1.000 1.00 1.000 mymsgs_rr4 mymsgs_rr4 mymsgs_rr4_802098867 0 0.90 1.000 1.00 1.000 mymsgs_rr4 mymsgs_rr4 mymsgs_rr4_818098924 0 0.89 1.000 1.00 1.000 mymsgs_rr4 mymsgs_rr4 mymsgs_rr4_834098981 0 0.90 1.000 1.00 1.000 mymsgs_rr4 mymsgs_rr4_clustind mymsgs_rr4_clustind_850099038 2 0.83 0.995 1.00 1.000 mymsgs_rr4 mymsgs_rr4_ncind1 mymsgs_rr4_ncind1_882099152 3 0.99 0.445 0.88 1.000 mymsgs_rr4 mymsgs_rr4_ncind2 mymsgs_rr4_ncind2_898099209 4 0.15 1.000 1.00 1.000 mymsgs_rr4 mymsgs_rr4_ncind2 mymsgs_rr4_ncind2_914099266 4 0.88 1.000 1.00 1.000 mymsgs_rr4 mymsgs_rr4_ncind2 mymsgs_rr4_ncind2_930099323 4 0.877 1.000 1.000 1.000 mymsgs_rr4 mymsgs_rr4_ncind2 mymsgs_rr4_ncind2_946099380 4 0.945 0.993 1.000 1.000
Example 7 Select derived statistics for all allpages-locked tables in the current database:
2> select convert(varchar(10), object_name(id)) as name 3> (select convert(varchar(20), i.name) from sysindexes i 4> where i.id = p.id and i.indid = p.indid), 5> convert(varchar(30), name) as ptnname, indid, 6> convert(decimal(5, 3), derived_stat(id, indid, partitionid, 'sput')) as 'sput', 7> convert(decimal(5, 3), derived_stat(id, indid, partitionid, 'dpcr')) as 'dpcr', 8> convert(decimal(5, 3), derived_stat(id, indid, partitionid, 'drcr')) as 'drcr', 9> convert(decimal(5, 3), derived_stat(id, indid, partitionid, 'lgio')) as 'lgio' 10> from syspartitions p 11> where lockscheme(id) = "allpages" 12> and (select o.type from sysobjects o where o.id = p.id) = 'U'
name ptnname indid sput dpcr drcr lgio ----------- ----------------- --------------------------- ----- ---- ---- ---- ---- stores stores stores_18096074 0 0.276 1.000 1.000 1.000 discounts discounts discounts_50096188 0 0.075 1.000 1.000 1.000 au_pix au_pix au_pix_82096302 0 0.000 1.000 1.000 1.000 au_pix tau_pix tau_pix_82096302 255 NULL NULL NULL NULL blurbs blurbs blurbs_114096416 0 0.055 1.000 1.000 1.000 blurbs tblurbs tblurbs_114096416 255 NULL NULL NULL NULL t1apl t1apl t1apl_1497053338 0 0.095 1.000 1.000 1.000 t1apl t1apl t1apl_1513053395 0 0.082 1.000 1.000 1.000 t1apl t1apl t1apl_1529053452 0 0.095 1.000 1.000 1.000 t1apl t1apl_ncind t1apl_ncind_1545053509 2 0.149 0.000 1.000 1.000 t1apl t1apl_ncind_local t1apl_ncind_local_1561053566 3 0.066 0.000 1.000 1.000 t1apl t1apl_ncind_local t1apl_ncind_local_1577053623 3 0.057 0.000 1.000 1.000 t1apl t1apl_ncind_local t1apl_ncind_local_1593053680 3 0.066 0.000 1.000 1.000 authors auidind auidind_1941578924 1 0.966 0.000 1.000 1.000 authors aunmind aunmind_1941578924 2 0.303 0.000 1.000 1.000 publishers pubind pubind_1973579038 1 0.059 0.000 1.000 1.000 roysched roysched roysched_2005579152 0 0.324 1.000 1.000 1.000 roysched titleidind titleidind_2005579152 2 0.777 1.000 0.941 1.000 sales salesind salesind_2037579266 1 0.444 0.000 1.000 1.000 salesdetai salesdetail salesdetail_2069579380 0 0.614 1.000 1.000 1.000 salesdetai titleidind titleidind_2069579380 2 0.518 1.000 0.752 1.000 salesdetai salesdetailind salesdetailind_2069579380 3 0.794 1.000 0.726 1.000 titleautho taind taind_2101579494 1 0.397 0.000 1.000 1.000 titleautho auidind auidind_2101579494 2 0.285 0.000 1.000 1.000 titleautho titleidind titleidind_2101579494 3 0.223 0.000 1.000 1.000 titles titleidind titleidind_2133579608 1 0.895 1.000 1.000 1.000 titles titleind titleind_2133579608 2 0.402 1.000 0.688 1.000 (27 rows affected)
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.
Using the optional partition_name or partition_id reports the requested statistic for the target partition; otherwise, derived_stat reports the statistic for the entire object.
If you provide:
Four arguments – derived_stat uses the third argument as the partition, and returns derived statistics on the fourth argument.
Three arguments – derived_stat assumes you did not specify a partition, and returns derived statistic specified by the third argument.
ANSI SQL – Compliance level: Transact-SQL extension.
Only the table owner can execute derived_stat.
Document Performance and Tuning Guide for:
“Access Methods and Query Costing for Single Tables”
“Statistics Tables and Displaying Statistics with optdiag”