Besides the documented LOGSPACE parameter (see previous post), DBCC SQLPERF utility has several undocumented parameters which allow providing statistics about schedulers, threads, spinlocks, IO, network, read-aheads, respectively waits.
Scheduler Statistics
By providing 'umsstats' as parameter, the utility returns as result the visible UMS schedulers on the system:
-- visible UMS schedulers DBCC SQLPERF(umsstats)
Statistic | Value |
Node Id | 0 |
Avg Sched Load | 5 |
Sched Switches | 6903 |
Sched Pass | 6721358 |
IO Comp Passes | 11334 |
Scheduler ID | 0 |
online | 1 |
num tasks | 6 |
num runnable | 0 |
num workers | 9 |
active workers | 6 |
work queued | 0 |
cntxt switches | 7125444 |
cntxt switches(idle) | 9898903 |
preemptive switches | 2304 |
Scheduler ID | 1 |
online | 1 |
num tasks | 6 |
num runnable | 0 |
num workers | 9 |
active workers | 5 |
work queued | 0 |
cntxt switches | 3370432 |
cntxt switches(idle) | 4427991 |
preemptive switches | 22729 |
Statistic | Description |
Node Id | |
Avg Sched Load | |
Sched Switches | The number of switches between schedulers |
Sched Pass | |
IO Comp Passes | |
Scheduler ID | The scheduler's zero-based ID number |
num tasks | The number of tasks associated with the scheduler |
num runnable | The number of workers on the runnable list |
num workers | The total number of workers associated with the scheduler |
idle workers | The number of idle workers |
work queued | The number of items waiting to be processed in the work queue |
cntxt switches | The number of switches between workers for the scheduler |
cntxt switches(idle) | The number of times the idle loop was switched into |
The functionality is useful when one suspects that there are issues related to the visible schedulers.
Detailed information about the schedulers can be found also via the sys.dm_os_schedulers DMV.
Threads Statistics
By providing 'threads' as parameter, the utility returns as result the threads created in the system:
-- thread statistics DBCC SQLPERF(threads)
Spid | Thread ID | Status | LoginName | IO | CPU | MemUsage |
1 | 11228 | background | sa | 0 | 0 | 0 |
2 | 12572 | background | sa | 0 | 0 | 0 |
3 | 12576 | background | sa | 0 | 0 | 0 |
4 | 11884 | background | sa | 0 | 0 | 0 |
5 | 12964 | background | sa | 0 | 0 | 0 |
6 | 12960 | background | sa | 0 | 0 | 4 |
7 | 12968 | background | sa | 0 | 0 | 0 |
Detailed information about the schedulers can be found also via the sys.dm_os_threads DMV.
IO Statistics
By providing 'iostats' as parameter, the utility returns as result a count of the outstanding reads, respectively writes:
-- IO statistics DBCC SQLPERF(iostats)
Statistic | Value |
Reads Outstanding | 0 |
Writes Outstanding | 0 |
Network Statistics
By providing 'netstats' as parameter, the utility returns as result network-related statistics:
-- network statistics DBCC SQLPERF(netstats)
Statistic | Value |
Network Reads | 6976 |
Network Writes | 9036 |
Network Bytes Read | 5318957 |
Network Bytes Written | 2,222512E+07 |
Command Queue Length | 0 |
Max Command Queue Length | 0 |
Worker Threads | 0 |
Max Worker Threads | 0 |
Network Threads | 0 |
Max Network Threads | 0 |
Read Ahead Statistics
By providing 'rastats' as parameter, the utility returns read-ahead statistics:
-- read ahead statistics DBCC SQLPERF(rastats)
Statistic | Value |
RA Pages Found in Cache | 0 |
RA Pages Placed in Cache | 0 |
RA Physical IO | 0 |
Used Slots | 0 |
Spinlock Statistics
By providing 'spinlockstats' as parameter, the utility returns the spinlock statistics, where a spinlock is a a lightweight synchronization object used to serialize access to data structures which are typically held for a short period of time:
-- spinlock statistics DBCC SQLPERF(spinlockstats)
Spinlock Name | Collisions | Spins | Spins/Collision | Sleep Time (ms) | Backoffs |
LOCK_RW_TEST | 0 | 0 | 0 | 0 | 0 |
LOCK_RW_SECURITY_CACHE | 711 | 210500 | 296,0619 | 0 | 54 |
LOCK_RW_CMED_HASH_SET | 5 | 1750 | 350 | 0 | 1 |
LOCK_RW_ABTX_HASH_SET | 0 | 0 | 0 | 0 | 0 |
LOCK_RW_RBIO_REQ | 0 | 0 | 0 | 0 | 0 |
Detailed information about the spinlock stats can be found also via the sys.dm_os_spinlock_stats DMV.
Wait Statistics
By providing 'waitstats' as parameter, the utility returns the available wait statistics:
-- wait statistics DBCC SQLPERF(waitstats)
Wait Type | Requests | Wait Time | Signal Wait Time |
LCK_M_SCH_M | 18 | 597 | 3 |
LCK_M_S | 13 | 16895 | 0 |
PAGEIOLATCH_SH | 1789 | 38568 | 25 |
PAGEIOLATCH_UP | 118 | 101 | 0 |
PAGEIOLATCH_EX | 736 | 10490 | 16 |
Detailed information about the wait stats can be found also via sys.dm_os_wait_stats DMV.
Notes:
As Microsoft warns, the undocumented features shouldn't be used into production environments as they will be deprecated in future versions. Instead should be used the documented DMVs, when available.
All objects mentioned above require VIEW SERVER STATE permissions.
The DBCC SQLPERF utility allows resetting the latch, spinlock, respectively the wait statistics by providing the following parameters (see the SQL Docs for more information):
-- resetting the latch statistics DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) -- resetting the spinlock statistics DBCC SQLPERF ('sys.dm_os_spinlock_stats', CLEAR); -- resetting the wait statistics DBCC SQLPERF('sys.dm_os_latch_stats', CLEAR)
-- performance statistics DBCC PERFMON
No comments:
Post a Comment