| Lesson 9 | Monitoring with dbcc, continued |
| Objective | Use dbcc to monitor overall performance. |
SQL-Server Database Consistency Checker
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:
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
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:
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.
- 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:
SELECT *
FROM sys.dm_os_performance_counters
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
The
dbcc perfmon is as follows:dbcc perfmonThe
dbcc perfmon command returns information about the overall performance of SQL Server.
dbcc perfmon output
The following is sample output from
dbcc perfmon:
Statistic Value ------------------------------------- Reads Outstanding 0.0 Writes Outstanding 0.0 (2 row(s) affected)
The outstanding I/O section lists the number of disk reads and writes that are currently being performed on SQL Server’s behalf.
Statistic Value --------------------------- -------- Cache Hit Ratio 97.31707 Cache Flushes 0.0 Free Page Scan (Avg) 0.0 Free Page Scan (Max) 0.0 Min Free Buffers 331.0 Cache Size 4362.0 Free Buffers 50.0 (7 row(s) affected)
The cache information section provides an overview of the data cache usage.
Statistic Value ------------------------------ ------ Network Reads 79.0 Network Writes 152.0 Command Queue Length 0.0 Max Command Queue Length 0.0 Worker Threads 0.0 Max Worker Threads 0.0 Network Threads 0.0 Max Network Threads 0.0 (8 row(s) affected)
The thread information section contains network information and thread information.
Statistic Value ------------------------------- ----- RA Pages Found in Cache 0.0 RA Pages Placed in Cache 0.0 RA PhysicalI/O 0.0 Used Slots 0.0 (4 row(s) affected)
The read ahead section contains information about read ahead access ( where SQL Server reads data pages before it has been requested).
Spinlock Name Collisions Spins Spins/Collision ---------------- ---------- ----------- --------------- QP_INIT 0 0 0.0 MISC 0 0 0.0 EXT_CACHE 0 0 0.0 DBT_HASH 0 0 0.0 LOCK_FREE_LIST 0 0 0.0 DES_HASH 0 0 0.0 BUF_HASH 0 0 0.0 PSS_XDES 0 0 0.0 PSS 0 0 0.0 . . . SQL_MGR 0 0 0.0 DROP_TEMPO 0 0 0.0 NHASH_BKT 0 0 0.0 IHASH_BKT 0 0 0.0 CACHEOBJ_DBG 0 0 0.0 GHOST_HASH 0 0 0.0 GHOST_FREE 0 0 0.0 ISSRESOURCE 0 0 0.0 (60 row(s) affected)
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).
Wait Type Requests Wait Time Signal Wait Time ---------------- -------------- -------------- ---------------- MISCELLANEOUS 2223.0 3561460.0 3551857.0 LCK_M_SCH_S 0.0 0.0 0.0 LCK_M_SCH_M 0.0 0.0 0.0 LCK_M_IS 0.0 0.0 0.0 LCK_M_SIU 0.0 0.0 0.0 . . . PAGEIOLATCH_EX 6.0 291.0 0.0 PAGEIOLATCH_SH 93.0 1885.0 10.0 PAGEIOLATCH_UP 0.0 0.0 0.0 NETWORKIO 0.0 0.0 0.0 Total 2330.0 3563646.0 3551867.0
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.
dbcc outputbuffer
The
The output for this command will only display the first 512 bytes that were sent to the client.
dbcc outputbuffer command can be used to look at the network buffer that was last sent to a connection. The syntax is as
followsdbcc 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.
If the connection’s last command did not return the 512 bytes, the buffer will contain information from the previous command.
dbcc inputbuffer
The command
The syntax of
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
dbcc commands
[1] trace flags: A SQL Server dbcc option that changes the behavior of SQL Server.
