Undocumented DBCC Commands

If you’ve been a SQL Server DBA for very long, you’ve probably made use of some cool but undocumented DBCC commands.  Kalen Delaney, in her series of articles in SQL Server Magazine mentions several very powerful undocumented DBCC commands that are extremely useful for performance tuning, such as DBCC PAGE.  

Microsoft keeps commands undocumented for a number of reasons.  Some DBCC commands are prohibitively lock-heavy or have too much impact on system performance to be used in all but the most specialized of circumstances.  Some DBCC commands were created for a specific version or patch for SQL Server and Microsoft has no intention of guaranteeing that they’ll be around in the next release.  Every now and then, a DBCC command isn’t documented simply because it’d take way to much work to do a good job of documenting it.  For DBCC commands like these, they usually get documented afterwards within the Microsoft Knowledge Base, such as DBCC PSS.

You can find a long and somewhat enigmatic list of DBCC commands at http://www.extremeexperts.com/sql/articles/ListDBCCCommands.aspx.  

Some may be very useful, but others may simply be holdovers from the old Sybase days.  Who knows – they are, after all, undocumented.  But some of these commands sound quite intriguing.  Here are just a few that you can just prefix 'DBCC ' to each one and it'll go:

memorystatus

cursorstats (spid [,clear])

show_statistics (table, target)

sqllmgrstats

cachestats

stackdump

I haven’t had time to experiment with all of these, but if you get a chance (on an unimportant test/development box of course), I’d love to hear what you find out.  Be safe!  But have fun too. 

Thanks,

-Kevin