The Secret Lives of DBCC Commands

Microsoft's unwillingness to document many valuable Database Consistency Checker (DBCC) commands has always been a pet peeve of mine. In SQL Server's early releases (6.5 and 4.2), the "consistency checker" name was more relevant than it is in SQL Server 2000 and 7.0. Many early DBCC commands checked page structures for errors that might corrupt the data. Fortunately, corrupt databases are mostly a thing of the past. However, DBCC commands are still with us. Today, I think of the "D" as standing for "diagnostic" because current DBCC commands are most useful for performance and troubleshooting exercises.

Many of you have used documented DBCC commands. Search SQL Server Books Online (BOL) for entries that start with DBCC and you'll quickly get a list of commands that are part of the official documentation set. However, many valuable DBCC commands are undocumented. Even when you find documentation for lesser-known commands, sometimes the information is haphazard. Microsoft sometimes posts a simple Knowledge Base article about a DBCC command when information about the command has leaked out through various channels and third-party sites are writing about it. Unfortunately, sketchy documentation means the general SQL Server community doesn't know about many valuable performance-tuning and troubleshooting commands.

Here's a practical example that shows why documenting DBCC commands is so important. The little-known DBCC WAITSTATS command gives you statistics about various waittypes that connections might be waiting for. Understanding SQL Server waitstats and how to interpret them with DBCC WAITSTATS is one of the most important new tuning tricks to come out in the past few years. Did you notice that I said new? The DBCC WAITSTATS command isn't new, but the fact that anyone has any idea how to interpret the information the command provides is new. Microsoft simply never bothered to document the waittypes that might show up in sysprocesses. Today, Microsoft tells you that analyzing waittypes is a valuable and necessary step for fully tuning your system. What changed? Did the information magically become valuable? Or has the information always been valuable but no one had access to it because the DBCC command wasn't documented? Waitstat statistics have always been an important tuning tool. Alas, no one (including most people at Microsoft) had any idea how to use it.

If Microsoft had done a better job of documenting the DBCC WAITSTATS command and waittype information, third parties and customers might have wondered about them enough to play with the data. They might have gleaned interesting tuning tips from analyzing waitstats years before Microsoft engineers got around to figuring it out. Then those third parties might have asked Microsoft probing questions and encouraged the SQL Server development team to dig deeper into this important resource years earlier than it did. Either way, customers would have had access to the information a long time ago.

I cheated and picked an example in which documentation has a tremendous upside and little downside for Microsoft. To be fair, Microsoft often has valid reasons for not documenting some DBCC commands and other useful commands. One reason is that if Microsoft documents something, people begin to rely on the behavior, which limits Microsoft's ability to change it in future releases. Another reason Microsoft might not document certain commands is that some DBCC commands can change data and be destructive. I'm not suggesting that Microsoft document potentially destructive commands. But Microsoft could fully document benign DBCC commands (i.e., commands that can't change data) and make it clear that some commands might not be supported in future releases. I think the SQL Server community would be well served by better command documentation. There's gold in them specs.

I've heard through the grapevine that Microsoft is considering documenting more DBCC commands. If you think that expanding documentation is as good an idea as I do, drop a note to [email protected]. When you write to this alias, your requests will go to the development team for consideration. The trick to getting something from SQLWish is to ask for it when it's practical for Microsoft to consider the change. If Microsoft is thinking about different strategies for documenting DBCC commands, now is the time to make your wishes known. But a simple "I want more DBCC commands" isn't likely to sway many folks on the development team. Send a thoughtful note that makes a compelling argument for your request and you might get your wish.