Re: Proving or Debunking the need for rebuilding

<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 Received on Fri Nov 17 2006 - 03:56:51 CST