Last SQLskills classes of 2012 coming up in August

Time flies when you're having fun!

August is fast approaching and it brings the last set of scheduled SQLskills Immersion Events of 2012 – Bellevue, WA. We will not be doing any more public classes after August anywhere in the world until Spring 2013. The main reason for this is that the team has a lot of other commitments and projects on the go and we simply don't have the time to jam in another set of classes this year!

The classes are filling up (IE1 and IE2 are already more than half full) so don't leave it too late to register and end up being disappointed! You've got until one month before each class to register for the early-bird price of US$2995.

Here are the classes, all in Bellevue, WA, USA, with links to their information and registration pages:

We hope to see you there!

Why are so many DBCC commands undocumented?

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.