At 12:47 PM 8/12/2003 +0200, you wrote:

>Hi all,
>
>How do I know if the following indexes have too many duplicates? In other
>words, when is a chain of duplicates "too long", and when should I consider
>dropping the index?

MAX DUP gives you the number of nodes (indexes for rows) in the longest
duplicate chain.
TOTAL DUP is the number of nodes that are duplicates. If you subtract Max
Dup from the number of nodes, the number you have left are the values that
are distributed in other chains. In all but two cases, the numbers are
similar for all of the indexes - one chain that's holding about 18% - which
might be OK, say, if most of the rest are well-scattered. This probably
isn't too bad on such a small table.

>And what is meant by "fill distribution"?

It breaks down the distribution of nodes on index pages. If you add all
the numbers up, you'll have the number of pages that the index occupies
(="leaf buckets"). If most of the pages are in the high percentiles, i.e.
well-filled, that's good, because it means there's not a lot of dead
space. It's time consuming for the engine to have to visit a lot of pages
that are sparsely filled. The more nodes it can find on a single page, the
faster.

The fill distributions aren't too good on some of those indexes and, if you
look at the depths, you're getting quite a lot of indirection for such a
small table. You might like to consider rebuilding the indexes and taking
a look at the stats afterwards to see if there's any dramatic change...

Indirection starts a step out from index root page, which has pointers to a
page for each index where pointers to index pages are stored. If that page
has nodes on it, then you have one level of indirection. If it has only
pointers to more pages, where there are actually nodes, then you have two
levels. If there are no nodes on those pages, but only pointers to more
pages where there are nodes, then you are at level 3 (where you are). Did
I explain that well...hmmm, maybe not...

/heLen