Re: Episode 2: the EntMgr Green Light!!

Hi (again) Domenic,

Comments embedded.

"Domenic G." <domenicg_at_hotmail.com> wrote in message news:c7e08a19.0311081901.60899479_at_posting.google.com...
> Guys,
>
> This is a bunch of BS. If your system is read-only most of the time,
> DSS, OLAP, then rebuilding your indexes after a huge load of data
> increases read performance -- don't tell me it doesn't. Smaller
> index, less I/O.

It doesn't. Or rather as I mentioned previously it doesn't in the majority of cases.

Look at the simple example in my previous post, unless you describe a 0.2% improvement on a "poorly" fragmented index as a significant increase in read performance ...

The "costs" associated with rebuilding an index vs. the "benefits" afterwards *in most cases* makes the rebuild an unjustified operation.

This is the first time you mentioned your system being read only with changes only after huge data loads. Interesting.

What happened to your deletes, remember you're checking for 20% deleted entries ?
What happened to your sequence based indexes ? What happened to checking for too many levels in an index ?

None of these are symptoms of a read only database.

Also, if you're doing huge bulk inserts, consideration should be given for invalidating your indexes and rebuilding afterwards. Not so much for subsequent index performance but for the performance of the bulk loads themselves.

>
> Second, if the index starts growing again too quickly, the pctfree
> setting used on the rebuild was too low. The rebuild is not the
> culprit, you didn't use the correct setting. The base of the index is
> much better after a rebuild -- go write a b tree routine and you'll
> see for yourself.

Need to be careful here. If you're rebuilding to make your indexes smaller, then you need to packing the buggers by definition (else why bother). If you're now arguing "wasted" space is good, then what criteria do you use. You admit to no metrics so what pctfree value do you use. How did you determine such a value.

What do you define as a "much better" base of an index. Does a (say) 0.2% performance improvement at best justify having such a better base.

>
> Geez, you guys make a mountain out of a mole hill. Thank God I'm the
> only DBA in my shop. Next thing you'll say it's not a good idea to
> index FKs.

Your lucky. Your the only DBA in your shop, you have plenty of free time to get busy in and you have the luxury of a quiet time in which to waste resources.

Many sites have DBAs that don't have much free time on their hands and more importantly don't have the luxury of a quiet time in which to waste resources. Validating structure and rebuilding of indexes and the vast resources they consume and the availability and performance issues they create means that many sites need to *justify* such operations. If there are no measurable performance improvements, then why the hell do we subject our users and our batch operations to these considerable inconveniences.

The key, the secret is to know and pick the right indexes. And as I've suggested, you're criteria is not accurate enough.

If someone, anyone, reads this thread and at least *thinks* first before rebuilding an index, at least *considers* what am I actually achieving by doing all this and at least takes some *metrics* before/after a rebuild to see whether such an operation was worthwhile, then I thank you Domenic for helping to make someone out there see the light.

Domenic, it's time for you to see the light also ;)

Cheers

Richard Received on Sun Nov 09 2003 - 07:18:20 CST