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:
"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
0.0
(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 !!
Steve