469,167 Members | 1,256 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,167 developers. It's quick & easy.

I'm dense on Density

Hi all,

I've been doing some work trying to tune indexes in a couple of fairly large
tables that are slow for us to query. I've already gotten some
great suggestions from this group and they're much appreciated. One thing I
did to try and gather objective stats is run "DBCC SHOW_STATISTICS".

The question I have is on interpreting the results, particularly when it
comes to "Density". I read the docs in Books Online and it tells me,
that a lower density means higher selectivity for an index, which is good.
The actual description is:

DENSITY - Selectivity of the first index column prefix (non-frequent).
What does "non-frequent" mean and why is it in brackets?

I'm also finding that the Average Key Length is 0 for every index?

And finally, the ALL DENSITY stat - it's described as:
"Selectivity of a set of index column prefixes (frequent)." What's
"(frequent)" mean?
It shows different density stats for different combinations of columns, some
of which aren't in the actual index....so, I'm assuming that it's kind of a
"what if" analysis,
to show you what the density would be if you included different groups of
columns in the index? For a rookie, it's also hard to know the relative
magnitude of the density stats (e.g. is a density of "8" literally 4 times
as bad as a density of "2" ?) Are 2 and 8 both good numbers for density? Is
50 a good number? How high can the number go - know what I mean?

Here's a sample - it's confusing, because the density numbers are all over
the map (or maybe not - dunno):

Statistics for INDEX 'XIKudfvalues_UDFGUID_ConGUIDValueGUID'.

Updated Rows Rows Sampled Steps Density
Average key length

-------------------- ----------- ------------ ----------- ------------------------

Oct 14 2004 9:34PM 7963050 7957900 300 8.4528123E-4

(1 row(s) affected)

All density Columns

------------------------ --------------------------------------------------------------------------------------------------------------------------------

4.0000002E-3 UDFGUID

1.2364455E-5 UDFGUID, ConGUIDValueGUID

1.330032E-7 UDFGUID, ConGUIDValueGUID, ConGUID

(3 row(s) affected)

I don't know from this result if the index is a star performer or in need of
drastic medical attention..any help is very much appreciated !!


Jul 20 '05 #1
0 1175

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

699 posts views Thread by mike420 | last post: by
5 posts views Thread by j_mckitrick | last post: by
3 posts views Thread by max khesin | last post: by
11 posts views Thread by hasadh | last post: by
109 posts views Thread by jmcgill | last post: by
6 posts views Thread by news.demon.co.uk | last post: by
reply views Thread by ric3031urbina | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.