SQL Server Performance

Logical Scan Fragmentation

Discussion in 'T-SQL Performance Tuning for Developers' started by BigJimSlade, Feb 12, 2003.

  1. BigJimSlade New Member

    I have a very large table (over 30 milion records)
    when I run DBCC Show contig on the table using the fast option
    I show a Logical Scan Fragmentation of 60%

    is what is the best way to improve this?


    Thanks
  2. bradmcgehee New Member

    You need to rebuild the indexes on this table using an appropriate fillfactor. The downside to this is that the table will be locked during this time, so users won't be able to access it. Another option, although not as good, is to run DBCC INDEXDEFRAG, if you are running SQL Server 2000. This command will help to make the indexes more efficient, but it won't be as good as a total index rebuild, but it will allow users to access the table when it is working. Whichever option you choose, you will want to do it during low activity.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  3. BigJimSlade New Member

    I ran DBCC INDEXDEFRAG
    on all of the indexes on the table Except the Cluster Index yet still the Logical Scan Fragmentation is at 60%

    Im trying to defrag the clustered index now....

    any other thoughts[?]
  4. royv New Member

    Do a rebuild of the indexes as brad has suggested. I feel this will resolve your problem.


    "How do you expect to beat me when I am forever?"
  5. bradmcgehee New Member

    Yes, if yo are running DBCC INDEXDEFRAG, and you still have a fragmentation problem, then rebuilding the indexes is your only choice. DBCC INDEXDEFRAG only partially works, as you have seen.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  6. BigJimSlade New Member

    Amazingly enough defraging the clustered index reduced the Logical Scan Fragmentation to 0%

    I not sure if this will have any effect on overall performance

  7. bradmcgehee New Member

    It will help, although it probably won't make a huge difference, but every little bit helps.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  8. royv New Member

    I don't know your situation, but it is of utmost importance that on some regular schedule you rebuild your indexes. I have seen not only fragmentation problems go away, but other problems as well.


    "How do you expect to beat me when I am forever?"
  9. satya Moderator

    And also should have regular DBCC checks and maintenance tasks on the database which helps the good health of database.

    Satya SKJ

Share This Page