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
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.
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.
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
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.
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.
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.