The `dbcc perfmon` command is no longer used to show I/O activity, cache activity, locking activity, and process waiting in SQL Server. It was deprecated in SQL Server 2012 and removed entirely in SQL Server 2016. Instead, you can use the following tools to monitor SQL Server performance:
SQL Server Activity Monitor: This tool provides a real-time view of SQL Server activity, including I/O activity, cache activity, locking activity, and process waiting.
Performance Monitor: This tool provides a more detailed view of SQL Server performance, including performance counters for I/O activity, cache activity, locking activity, and process waiting.
Dynamic Management Objects (DMOs): DMOs are a set of views that provide real-time information about SQL Server performance. You can use DMOs to query information about I/O activity, cache activity, locking activity, and process waiting.
To monitor SQL Server performance using SQL Server Activity Monitor, open SQL Server Management Studio and connect to the SQL Server instance that you want to monitor. In the Object Explorer pane, expand the Databases node and expand the database that you want to monitor.
Right-click the database and select
Tasks > Activity Monitor.
The SQL Server Activity Monitor window will appear. To monitor SQL Server performance using Performance Monitor, open Performance Monitor. In the Performance Monitor window, expand the SQL Server node and expand the instance of SQL Server that you want to monitor. Expand the Counters node and select the performance counters that you want to monitor. The Performance Monitor window will display a graph of the selected performance counters.
To monitor SQL Server performance using DMOs, you can use a SQL query or a third-party tool. To use a SQL query, you can use the following syntax:
WHERE counter_name IN ('Physical Disk I/O Throughput', 'Buffer Manager Cache Hit Ratio')
This query will return the physical disk I/O throughput and the buffer manager cache hit ratio for the current SQL Server instance.
To use a third-party tool, you can use a tool such as SQL Server Management Studio or Red Gate SQL Monitor. These tools provide a graphical user interface for viewing DMO information.
dbcc command Legacy
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 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.
The spinlock section, for the various item types on which SQL Server places internal locks, keeps track of the number of retries for a lock (spin) and the number of requests that need to retry (collisions).
The wait section, for the various items that a task can wait on, keeps track of the number of requests that wait and the total wait time.
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.
Click the link above to see the output for this command.
If the connection’s last command did not return the 512 bytes, the buffer will contain information from the previous command.
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 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
trace flags: A SQL Server dbcc option that changes the behavior of SQL Server.