The DBCC PSS command enables you to obtain and dump out detailed information about processes and users currently connected to a SQL Server. It has remained an undocumented feature because the type of information dumped out by PSS has changed from one SQL Server edition to the next, and there's no guarantee it will continue to be supported. But it is still useful for obtaining low-level process data.
The syntax for DBCC:
DBCC TRACEON(3604)
DBCC PSS(<userID>, <processed>, <displayoptions>)
When you run PSS with nothing but user 0 as the parameter -- DBCC PSS(0) -- the database dumps out a list of all active users and their attendant processes. If you then rerun DBCC PSS with a user number and a process number, you'll see an extremely detailed dump of all of the user's flags as well as the execution context, the input stream and the output buffers for that process.
DBCC TRACEON(3604)
DBCC PSS(5,53) <these are arbitrary user and process numbers, but you get the idea>
The <displayoptions> option is not required, but if you set it to 1 -- e.g., DBCC PSS(5,53,1) -- you'll see all open descriptors and the sequence tree for the query (if there is one) also dumped out to the console. The output buffers are raw data, so if they feature things like column names or other internal data they're usually dumped in UTF-16 format (the codepage used by SQL Server internally).
If you simply dump a user ID (except user 0) without a process ID, you'll see data for all the processes currently executing under that ID. Keep in mind this might take a while to print if you have a lot of processes currently running. Most flags dumped by PSS are not documented, but some are fairly self-evident: Language is the language ID for the connection and prowcount is the number of rows returned as set by the ROWCOUNT option for that connection.
About the author: Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!
More information from SearchSQLServer.com
Tip: Undocumented stored procedure: View SQL Server 2000 raw data
Tip: Use DBCC CLEANTABLE to reclaim unused space in SQL Server tables
Expert response: Fragmentation based on results from DBCC SHOWCONTIG
