Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Posts
    4

    Unanswered: 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!

    David

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  3. #3
    Join Date
    May 2003
    Posts
    4
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •