SQL Server Performance

Cost of Running DBCC Show Contig

Discussion in 'Performance Tuning for DBAs' started by gortij, Jan 4, 2005.

  1. gortij New Member

    What is the effect of running DBCC ShowContig on Production Servers?
    Does it degrade the performance of a server considerably ?

    We run a 24 X 7 shop and was wondering what might happen if I did that.

    Thanks


    JPG
    gortij, Jan 4, 2005
    #2

  2. Luis Martin Moderator

    Well, if you try to run Showcontig in one table with all index, I think performance will degrade, but you can do it index by index to reduce it.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

    Luis Martin, Jan 4, 2005
    #2

  3. satya Moderator

    I don't see any issues in running against production servers and its adivsable to run during less traffic hours.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
    satya, Jan 4, 2005
    #2

  4. thomas New Member

    Like Satya I don't see heavy impact from running it, provided it's done at minimal times of use (e.g. early hours of the morning). Reindexing itself has more impact and can cause blocking if using dbcc dbreindex.

    I find that running dbcc checkdb() has a much heavier impact than dbcc showcontig.

    Tom Pullen
    DBA, Oxfam GB
    thomas, Jan 5, 2005
    #2

  5. gortij New Member

    Thanks folks. I shall do it.

    JPG
    gortij, Jan 5, 2005
    #2

  6. derrickleggett New Member

    Just an fyi, you can use the FAST option if you don't need the entire result. You might want to check it out.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
    derrickleggett, Jan 5, 2005
    #2

  7. Richard Ding New Member

    If you are using SQL 2000, you can run the fast version, for example:

    DBCC SHOWCONTIG WITH NO_INFOMSGS, FAST, TABLERESULTS, ALL_LEVELS

    I run the above weekly in my maintenance job and it generates results quickly, with minimum impact on my servers.


    Richard Ding, Jan 6, 2005
    #2

  8. derrickleggett New Member

    It's BRILLIANT!!! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Here's a script you might find interesting. Actually, the whole site is good.<br /><br /<a target="_blank" href=http://www.sqlskills.com/resources/demoscripts/sp_RebuildIndexes.txt>http://www.sqlskills.com/resources/demoscripts/sp_RebuildIndexes.txt</a><br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
    derrickleggett, Jan 6, 2005
    #2

  9. bpflanzer New Member

    Unfortunately there are major issues with CASE in this script.
    bpflanzer, Jan 13, 2007
    #2

Share This Page