Automatic Maintenance Plan Optimization vs Logical Scan Frag.

Hi!

SQL Server 7.0, SP4

I would like to know if it's normal that, with a Maintenance plan Optimization done every week, I got a Logical Scan Fragmentation of 99.99% for a non-clustered index.

I though that the logical scan should be as lower as it can.

I though that the Optimization plan should drop and recreate all indexes in a database.

My table is heavy (7GB). DOes the clustered is recreated too in this maintenance plan? Is it the reason why my Logical Scan Frag. is so high?

PS: Yes, the database is included in the list of the Maintenance plan.

Thanks a lot!

DavidMake sure your non-clustered index is being created after your clustered index. Recreating a clustered index changes the order of the data, and causes all the non-clustered indexes to be rebuilt as well, and not necessarily efficiently.

99% sounds pretty weird though. This could be an erroneous value.

blindman|||I don't recreate any indexes manually or by any script other than the Optimization option in the Maintenance Plan. BOL doesn't tell me a lot of information on what is done by this option "Reorganize data and index pages" and in which order. Does this use un DBCC CHECKDB or DBREINDEX?

Someone know what is done and what should be the result (at least an idea) on the indexes?

A query doing a index scan of 6GB of data shouldn't tell to the Tuning Wizard that the creation of another index on the specified WHERE clause field should be created to get an Index seek?

All this make me lost... Some ideas?

thanks a lot

Originally posted by blindman
Make sure your non-clustered index is being created after your clustered index. Recreating a clustered index changes the order of the data, and causes all the non-clustered indexes to be rebuilt as well, and not necessarily efficiently.

99% sounds pretty weird though. This could be an erroneous value.

blindman