SQL Server Performance

When to Rebuild indexes in SQL server 2000

Discussion in 'Performance Tuning for DBAs' started by Arman, Feb 21, 2008.

  1. Arman New Member

    Hi, I am a new into sql field. I want to know that when we have to decide to rebuild indexes. What kind of cammands i have to run and how to analyze thier result before going to rebuild index.I ran DBCC SHOWCONTIG (Tablename) anf got the following results.
    TABLE level scan performed.
    - Pages Scanned................................: 3
    - Extents Scanned..............................: 2
    - Extent Switches..............................: 1
    - Avg. Pages per Extent........................: 1.5
    - Scan Density [Best Count:Actual Count].......: 50.00% [1:2]
    - Logical Scan Fragmentation ..................: 0.00%
    - Extent Scan Fragmentation ...................: 50.00%
    - Avg. Bytes Free per Page.....................: 246.7
    - Avg. Page Density (full).....................: 39.95%
    I know that the DBCC SHOWCONTIG command provides several measurements, the key one is Scan Density. This figure should be as close to 100% as possible. If the scan density is less than 75%, then you may want to reindex the tables in your database and in my case its 50%. I can not go to rebuild my index only on this concolusion. Can some one tell whatelse and where else i have to look to know that these indexes needs to be rebuild or refrag.
    Thanks in Advance.
  2. Madhivanan Moderator

Share This Page