Monitoring SQL  «Prev  Next»

Lesson 9 Monitoring with dbcc, continued
Objective Use dbcc to monitor overall performance.

Database Consistency Checker

dbcc perfmon

The dbcc perfmon command shows I/O activity, cache activity, locking activity, and process waiting.
You can use this command to monitor how SQL Server is performing.
This dbcc command is not documented to exist by Microsoft in the Books OnLine. You should be wary about using it as it may change or go away. It is recommended that you use Windows NT performance monitor instead.
The syntax of dbcc perfmon is as follows:
dbcc perfmon
The dbcc perfmon command returns information about the overall performance of SQL Server.

dbcc outputbuffer

The dbcc outputbuffer command can be used to look at the network buffer that was last sent to a connection. The syntax is as follows
dbcc outputbuffer (spid)
The output for this command will only display the first 512 bytes that were sent to the client.

1. The memory address that the information is stored in, 2. The octal representation of the output buffer, 3. The ASCII representation of the buffer

dbcc inputbuffer

The command dbcc outputbuffer returns the last output buffer, whereas the command dbcc inputbuffer will return the last buffer sent from a client to dbccinput-buffer-output .
The syntax of dbcc inputbuffer is as follows:
dbcc inputbuffer (spid)
The next lesson will cover the dbcc trace flags[1] that can be used to monitor SQL Servers behavior.
Click the link below to test your knowledge of the dbcc commands presented in this module.
dbcc commands

[1] trace flags: A SQL Server dbcc option that changes the behavior of SQL Server.