Databases Reference
In-Depth Information
with
sp_helpstats
and detailed information on a particular set of statistics can be viewed using DBCC
SHOW_STATISTICS. For example, if you create this index:
CREATE NONCLUSTERED INDEX idx_name
ON lastnames(name)
And run
sp_helpstats
on the lastnames table:
sp_helpstats 'lastnames', 'ALL'
go
statistics_name statistics_keys
------------------- ---------------
idx_name
name
If you've been running all the code examples so far in this chapter you'll also see some statistics
on the ID column, which are prefixed with
_WA_Sys_
. These are auto-generated statistics from running
the
usp_looppeopleinsert
stored procedure. The different ways statistics are created are discussed
in the next section.
You can see that there are statistics on the name column, which you can then have a look at in more detail
using DBCC SHOW_STATISTICS:
DBCC SHOW_STATISTICS ('lastnames','idx_name')
go
Name Updated Rows Rows Sampled
---------------- -------------------- ------ -------------
idx_lastname
Jul 31 2007 12:05AM 2000
2000
Steps Density Average key length String Index
------ -------- ------------------ ------------
192 1 44.231 YES
All density Average Length Columns
------------- -------------- --------
0.0005
44.231
name
RANGE_HI_KEY
------------------------------------------------------
ABQPXHSRBWEOXYQSNBBJESAXRUXFDCIXKICFVXKXPYVJXLTVMLHD
AFRQGVFFXBOGXGYVUCGCJDETEQWTEAWLY
AKFVETQFWEGANVGMAIJQJIKEBCFHSFMFANVFMBAGJUCLSADQQWQEBE
ASYBJUDGKJJUBUBXWYMLPBAJOEWVDPWLKHBRHRJABAWVLVECTPH
AWGEMFCXBSINYRUUSTUSITXMWNTWURALDNALYYKWF
BAGCHFEOOVJTTJMSMOSOEAIAC
BCEIDGWQSCVWVTVCLJBIK
RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
------------- ------------- -------------------- --------------
6
1
6
1
7
1
7
1
11
1
11
1
11
1
11
1
7
1
7
1
14
1
14
1
7
1
7
1
Search WWH ::
Custom Search