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 :
DBCC SQLPERF( LOGSPACE ) go
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:
For the active threads information, the syntax is as follows:
DBCC SQLPERF(THREADS) go
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:
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.
DBCC PERFMON Go
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 Go DBCC SHOWCONTIG Go
The DBCC PROCCACHE command returns the following information on the procedure cache:
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.
INPUTBUFFER and OUTPUTBUFFER
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:
DBCC INPUTBUFFER( spid ) DBCC OUTPUTBUFFER( spid )
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.