Using 'DBCC' to Examine Performance

Microsoft might have targeted the DBCC command for extinction, but it can still provide useful information on the current state of SQL Server. The next several sections detail the available options. Many of the same commands are used and presented in a more friendly format by the SQL tools; however, you can capture information from these DBCC commands into tables for historical statistics. The old DBCC MEMUSAGE is all but a stripped-out skeleton of a command now only returning the Procedure Cache top-20 list now.


The DBCC SQLPERF command has been drastically altered from previous versions and now only reports transaction-log space usage for all databases and active threads on the server. The syntax for the transaction log information is as follows:


The results of this command are tabular and can be captured into a database table to maintain historical statistics on log usage on the server. The information returned is as follows:

Data Description
Database Name Name of the database
Log Size (MB) Current size of the log file
Log Space Used (%) Percentage of the log file currently used
Status Status of the log file (always contains 0)

For the active threads information, the syntax is as follows:


The results of this command are also tabular and can be captured into a database table to maintain historical statistics on the threads used and for what purpose. The information returned is as follows:

Data Description
Spid Server process ID
Thread ID Thread ID at the operating system level
Status Status of the process (sleeping, background, and so on)
LoginName SQL Server login associated with the spid
IO Amount of IO accumulated
CPU Amount of CPU accumulated
MemUsage Amount of memory touched

Overall, DBCC SQLPERF is great for corresponding SQL Server processes back to the operating system thread information.


Another DBCC command useful for finding performance information on SQL Server is DBCC PERFMON. This command returns information about the I/O work that SQL Server has been performing, the page cache state and operation, and network statistics. The system-stored procedure equivalent is sp_monitor. PERFMON might be left out of future SQL Server releases, so use caution when embedding its use in your activities.



The DBCC SHOWCONTIG command has been discussed in other chapters and is only mentioned here for completeness. The DBCC SHOWCONTIG command illustrates the internal state of extents and pages and is helpful in determining how SQL Server is likely to perform when reading data from a table. This can be valuable information when trying to determine the level of fragmentation and the page density of table allocations.

USE dbname 


The DBCC PROCCACHE command returns the following information on the procedure cache:

Data Description
num proc buffs The number of possible cache slots in the cache
num proc buffs used The number of cache slots in use by procedures
num proc buffs active The number of cache slots that have currently executing procedures
proc cache size The total size of the procedure cache
proc cache used The amount of the procedure cache holding stored procedures
proc cache active The amount of the procedure cache holding stored procedures that are currently executing

Even though SQL Server 2000 grows and shrinks the procedure cache size as required, you will still want to monitor how much of the memory allocated to SQL Server is in use by the procedure cache. This need makes the DBCC command quite useful. This, combined with DBCC MEMUSAGE, lets you know what the complete picture of procedure cache is. You can also use the DBCC FREEPROCCACHE command to remove all elements from the procedure cache. This will, for example, cause an ad hoc SQL statement to be recompiled rather than reused from the cache.


You use the DBCC INPUTBUFFER/OUTPUTBUFFER command to examine the statements sent by a client to the SQL Server. The syntax for these commands is as follows:


INPUTBUFFER shows the last statement sent from the specified client, and OUTPUTBUFFER shows the results sent back from the SQL Server.

SQL tools use INPUTBUFFER and OUTPUTBUFFER to display current activity, and you can also use them to examine the commands sent by certain processes that are affecting system performance.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features