Post reply

DBCC SHOWCONTIG

  • August 22, 2005 at 8:40 am

    #102722

    - Pages Scanned................................: 63958

    - Extents Scanned..............................: 8002

    - Extent Switches..............................: 8001

    - Avg. Pages per Extent........................: 8.0

    - Scan Density [Best Count:Actual Count].......: 99.91% [7995:8002]

    - Extent Scan Fragmentation ...................: 26.56%

    - Avg. Bytes Free per Page.....................: 357.6

    - Avg. Page Density (full).....................: 95.58%

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    WHEN I Ran DBCC SHOWCONTIG ON one of my database table I got the above result and the Stored Procedures which ran like with in 5 seconds are now taking more than 40 secs. Can anyone let me know is there any problem with my data base and do i Need to reindex the table?

    Thanks.

  • August 22, 2005 at 1:05 pm

    #584021

    There does not seem to be much of a problem with fragmentation. Are you sure that the procedure is using an optimal execution plan? Are the statistics up to date?

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

  • August 23, 2005 at 9:20 am

    #584250

    The entry "Extent Scan Fragmentation ...................: 26.56%" indicates external fragmentation (gaps between extents). This percentage should be close to 0%.

  • August 23, 2005 at 12:18 pm

    #584330

    also...

     

    Try running DBCC SHOWCONTIG .....  WITH ALL_INDEXES 

    If you didn't do that already, this will show the frag level for all indexes in the table

    The index you listed looks good - I wish all mine were that way!

    John

     

     

  • August 23, 2005 at 3:22 pm

    #584408

    In some cases that is true. But not if the object is stored in multiple files, since then the algorithm for calculating extent scan fragmentation does not work as intended.

    Even so I would not think too much of it in this case with the great figures for the really interesting parts.

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

  • August 24, 2005 at 9:58 am

    #584669

    First I would update the stats then recompile the SP. Also you should check the execution plan whether you are still using all the required indexes



    Bye
    Gabor

Viewing 6 posts - 1 through 5 (of 5 total)

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