Monitoring SQL  «Prev  Next»

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:
  • 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:
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 dbcc perfmon is as follows:
dbcc perfmon
The 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 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.

Apply, Filter, Sort
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.