Post reply

DBCC SHOWCONTIG

  • February 5, 2004 at 6:57 am

    #107259

    DBAs,

    When using DBCC SHOWCONTIG, how much importance should be placed on the analysis for the system tables?  Also, I understand that the 'Avg. Bytes Free per Page' should be low.  Can anyone recommend a threshold at which one should become concerned?  What would be reasonable level to be concerned in terms of the 'Avg. Page Density (full)' metric?

    Thanks!


    Carpe diem!

  • February 6, 2004 at 1:11 am

    #493003

    Not a direct answer to your question, but maybe it helps anyway.  This is an answer from Paul Randal DBCC Technical Lead, Microsoft SQL Server Storage Engine in sqlserver public newsgroup

    "It's not possible to defragment system tables in SQL Server 2000. You should

    find that your system tables' fragmentation does not impede your system's

    performance due to the way they are accessed and that the hot pages should

    be in the buffer pool anyway."

    FWIW

     

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • February 6, 2004 at 1:43 am

    #493004

    It's possible to force a rebuild of a system table by changing the fillfactor on the clustered index - this then forces a rebuild. My msdb gets hit by around 75k new rows a day, and a similar amount are removed - leading to fragmented tables - does it improve performance? probably not but it will tidy things up - just hate heavily fragmented tables on my reports

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

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

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