SQL Server Performance

DBCC IndexDefrag

Discussion in 'Performance Tuning for DBAs' started by Kyliedude, Nov 27, 2002.

  1. Kyliedude New Member

    Hi there,

    How greatly does Extent Scan Fragmentation affect performance? I am running DBCC IndexDefrags and my Showcontig is very cool other than having an increasing Extent Scan fragmentation. Does this factor affect performance? And if so, what use is DBCC IndexDefrag? I'd rather not do a DBCC DBReindex due to the size of my tables, time constraints etc.

    Thanks.
  2. bradmcgehee New Member

    DBCC Indexdefrag does not perform a 100% defragmentation. If you want to perform a 100% defragmentation you will have to rebuild the indexes. The reason for this, and the pros and cons of this, are that while DBCC Indexdefrag reduces fragmentation, it does not eliminate it, but it will not lock tables and prevent users from accessing the database. On the other hand, rebuilding idexes will perform a 100% defragmention, but it will lock tables for a short time, which could negatively affect user access. So you have to choose which option is best for your situation.

    You might consider running DBCC Indexdefrag daily or weekly, and only rebuild the indexes completely monthly, or whatever best fits your needs.

    And to really answer your first question, Extent Fragmentation can hurt performance over time if it gets bad enough, so you will eventually want to rebuild your indexes at some point.


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

    Thanks Brad,

    I think i'll take the weekend rebuild approach.

Share This Page