Last week on the MVP distribution alias there was a discussion on undocumented DBCC commands and someone asked why so many potentially useful DBCC commands are undocumented. I briefly explained why, and I’ve discussed this openly during classes and conferences too and don’t consider it an NDA explanation, so I thought I’d share it with you.
DBCC commands have been around for a long, long time. Its way easier to add a DBCC command than it is to add any other way of getting SQL Server to do something through T-SQL.
So the easiest way to make SQL Server change its behavior in some way, or get access to some internal data structures, is to write a DBCC command to print them out. The vast majority of the undocumented DBCC commands exist to help testing (the multitude of test teams inside the SQL Server Product Group) and debugging (Product Support and SQL Server team developers).
DBCC doesn’t use the same parsing mechanism that everything else in the Engine does (i.e. the parser and algebrizer in the Query Processor). It does all it’s own parsing, permission checking, parameter checking, and code calling – I rewrote this entire mechanism during SQL Server 2005 development to be standardized and easily extensible – ironically making it even easier to add DBCC commands!
When I rewrote all this code, I cleaned up a bunch of commands that weren’t being used any more (e.g. DBCC NEWALLOC, DBCC TEXTALLOC) and some that were used but were dangerous (e.g. DBCC PINTABLE – which I explained about here). As part of that effort I canvassed the entire team to see which commands weren’t wanted any more. I also figured out which commands should be documented – there were five on the short list.
At the top of the short list were DBCC PAGE and DBCC IND, which you’ve seen me use a lot on the blog here, and that are the two most well-known undocumented DBCC commands out there. There simply wasn’t enough justification of the engineering effort required to document them. The same thing happened in SQL Server 2008 development – not enough time.
Not enough time for what? Well, it takes a lot of work to make an undocumented command become documented:
- Documentation: The Books Online entry needs to be created with appropriate examples
- Testing: There are an infinity of test cases for any feature, considering combinations of all other features, and these have to be whittled down to a representative set. For DBCC PAGE, that includes some corruptions, and all types of pages. That’s a lot of work.
- Hardening: The DBCC PAGE code is pretty robust, but ‘pretty robust’ isn’t good enough for a documented command – it has to 100% not cause problems on a production server. I’m confident that it won’t but it would have to be tested extensively to make doubly-sure.
- Keeping up: As a documented command, any time any page structure changes, then DBCC PAGE has to cope with it.
And all this engineering effort was (and probably always will be) deemed less important than other features.
The thing to consider is what practical use to the majority of SQL Server customers would any undocumented DBCC command be if it were to be documented? In some corruption scenarios, yes, but mostly it’s in working out what’s going on under the covers of SQL Server – and that’s not a compelling business case.
I’m not holding my breath on any of the undocumented DBCC commands becoming documented, and I hope this explains why.