Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Indexes Total Fragmentation

    I have a question, might be silly but here goes

    Lets say your have a clsustered index with a total fragmentation of 63.8157894736842 at this point would it be better to rebuild? I want to see what happens I was doing some reading in the BOL, what I think I will do is copy the database on to the test server and try rebuild and reorganize to see what happens. I figure would be a good learning experience.

    any comments would help

    thanks guys

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Fragmentation affects index scans, it has minimal to no effect on index seeks.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You can rebuild it, but there may be no [humanly] noticeable effect. This can happen if the table is respectably small (I believe under 10,000 pages was the round figure).

    Here is an MS whitepaper that relates to 2000 (Not sure if they have one for 2005). http://www.microsoft.com/technet/pro.../ss2kidbp.mspx

  4. #4
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by rdjabarov
    Fragmentation affects index scans, it has minimal to no effect on index seeks.

    Ok was just wondering because the database is running so slow, so trying to figure out what the problem could be

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by MCrowley
    You can rebuild it, but there may be no [humanly] noticeable effect. This can happen if the table is respectably small (I believe under 10,000 pages was the round figure).

    Here is an MS whitepaper that relates to 2000 (Not sure if they have one for 2005). http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
    Ok McCrowley I'm all set thank you

  6. #6
    Join Date
    Feb 2004
    Posts
    8
    You could use sys.dm_db_index_physical_stats to dynamically rebuild or reorganize your indexes.
    http://msdn.microsoft.com/en-us/library/ms188917.aspx

    Microsoft has some recommendations about when to do rebuild and when to do reorganize.
    http://msdn.microsoft.com/en-us/library/ms189858.aspx

    I have a stored procedure that you can use if you like.
    http://blog.ola.hallengren.com/blog/...1/3440068.html

    Ola Hallengren
    http://ola.hallengren.com

Posting Permissions

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