I'm dense on Density

[Steve_CA]

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,
basically,
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:
"Selectivit y 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_U DFGUID_ConGUIDV alueGUID'.

Updated Rows Rows Sampled Steps Density
Average key length

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

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

(1 row(s) affected)

All density Columns

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

4.0000002E-3 UDFGUID

1.2364455E-5 UDFGUID, ConGUIDValueGUI D

1.330032E-7 UDFGUID, ConGUIDValueGUI D, 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 !!

Steve