Results 1 to 3 of 3

Thread: Database defrag

  1. #1
    Join Date
    Mar 2004
    Posts
    4

    Unanswered: Database defrag

    Hello,
    I am working with a very large db (850+gig) that is in desperate need of defragging. I am familiar with DBCC Reindex and DBCC IndexDefrag but am concerned that these processes will take an extremely long time to run. Has anyone had any experience with any 3rd party tools that better/faster then DBCC? Or any ideas or suggestions how best to attack this? Among the many tables with large footprints and millions of rows, I have 3 tables with reserved space over 100g also 3 tables with over 500Mil rows (1 tbl with > 1bil rows). This is a 24x7 db.

    Thanks in advance for any ideas and suggestions
    Jeff

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I assume you used SHOWCONTIG on the tables to determine that there is fragmentation in your database.

    I don't think there any tools that would do anything anywhere near to what DBCC does. In fact, I've never heard of a tool that claims to replace DBCC functionality.

    As per INDEXDEFRAG vs. rebuilding, - it depends on the degree of fragmentation, size of the table, maintenance window, and of course, - how close you are to retirement
    "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
    Mar 2004
    Posts
    4
    You could say I have some fragmentation - you could say I have a huge amount of fragmentation! Here is the output from 2 of the tables... I plan on running DBCC INDEXDEFRAG in the background and hope it's not too intrusive. Rebuilding means taking the db offline (or at least the table) - unless someone has some better tricks up their sleeve...

    TABLE level scan performed.
    - Pages Scanned................................: 5397937
    - Extents Scanned..............................: 705604
    - Extent Switches..............................: 4688826
    - Avg. Pages per Extent........................: 7.7
    - Scan Density [Best Count:Actual Count].......: 14.39% [674743:4688827]
    - Logical Scan Fragmentation ..................: 36.78%
    - Extent Scan Fragmentation ...................: 69.05%
    - Avg. Bytes Free per Page.....................: 1622.3
    - Avg. Page Density (full).....................: 79.96%
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.


    TABLE level scan performed.
    - Pages Scanned................................: 7303279
    - Extents Scanned..............................: 918662
    - Extent Switches..............................: 6153163
    - Avg. Pages per Extent........................: 7.9
    - Scan Density [Best Count:Actual Count].......: 14.84% [912910:6153164]
    - Logical Scan Fragmentation ..................: 31.89%
    - Extent Scan Fragmentation ...................: 63.25%
    - Avg. Bytes Free per Page.....................: 1852.8
    - Avg. Page Density (full).....................: 77.11%
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Posting Permissions

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