13 January 2021

SQL Server Administration: Undocumented IV (DBCC SQLPERF)

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)

Output:
StatisticValue
Node Id0
Avg Sched Load5
Sched Switches6903
Sched Pass6721358
IO Comp Passes11334
Scheduler ID0
    online1
    num tasks6
    num runnable0
    num workers9
    active workers6
    work queued0
    cntxt switches7125444
    cntxt switches(idle)9898903
    preemptive switches2304
Scheduler ID1
    online1
    num tasks6
    num runnable0
    num workers9
    active workers5
    work queued0
    cntxt switches3370432
    cntxt switches(idle)4427991
    preemptive switches22729

The fields have the following meaning: 
StatisticDescription
Node Id
Avg Sched Load
Sched SwitchesThe number of switches between schedulers
Sched Pass
IO Comp Passes
Scheduler IDThe scheduler's zero-based ID number
num tasksThe number of tasks associated with the scheduler
num runnableThe number of workers on the runnable list
num workersThe total number of workers associated with the scheduler
idle workersThe number of idle workers
work queuedThe number of items waiting to be processed in the work queue
cntxt switchesThe 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)

Output (just the first records):
SpidThread IDStatusLoginNameIOCPUMemUsage
111228backgroundsa000
212572backgroundsa000
312576backgroundsa000
411884backgroundsa000
512964backgroundsa000
612960backgroundsa004
712968backgroundsa000

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)

Output:
StatisticValue
Reads Outstanding0
Writes Outstanding0

Network Statistics

By providing 'netstats' as parameter, the utility returns as result network-related statistics:

--  network statistics
DBCC SQLPERF(netstats)

Output:
StatisticValue
Network Reads6976
Network Writes9036
Network Bytes Read5318957
Network Bytes Written2,222512E+07
Command Queue Length0
Max Command Queue Length0
Worker Threads0
Max Worker Threads0
Network Threads0
Max Network Threads0

Read Ahead Statistics

By providing 'rastats' as parameter, the utility returns read-ahead statistics:

--  read ahead statistics
DBCC SQLPERF(rastats)

Output
StatisticValue
RA Pages Found in Cache0
RA Pages Placed in Cache0
RA Physical IO0
Used Slots0

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)

Output:
Spinlock NameCollisionsSpinsSpins/CollisionSleep Time (ms)Backoffs
LOCK_RW_TEST00000
LOCK_RW_SECURITY_CACHE711210500296,0619054
LOCK_RW_CMED_HASH_SET5175035001
LOCK_RW_ABTX_HASH_SET00000
LOCK_RW_RBIO_REQ00000

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)

Output (only a few records):
Wait TypeRequestsWait TimeSignal Wait Time
LCK_M_SCH_M185973
LCK_M_S13168950
PAGEIOLATCH_SH17893856825
PAGEIOLATCH_UP1181010
PAGEIOLATCH_EX7361049016

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)

DBCC PERFMON provides in a single call the IO, network, read ahead, spinlocks, respectively the wait statistics in distinct resultsets:

-- performance statistics
DBCC PERFMON

No comments:

Related Posts Plugin for WordPress, Blogger...

About Me

My photo
Koeln, NRW, Germany
IT Professional with more than 24 years experience in IT in the area of full life-cycle of Web/Desktop/Database Applications Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP implementations & support, Team/Project/IT Management, etc.