Ambling through undocumented DBCC commands may result in boredom

There comes a time when we heed a certain call. The call is to avoid dangerous undocumented DBCC commands in SQL Server, especially those that bypass built-in protections. I’m looking directly at you DBCC WRITEPAGE. Besides, Paul Randal (blog | Twitter) has written strongly and authoritatively on the topic, striking fear in the hearts of everyone not called John.

Of course, there’s a flip side. No, I don’t mean to rambunctiously frolic in the waters of other dangerous undocumented DBCC commands. Rather, I’d like to turn your attention to a seldom used, often unloved command of the DBCC world. Or as Ewald Cress (blog | Twitter) puts it, “What am I supposed to do with this?”

Ewald is of course the progenitor of the blog post about spinlocks that very few people are interested in, but for those that are, they can revel in a great deal of useful information. And it is Ewald who handed over his notes about the extremely obscure, lesser known, unremarkable, and equally undocumented command, DBCC READPAGE.

Put on your coat and hat

I must emphasize that Ewald has done most of the dirty work (and the words describing it). I am just here for the clout.

Here’s what is semi-officially known:

DBCC READPAGE ({ dbid, 'dbname'}, fileid, pageid, formatstr [, printopt = { 0 | 1} ])

The headline is that this gives you both more and less than DBCC PAGE. Less, because there is zero insider knowledge about what might live in the bytes found on the page (e.g., PAGE would call Page::Dump() to pretty-print itself). And more, because if it’s raw uninterpreted bytes you’re after in result set format (WITH TABLERESULTS needed?), you’ve come to the right place. And some folks like their bytes raw.

The first few parameters are exactly as per PAGE, and as a casual reminder, you also need trace flag 3604 in order to see the results in your SQL Server Management Studio window. Or perhaps you’ve migrated to Azure Data Studio. I’m not the boss of you.

printopt affects exploded presentation of nested stuff — you’ll see the difference between 0 and 1 when running more complicated examples.

So, the action lies in the mysterious formatstr, which is a little language¹If you’ve ever used awk on Unix-like operating systems, this is nothing like that, but it helps to keep it in mind. for grabbing sections of what’s on the page. A simple example would be V100(0), which will dump the 100 bytes starting at offset 0. In fact, omitting the (0) means a default offset of 0, saving you three keystrokes if you’re at the start of the page.

But wait, I hear you cry, where does V come from?

The funky answer is that there is a handful of such verbs:

• D sets a numeric symbol of which you can have a max of 30: D 0 [abc] sets symbol/slot 0 to abc.
• I = An integer that can be of length 1, 2, or 4 bytes. e.g., I 2 (8190) reads the offset of slot 0 on a normal data page.
• R = Repeat the following expression in brackets n times. e.g., R 2 (I 2 (8190)) does the above twice. While this appears pointless at first glance, it turns out omitting the offset in the inner expression means the reigning offset is inherited and moves on by those number of bytes. In other words, the offset is a kind of cursor.
• U = Use the symbol defined by D. D 0 [abc] U 0 I 2 (96) runs fine (U fails if slot 0 is undefined), but neither affects what the I expression actually does (this is clearer in the context of push/pop).
• V = Value. The raw hexadecimal data.
• L = Push
• P = Pop
• S = Skip. Advances the cursor so you don’t need to dump the next n bytes.

Then you can name retrieved expressions in curly braces, e.g. I {slot_0} 2 (8190). No spaces allowed in names!

Diving right in

Here’s an example which uses naming and loops:

R {R} 2 (I {I1} 2 I {I2} 4)

Given the R, I1, and I2 “scope identifiers,” R gets an index added per iteration, and the four returned rows are named:

R[0].I1
R[0].I2
R[1].I1
R[1].I2

To get a working format for push and pop (L and P respectively), this works:

L {L20} (20) L {L100} (100) I {innerI} 2 P I {I1} 4 P I {I2} 4

At this point, Ewald found himself wondering whether it ought to be possible to pop the offset itself. It turns out they only affect notional levels and the emitted names. There is no way to use these to manage offsets. That’s a shame if you were hopeful about this possibility in a command you’d never heard of until today, but on behalf of Ewald I apologize.

If we wanted to read two bytes, skip ten bytes, and read two again, we could do this:

I {first} 2 S 10 I {second} 2

But lo! It turns out offsets can be relative: (4) is absolute, but (-4) or (+4) are relative to the current cursor position. This does add some confusion in the next example:

S (8190) R {slot} 10 (I 2 S 0 (-4))

Note that stepping back two bytes at a time to print the first 10 entries in a page slot array requires -4 to counteract the +2 advance implied by the I 2 command itself.

So long, farewell

Ewald finishes by saying there’s precious little here that you wouldn’t want to do by other means but imagines that it has its uses during development for page types which are opaque to the end user. I agree that digging into obscurity like this is a fascinating insight into the addled arcane minds of software developers.

Perhaps you wanted actual examples using pages in an actual database, and for that I’m sorry. I’ll leave that as an exercise for the reader. The single, solitary reader. Probably Lonny if we’re being honest. Hi Lonny.

Many thanks to Ewald Cress (who attended the same rural school in South Africa as me in 1990) for most of the content in this blog post. Roughly 90% of it is his work.

Share your own obscure knowledge in the comments. If you’re reading this, wrote the READPAGE command, and want to add any corrections, please feel free to do so, Paul.

Photo by Dan Gold on Unsplash.