SQLConfiguration SQLConfiguration


Monitoring SQL  «Prev 

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.