By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,573 Members | 903 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,573 IT Pros & Developers. It's quick & easy.

I'm dense on Density

P: n/a
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


Jul 20 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.