Post reply

different results dbcc showcontig

  • February 14, 2005 at 9:08 am

    #109178

    I have a table with no clustered index but a few nonclustered index, when i run  DBCC SHOWCONTIG (mytable, myindex) i get these results:

    DBCC SHOWCONTIG scanning 'mytable' table...

    Table: 'myindex' (1861581670); index ID: 12, database ID: 6

    LEAF level scan performed.

    - Pages Scanned................................: 11071

    - Extents Scanned..............................: 1389

    - Extent Switches..............................: 1388

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

    - Scan Density [Best Count:Actual Count].......: 99.64% [1384:1389]

    - Logical Scan Fragmentation ..................: 12.37%

    - Extent Scan Fragmentation ...................: 2.30%

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

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

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

     

    But if I run EXEC ('DBCC SHOWCONTIG WITH TABLERESULTS') to populate temp table with results for all tables in base I notice that the nonclustered index does not appear in the indexname column, and the logicalfragmentation column for mytable states that there is 99.994430541992188 logicalfragmentation and the extentfragmentation column for mytable states that there is 55.579963684082031 extentfragmentation.

    I want to run a script to report index fragmentation using EXEC ('DBCC SHOWCONTIG WITH TABLERESULTS')  into temp table but I need to know which result set is accurate: DBCC SHOWCONTIG (mytable, myindex)  or EXEC ('DBCC SHOWCONTIG WITH TABLERESULTS') . Any advice would be greatly appreciated.

     

     

  • February 17, 2005 at 8:00 am

    This was removed by the editor as SPAM

  • February 17, 2005 at 11:55 pm

    #543013

    BOL states that if you don't specify the index in the DBCC SHOWCONTIG command, that it then uses the base index to report results. Your first example queries an index, your second example is querying the table base index.

    You could add the ALL_INDEXES option to your second example.

    Regards


    __________________________________
    Searching the KB articles is like
    picking your nose. You never know
    what you'll find.

Viewing 3 posts - 1 through 2 (of 2 total)

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