Post reply

When to defrag

  • July 1, 2003 at 12:40 pm

    #106182

    How large (cardinality) should a table be before I check for fragmentation? Would a rule of thumb be "Check only tables greater that 50K rows"? Or > 500K rows?

  • July 1, 2003 at 10:05 pm

    #463863

    Hi there

    As a matter of course in SQL Server, you should be running dbreindex or indexdefrag on a weekly basis. I have an article coming soon showing the movement of pages after these routines are run. As for table sizes, run dbcc showcontig and have a careful looksie re fragmentation. I tend to ignore anything less that 1000 to 5000 rows (dending on avg row size), you will find that no matter the setting showcontig may remove terrible extent fragmentation due to mixed extent alloc, and in reality,the physical pages are in extents that are side-by-side. Id be more concerned about SQL at a higher level, and run the defrag commands on a regular basis.

    Cheers

    Ck

    Chris Kempster

    http://www.chriskempster.com

    Author of "SQL Server 2k for the Oracle DBA"


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply