Monitoring SQL 2005 Performance Statistics With The DBCC SqlPerf Command
The SqlPerf Database Consistency Check (DBCC) command most of which is undocumented with the exception of the LogSpace option in the SQL Books On Line (BOL) can be used to monitor the performance statistics of your SQL 2005 database.
Here you will find brief descriptions and examples of each of the DBCC SqlPerf Commands to use on your SQL 2005 server.
DBCC SqlPerf (LogSpace)
The LogSpace option retrieves information about the transaction logs for all of the databases on the server and it writes the following: Database Name, Log Size (MB), Log Space Used (%) and Status.
Example: Exec ('DBCC SqlPerf (logspace)')
DBCC SqlPerf (UmsStats)
The UmsStats option retrieves information for your threads and it writes the following: Statistic and Value which includes statistic information on the following: Scheduler ID, Num users, Num runnable, Num workers, Idle workers, Work queued, Cntxt switches, Cntxt switches(idle), cheduler ID, Num users, Num runnable, Num workers, Idle workers, Work queued, Cntxt switches, Cntxt switches(idle), Scheduler Switches
and Total Work
Example: Exec ('DBCC SqlPerf (UmsStats)')
DBCC SqlPerf (WaitStats)
The WaitStats option retrieves information for the servers resources wait types and it writes the following: Wait Type, Requests, Wait Time and Signal Wait Time
Example: Exec ('DBCC SqlPerf (WaitStats)')
DBCC SqlPerf (IoStats)
The IoStats option retrieves information about your server’s outstanding reads and writes and it writes the following: Statistic and Value which includes statistic information on the following: Reads Outstanding and Writes Outstanding.
Example: Exec ('DBCC SqlPerf (IoStats)')
DBCC SqlPerf (RaStats)
The RaStats option retrieves information about your servers read-ahead statistics and it writes the following: Statistic and Value which includes information on the following: RA Pages Found in Cache, RA Pages Placed in Cache, RA Physical IO and Used Slots
Example: Exec ('DBCC SqlPerf (RaStats)')
DBCC SqlPerf (Threads)
The Treads option retrieves information for the server’s currently running threads and writes and it writes the following: Spid, Thread ID, Status, LoginName, IO, CPU and MemUsage
Example: Exec ('DBCC SqlPerf (Threads)')
