derived_stat

Description

Returns derived statistics for the specified object and index.

Syntax

derived_stat("object_name" | object_id,
	index_name | index_id,
	["partition_name" | partition_id,]
	“statistic”)

Parameters

Examples

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
2> 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

Selects 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)

Usage

• 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.

• Instead of consuming resources, derived_stat discards the descriptor for an object that is not already in the cache.

• 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 specifiy a partition, and returns derived statistic specified by the third argument.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

The permission checks for derived_stat differ based on your granular permissions settings.

See also

• “Access Methods and Query Costing for Single Tables”

• “Statistics Tables and Displaying Statistics with optdiag”