A Cool Way to Spy on the Output Buffer

Since SQL Server's pre-GUI days, programmers have considered it cool—and even sometimes useful—to know obscure or undocumented Database Consistency Checker (DBCC) commands. Over the years, many of these commands have become a standard part of T-SQL, whereas others have dropped into oblivion. Most of these commands weren't designed to be user-friendly or for use in mainstream T-SQL programs; they were designed to perform a troubleshooting function. Therefore, the commands' syntax is often cryptic and reminiscent of machine code from the 1950s.

DBCC OUTPUTBUFFER is an obscure—but useful—DBCC command that lets you look at a SQL Server process's output stream. Its sibling, DBCC INPUTBUFFER, checks the input buffer for a particular system process ID (SPID). DBCC INPUTBUFFER is probably more widely known because Enterprise Manager invokes it every time you select Current Activity, Process Info and double-click a particular process to see the most recent T-SQL commands this process submitted. Let's look at DBCC OUTPUTBUFFER's uses and a stored procedure I wrote to make the command's results more readable.

The Purpose of DBCC OUTPUTBUFFER

Why would you want to sneak a peek at a process's output buffer? I usually look at the buffer when I want to investigate why a particular process is taking longer to execute than I expected. Looking at the output buffer is especially useful in three cases. The first case is when you want to analyze the activity of a process that someone else initiated but you don't have direct access to the process's output. This kind of analysis falls within the DBA's job of helping users resolve SQL Server problems.

The second case is when an application initiates a process that hides SQL Server's output from the end user. Such an application might substitute its own generic messages instead of the standard SQL Server messages, making it hard to troubleshoot an error. I prefer to see the actual SQL Server—generated error message than a nondescript message such as SQL Server error. Contact support. DBCC OUTPUTBUFFER can show you the real error message, unless the application drops the connection after the error occurs.

The third case when you might want to see the contents of the output buffer is when you're monitoring a long-running process that yields little output. What happens if you submit a T-SQL batch in Query Analyzer containing multiple queries that aren't GO-delineated? When queries return only a few rows, the results don't appear until the entire batch finishes. Each system process has an output buffer, and the process won't send the buffer's contents to the end user until the batch is complete or the buffer is full. DBCC OUTPUTBUFFER lets you see the results of the first few steps while the last steps are still running.

Analyzing Output

DBCC OUTPUTBUFFER's syntax is simple:

DBCC OUTPUTBUFFER( SPID )

where SPID corresponds to the SPID column in the sysprocesses table. You can also find a process's SPID on Enterprise Manager's Process Info screen.

The results of DBCC OUTPUTBUFFER are in hexadecimal code, as Figure 1 shows. The output buffer contains binary SQL Server control information, as well as the data that queries have retrieved and server messages for the client. So hex code is the logical common denominator for these types of information. When you use a front-end application such as Query Analyzer to communicate with SQL Server, the application interprets all the control information to properly format and present its data to the end user. However, when you look at a process's output stream, you see its exact contents. You can't filter out the control information because it often relates to previous communications between the server and the process that you're watching.

DBCC OUTPUTBUFFER returns its results as one column, consisting of three sections. The left section shows the internal memory address of each part of the output buffer. The left section has no practical use if you just want to observe the output going to a particular process. The output's middle and right sections contain the same information: The middle section shows the information in hex format, and the right section shows it in ASCII format. Notice that the ASCII portion has some readable parts interspersed with periods and other symbols. These symbols appear because most of SQL Server's communications with clients occur in Unicode, which uses 2 bytes for every character. Converting OUTPUTBUFFER content to ASCII is a byte-for-byte process, so one Unicode letter in the buffer translates into two ASCII characters in the right section of the DBCC OUTPUTBUFFER results. The sample output in Figure 1 uses a period for all nonprintable characters. The right section of the results is also only 16 positions.

To obtain the output that Figure 1 shows, open Query Analyzer and execute SELECT @@SPID in the query window. This will give you the SPID that you'll use in a moment. Then in the same query window, execute the following test script:

PRINT    'This is a test of DBCC OUTPUTBUFFER.'SELECT   city, COUNT(*)FROM     pubs..authorsGROUP BY citySELECT 1/0

Next, in another query window, execute DBCC OUTPUTBUFFER( SPID), using the SPID value you obtained earlier. (Your results might differ slightly from mine because of the SQL Server version, server name, and so on that you use.) The first command in the test script prints some text in Unicode. The second command returns a resultset of two columns—varchar and int types. The third command causes division by zero and results in an error message. Unfortunately, only the character information is readable. DBCC OUTPUTBUFFER displays integer, numeric, datetime, and other such data types in their internal format. You can decipher hex values manually, but that task is tedious and time-consuming.

I wrote a stored procedure that takes a pragmatic approach to reading DBCC OUTPUTBUFFER. The procedure ignores any unreadable characters, eliminates the periods in Unicode text, and displays output in longer lines. In many situations, this stored procedure can be more practical than the memory dump that DBCC OUTPUTBUFFER gives you.

Listing 1 shows the SQL Server 2000 source code for sp_outputbuffer; Figure 2 shows Listing 1's sample output. The procedure requires one parameter: the SPID of the desired system process. Although sp_outputbuffer doesn't make the output buffer's contents completely clean, the procedure does make the output more readable.

Remember DBCC OUTPUTBUFFER the next time you want to figure out what a particular process is doing. And take advantage of sp_outputbuffer to make DBCC OUTPUTBUFFER's results readable.