Re: Proving or Debunking the need for rebuilding

Richard Foote wrote:
> <hasta_l3_at_hotmail.com> wrote in message
> news:1163708504.745047.169370_at_h54g2000cwb.googlegroups.com...
> > Richard Foote wrote :
> >
> >> Now my suspicious mind is cast towards this night job that "YES,
> >> definitely yes" improved after index rebuilds.
> >
> > Please do, Richard :-)
> >
> > I didnt intend to be dragged into a long discussion, but
> > this one is becoming very interesting. You are doing
> > quite a bit of the work that I would have to perform
> > anyway sooner or later, and you are doing it much better
> > than I could.
> >
> > Thanks. Will do my best to answer.
> >
>
> Hi Raoul
>
> Sorry, I didn't mean to drag you into anything, I'm just genuinely curious
> as to what might be happening.
>
> Based on what you've said, it *could* be that you have an index (or more)
> that is being deleting heavily from one end (older stuff) but leaving enough
> bits 'n' pieces behind that the index blocks are not being freed for reuse.
> Scans over this portion of the index become expensive as mostly empty index
> blocks are being accessed.
>
> However based on the massive degradation, I'm not sure that's the whole
> picture.
>
> As stated before (and Ben, this is what I was referring to), it would be an
> interesting exercise to capture the execution plans when things start to
> wrong bad (although I don't see how they would change if nothing else
> changes between rebuilds), capture the index statistics, in particular
> blevel, leaf_blocks, avg_leaf_blocks_per_key and avg_data_blocks_per_key and
> to trace portions of the night jobs to see exactly what parts slow down and
> what wait events/cpu activity might be occurring during these periods.
>
> This provide information to help see exactly what is causing the slow down,
> what the expensive waits might be and how the index changes after a rebuild.
> You could then narrow down the indexes of interest from your current 150.
>
> One final point. If my wild and likely incorrect guess were even partially
> correct, I would suspect the less expensive and less impacting coalesce
> rather than a rebuild would be just as effective. Might be worth a try.
>
> If you do gather any more specific information it would be great if you
> could share it so we can drag, ooops sorry I mean further discuss this
> scenario ;)
>
> Cheers
>
> Richard

Thanks for the explanation Richard. Received on Fri Nov 17 2006 - 13:00:21 CST