SQL Server Performance

When to rebuild Indexes in sql server 20000

Discussion in 'The Lighter Side of Being a DBA' 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.
    - Pages Scanned................................: 3
    - Extents Scanned..............................: 2
    - Extent Switches..............................: 1
    - Avg. Pages per Extent........................: 1.0
    - Scan Density [Best Count:Actual Count].......: 50.00% [2:1]
    - Logical Scan Fragmentation ..................: 0.00%
    - Extent Scan Fragmentation ...................: 0.00%
    - Avg. Bytes Free per Page.....................: 4945.0
    - Avg. Page Density (full).....................: 38.91%
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    I know that Scan Density should be close to 100% and in my case its 50%.I can not go to rebuild my index on 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. satya Moderator

  3. Arman New Member

    Thanks !! [H]
  4. Jack Vamvas Member

    In SQL Books Online under DBCC SHOWCONTIG - there is a good script , that checks for levels of logical fragmentation - and only when it's a certain level will it do a defrag. You have the option to change the level. Deciding at what percentage defrag to instigate the defrag level , is dependant on your system ie. are there lots of INSERTS , UPDATES etc

Share This Page