sys.dm_os_wait_stats of Different type of WAIT TYPE in SQL Server

The key Dynamic Management View (DMV) that helps us to understand wait stats is sys.dm_os_wait_stats; this DMV gives us all the information that we need to know regarding wait stats. However, the interpretation is left to us. This is a challenge as understanding wait stats can often be quite tricky. Anyway, we will cover few wait stats in one of the future articles. Today we will go over the basic understanding of the DMV.

The Official Book OnLine Reference for DMV is over here: sys.dm_os_wait_stats. I suggest you all to refer this for all the accuracy.

Following is a statement from the online book:

“Specific types of wait times during query execution can indicate bottlenecks or stall points within the query. Similarly, high wait times, or wait counts server wide can indicate bottlenecks or hot spots in interaction query interactions within the server instance.”

This is the statement which has inspired me to write this series.

Let us first run the following statement from DMV.

SELECT *
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC
GO

Above statement will show us few of the columns. Here it is quick explanation of each of the column.

wait_type – this is the name of the wait type. There can be three different kinds of wait types – resource, queue and external.

waiting_tasks_count – this incremental counter is a good indication of frequent the wait is happening. If this number is very high, it is good indication for us to investigate that particular wait type. It is quite possible that the wait time is considerably low, but the frequency of the wait is much high.

wait_time_ms – this is total wait accumulated for any type of wait. This is the total wait time and includes singal_wait_time_ms.

max_wait_time_ms – this indicates the maximum wait type ever occurred for that particular wait type. Using this, one can estimate the intensity of the wait type in past. Again, it is not necessary that this max wait time will occur every time; so do not over invest yourself here.

signal_wait_time_ms – this is the wait time when thread is marked as runnable and it gets to the running state. If the runnable queue is very long, you will find that this wait time becomes high.

Additionally, please note that this DMV does not show current wait type or wait stats. This is cumulative view of the all the wait stats since server (instance) restarted or wait stats have been cleared.

As my Experience following WAIT TYPE saw in SQL Server

List of WAIT TYPE (After Server Restart):

ABR
ASSEMBLY_LOAD
ASYNC_DISKPOOL_LOCK
ASYNC_IO_COMPLETION
ASYNC_NETWORK_IO
AUDIT_GROUPCACHE_LOCK
AUDIT_LOGINCACHE_LOCK
AUDIT_ON_DEMAND_TARGET_LOCK
AUDIT_XE_SESSION_MGR
BACKUP
BACKUP_OPERATOR
BACKUPBUFFER
BACKUPIO
BACKUPTHREAD
BAD_PAGE_PROCESS
BROKER_CONNECTION_RECEIVE_TASK
BROKER_ENDPOINT_STATE_MUTEX
BROKER_EVENTHANDLER
BROKER_INIT
BROKER_MASTERSTART
BROKER_RECEIVE_WAITFOR
BROKER_REGISTERALLENDPOINTS
BROKER_SERVICE
BROKER_SHUTDOWN
BROKER_TASK_STOP
BROKER_TO_FLUSH
BROKER_TRANSMITTER
BUILTIN_HASHKEY_MUTEX
CHANGE_TRACKING_WAITFORCHANGES
CHECK_PRINT_RECORD
CHECKPOINT_QUEUE
CHKPT
CLEAR_DB
CLR_AUTO_EVENT
CLR_CRST
CLR_JOIN
CLR_MANUAL_EVENT
CLR_MEMORY_SPY
CLR_MONITOR
CLR_RWLOCK_READER
CLR_RWLOCK_WRITER
CLR_SEMAPHORE
CLR_TASK_START
CLRHOST_STATE_ACCESS
CMEMTHREAD
COMMIT_TABLE
CXPACKET
CXROWSET_SYNC
DAC_INIT
DBMIRROR_DBM_EVENT
DBMIRROR_DBM_MUTEX
DBMIRROR_EVENTS_QUEUE
DBMIRROR_SEND
DBMIRROR_WORKER_QUEUE
DBMIRRORING_CMD
DEADLOCK_ENUM_MUTEX
DEADLOCK_TASK_SEARCH
DEBUG
DISABLE_VERSIONING
DISKIO_SUSPEND
DISPATCHER_QUEUE_SEMAPHORE
DLL_LOADING_MUTEX
DROPTEMP
DTC
DTC_ABORT_REQUEST
DTC_RESOLVE
DTC_STATE
DTC_TMDOWN_REQUEST
DTC_WAITFOR_OUTCOME
DUMP_LOG_COORDINATOR
DUMP_LOG_COORDINATOR_QUEUE
DUMPTRIGGER
EC
EE_PMOLOCK
EE_SPECPROC_MAP_INIT
ENABLE_VERSIONING
ERROR_REPORTING_MANAGER
EXCHANGE
EXECSYNC
EXECUTION_PIPE_EVENT_INTERNAL
FAILPOINT
FCB_REPLICA_READ
FCB_REPLICA_WRITE
FS_FC_RWLOCK
FS_GARBAGE_COLLECTOR_SHUTDOWN
FS_HEADER_RWLOCK
FS_LOGTRUNC_RWLOCK
FSA_FORCE_OWN_XACT
FSAGENT
FSTR_CONFIG_MUTEX
FSTR_CONFIG_RWLOCK
FT_COMPROWSET_RWLOCK
FT_IFTS_RWLOCK
FT_IFTS_SCHEDULER_IDLE_WAIT
FT_IFTSHC_MUTEX
FT_IFTSISM_MUTEX
FT_MASTER_MERGE
FT_METADATA_MUTEX
FT_RESTART_CRAWL
FULLTEXT GATHERER
GUARDIAN
HTTP_ENUMERATION
HTTP_START
IMPPROV_IOWAIT
INTERNAL_TESTING
IO_AUDIT_MUTEX
IO_COMPLETION
IO_RETRY
IOAFF_RANGE_QUEUE
KSOURCE_WAKEUP
KTM_ENLISTMENT
KTM_RECOVERY_MANAGER
KTM_RECOVERY_RESOLUTION
LATCH_DT
LATCH_EX
LATCH_KP
LATCH_NL
LATCH_SH
LATCH_UP
LAZYWRITER_SLEEP
LCK_M_BU
LCK_M_IS
LCK_M_IU
LCK_M_IX
LCK_M_RIn_NL
LCK_M_RIn_S
LCK_M_RIn_U
LCK_M_RIn_X
LCK_M_RS_S
LCK_M_RS_U
LCK_M_RX_S
LCK_M_RX_U
LCK_M_RX_X
LCK_M_S
LCK_M_SCH_M
LCK_M_SCH_S
LCK_M_SIU
LCK_M_SIX
LCK_M_U
LCK_M_UIX
LCK_M_X
LOGBUFFER
LOGGENERATION
LOGMGR
LOGMGR_FLUSH
LOGMGR_QUEUE
LOGMGR_RESERVE_APPEND
LOWFAIL_MEMMGR_QUEUE
METADATA_LAZYCACHE_RWLOCK
MISCELLANEOUS
MSQL_DQ
MSQL_XACT_MGR_MUTEX
MSQL_XACT_MUTEX
MSQL_XP
MSSEARCH
NET_WAITFOR_PACKET
NODE_CACHE_MUTEX
OLEDB
ONDEMAND_TASK_QUEUE
PAGEIOLATCH_DT
PAGEIOLATCH_EX
PAGEIOLATCH_KP
PAGEIOLATCH_NL
PAGEIOLATCH_SH
PAGEIOLATCH_UP
PAGELATCH_DT
PAGELATCH_EX
PAGELATCH_KP
PAGELATCH_NL
PAGELATCH_SH
PAGELATCH_UP
PARALLEL_BACKUP_QUEUE
PERFORMANCE_COUNTERS_RWLOCK
PREEMPTIVE_ABR
PREEMPTIVE_AUDIT_ACCESS_EVENTLOG
PREEMPTIVE_AUDIT_ACCESS_SECLOG
PREEMPTIVE_CLOSEBACKUPMEDIA
PREEMPTIVE_CLOSEBACKUPTAPE
PREEMPTIVE_CLOSEBACKUPVDIDEVICE
PREEMPTIVE_CLUSAPI_CLUSTERRESOURCECONTROL
PREEMPTIVE_COM_COCREATEINSTANCE
PREEMPTIVE_COM_COGETCLASSOBJECT
PREEMPTIVE_COM_CREATEACCESSOR
PREEMPTIVE_COM_DELETEROWS
PREEMPTIVE_COM_GETCOMMANDTEXT
PREEMPTIVE_COM_GETDATA
PREEMPTIVE_COM_GETNEXTROWS
PREEMPTIVE_COM_GETRESULT
PREEMPTIVE_COM_GETROWSBYBOOKMARK
PREEMPTIVE_COM_LBFLUSH
PREEMPTIVE_COM_LBLOCKREGION
PREEMPTIVE_COM_LBREADAT
PREEMPTIVE_COM_LBSETSIZE
PREEMPTIVE_COM_LBSTAT
PREEMPTIVE_COM_LBUNLOCKREGION
PREEMPTIVE_COM_LBWRITEAT
PREEMPTIVE_COM_QUERYINTERFACE
PREEMPTIVE_COM_RELEASE
PREEMPTIVE_COM_RELEASEACCESSOR
PREEMPTIVE_COM_RELEASEROWS
PREEMPTIVE_COM_RELEASESESSION
PREEMPTIVE_COM_RESTARTPOSITION
PREEMPTIVE_COM_SEQSTRMREAD
PREEMPTIVE_COM_SEQSTRMREADANDWRITE
PREEMPTIVE_COM_SETDATAFAILURE
PREEMPTIVE_COM_SETPARAMETERINFO
PREEMPTIVE_COM_SETPARAMETERPROPERTIES
PREEMPTIVE_COM_STRMLOCKREGION
PREEMPTIVE_COM_STRMSEEKANDREAD
PREEMPTIVE_COM_STRMSEEKANDWRITE
PREEMPTIVE_COM_STRMSETSIZE
PREEMPTIVE_COM_STRMSTAT
PREEMPTIVE_COM_STRMUNLOCKREGION
PREEMPTIVE_CONSOLEWRITE
PREEMPTIVE_CREATEPARAM
PREEMPTIVE_DEBUG
PREEMPTIVE_DFSADDLINK
PREEMPTIVE_DFSLINKEXISTCHECK
PREEMPTIVE_DFSLINKHEALTHCHECK
PREEMPTIVE_DFSREMOVELINK
PREEMPTIVE_DFSREMOVEROOT
PREEMPTIVE_DFSROOTFOLDERCHECK
PREEMPTIVE_DFSROOTINIT
PREEMPTIVE_DFSROOTSHARECHECK
PREEMPTIVE_DTC_ABORT
PREEMPTIVE_DTC_ABORTREQUESTDONE
PREEMPTIVE_DTC_BEGINTRANSACTION
PREEMPTIVE_DTC_COMMITREQUESTDONE
PREEMPTIVE_DTC_ENLIST
PREEMPTIVE_DTC_PREPAREREQUESTDONE
PREEMPTIVE_FILESIZEGET
PREEMPTIVE_FSAOLEDB_ABORTTRANSACTION
PREEMPTIVE_FSAOLEDB_COMMITTRANSACTION
PREEMPTIVE_FSAOLEDB_STARTTRANSACTION
PREEMPTIVE_FSRECOVER_UNCONDITIONALUNDO
PREEMPTIVE_GETRMINFO
PREEMPTIVE_LOCKMONITOR
PREEMPTIVE_MSS_RELEASE
PREEMPTIVE_ODBCOPS
PREEMPTIVE_OLE_UNINIT
PREEMPTIVE_OLEDB_ABORTORCOMMITTRAN
PREEMPTIVE_OLEDB_ABORTTRAN
PREEMPTIVE_OLEDB_GETDATASOURCE
PREEMPTIVE_OLEDB_GETLITERALINFO
PREEMPTIVE_OLEDB_GETPROPERTIES
PREEMPTIVE_OLEDB_GETPROPERTYINFO
PREEMPTIVE_OLEDB_GETSCHEMALOCK
PREEMPTIVE_OLEDB_JOINTRANSACTION
PREEMPTIVE_OLEDB_RELEASE
PREEMPTIVE_OLEDB_SETPROPERTIES
PREEMPTIVE_OLEDBOPS
PREEMPTIVE_OS_ACCEPTSECURITYCONTEXT
PREEMPTIVE_OS_ACQUIRECREDENTIALSHANDLE
PREEMPTIVE_OS_AUTHENTICATIONOPS
PREEMPTIVE_OS_AUTHORIZATIONOPS
PREEMPTIVE_OS_AUTHZGETINFORMATIONFROMCONTEXT
PREEMPTIVE_OS_AUTHZINITIALIZECONTEXTFROMSID
PREEMPTIVE_OS_AUTHZINITIALIZERESOURCEMANAGER
PREEMPTIVE_OS_BACKUPREAD
PREEMPTIVE_OS_CLOSEHANDLE
PREEMPTIVE_OS_CLUSTEROPS
PREEMPTIVE_OS_COMOPS
PREEMPTIVE_OS_COMPLETEAUTHTOKEN
PREEMPTIVE_OS_COPYFILE
PREEMPTIVE_OS_CREATEDIRECTORY
PREEMPTIVE_OS_CREATEFILE
PREEMPTIVE_OS_CRYPTACQUIRECONTEXT
PREEMPTIVE_OS_CRYPTIMPORTKEY
PREEMPTIVE_OS_CRYPTOPS
PREEMPTIVE_OS_DECRYPTMESSAGE
PREEMPTIVE_OS_DELETEFILE
PREEMPTIVE_OS_DELETESECURITYCONTEXT
PREEMPTIVE_OS_DEVICEIOCONTROL
PREEMPTIVE_OS_DEVICEOPS
PREEMPTIVE_OS_DIRSVC_NETWORKOPS
PREEMPTIVE_OS_DISCONNECTNAMEDPIPE
PREEMPTIVE_OS_DOMAINSERVICESOPS
PREEMPTIVE_OS_DSGETDCNAME
PREEMPTIVE_OS_DTCOPS
PREEMPTIVE_OS_ENCRYPTMESSAGE
PREEMPTIVE_OS_FILEOPS
PREEMPTIVE_OS_FINDFILE
PREEMPTIVE_OS_FLUSHFILEBUFFERS
PREEMPTIVE_OS_FORMATMESSAGE
PREEMPTIVE_OS_FREECREDENTIALSHANDLE
PREEMPTIVE_OS_FREELIBRARY
PREEMPTIVE_OS_GENERICOPS
PREEMPTIVE_OS_GETADDRINFO
PREEMPTIVE_OS_GETCOMPRESSEDFILESIZE
PREEMPTIVE_OS_GETDISKFREESPACE
PREEMPTIVE_OS_GETFILEATTRIBUTES
PREEMPTIVE_OS_GETFILESIZE
PREEMPTIVE_OS_GETLONGPATHNAME
PREEMPTIVE_OS_GETPROCADDRESS
PREEMPTIVE_OS_GETVOLUMENAMEFORVOLUMEMOUNTPOINT
PREEMPTIVE_OS_GETVOLUMEPATHNAME
PREEMPTIVE_OS_INITIALIZESECURITYCONTEXT
PREEMPTIVE_OS_LIBRARYOPS
PREEMPTIVE_OS_LOADLIBRARY
PREEMPTIVE_OS_LOGONUSER
PREEMPTIVE_OS_LOOKUPACCOUNTSID
PREEMPTIVE_OS_MESSAGEQUEUEOPS
PREEMPTIVE_OS_MOVEFILE
PREEMPTIVE_OS_NETGROUPGETUSERS
PREEMPTIVE_OS_NETLOCALGROUPGETMEMBERS
PREEMPTIVE_OS_NETUSERGETGROUPS
PREEMPTIVE_OS_NETUSERGETLOCALGROUPS
PREEMPTIVE_OS_NETUSERMODALSGET
PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICY
PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICYFREE
PREEMPTIVE_OS_OPENDIRECTORY
PREEMPTIVE_OS_PDH_WMI_INIT
PREEMPTIVE_OS_PIPEOPS
PREEMPTIVE_OS_PROCESSOPS
PREEMPTIVE_OS_QUERYREGISTRY
PREEMPTIVE_OS_QUERYSECURITYCONTEXTTOKEN
PREEMPTIVE_OS_REMOVEDIRECTORY
PREEMPTIVE_OS_REPORTEVENT
PREEMPTIVE_OS_REVERTTOSELF
PREEMPTIVE_OS_RSFXDEVICEOPS
PREEMPTIVE_OS_SECURITYOPS
PREEMPTIVE_OS_SERVICEOPS
PREEMPTIVE_OS_SETENDOFFILE
PREEMPTIVE_OS_SETFILEPOINTER
PREEMPTIVE_OS_SETFILEVALIDDATA
PREEMPTIVE_OS_SETNAMEDSECURITYINFO
PREEMPTIVE_OS_SQLCLROPS
PREEMPTIVE_OS_SQMLAUNCH
PREEMPTIVE_OS_VERIFYSIGNATURE
PREEMPTIVE_OS_VSSOPS
PREEMPTIVE_OS_WAITFORSINGLEOBJECT
PREEMPTIVE_OS_WINSOCKOPS
PREEMPTIVE_OS_WRITEFILE
PREEMPTIVE_OS_WRITEFILEGATHER
PREEMPTIVE_OS_WSASETLASTERROR
PREEMPTIVE_REENLIST
PREEMPTIVE_RESIZELOG
PREEMPTIVE_ROLLFORWARDREDO
PREEMPTIVE_ROLLFORWARDUNDO
PREEMPTIVE_SB_STOPENDPOINT
PREEMPTIVE_SERVER_STARTUP
PREEMPTIVE_SETRMINFO
PREEMPTIVE_SHAREDMEM_GETDATA
PREEMPTIVE_SNIOPEN
PREEMPTIVE_SOSHOST
PREEMPTIVE_SOSTESTING
PREEMPTIVE_STARTRM
PREEMPTIVE_STREAMFCB_CHECKPOINT
PREEMPTIVE_STREAMFCB_RECOVER
PREEMPTIVE_STRESSDRIVER
PREEMPTIVE_TESTING
PREEMPTIVE_TRANSIMPORT
PREEMPTIVE_UNMARSHALPROPAGATIONTOKEN
PREEMPTIVE_VSS_CREATESNAPSHOT
PREEMPTIVE_VSS_CREATEVOLUMESNAPSHOT
PREEMPTIVE_XE_CALLBACKEXECUTE
PREEMPTIVE_XE_DISPATCHER
PREEMPTIVE_XE_ENGINEINIT
PREEMPTIVE_XE_GETTARGETSTATE
PREEMPTIVE_XE_SESSIONCOMMIT
PREEMPTIVE_XE_TARGETFINALIZE
PREEMPTIVE_XE_TARGETINIT
PREEMPTIVE_XE_TIMERRUN
PREEMPTIVE_XETESTING
PRINT_ROLLBACK_PROGRESS
QPJOB_KILL
QPJOB_WAITFOR_ABORT
QRY_MEM_GRANT_INFO_MUTEX
QUERY_ERRHDL_SERVICE_DONE
QUERY_EXECUTION_INDEX_SORT_EVENT_OPEN
QUERY_NOTIFICATION_MGR_MUTEX
QUERY_NOTIFICATION_SUBSCRIPTION_MUTEX
QUERY_NOTIFICATION_TABLE_MGR_MUTEX
QUERY_NOTIFICATION_UNITTEST_MUTEX
QUERY_OPTIMIZER_PRINT_MUTEX
QUERY_TRACEOUT
QUERY_WAIT_ERRHDL_SERVICE
RECOVER_CHANGEDB
REPL_CACHE_ACCESS
REPL_HISTORYCACHE_ACCESS
REPL_SCHEMA_ACCESS
REPL_TRANHASHTABLE_ACCESS
REPLICA_WRITES
REQUEST_DISPENSER_PAUSE
REQUEST_FOR_DEADLOCK_SEARCH
RESMGR_THROTTLED
RESOURCE_QUEUE
RESOURCE_SEMAPHORE
RESOURCE_SEMAPHORE_MUTEX
RESOURCE_SEMAPHORE_QUERY_COMPILE
RESOURCE_SEMAPHORE_SMALL_QUERY
RG_RECONFIG
SCAN_CHAR_HASH_ARRAY_INITIALIZATION
SEC_DROP_TEMP_KEY
SECURITY_MUTEX
SEQUENTIAL_GUID
SERVER_IDLE_CHECK
SERVER_RECONFIGURE
SHUTDOWN
SLEEP_BPOOL_FLUSH
SLEEP_DBSTARTUP
SLEEP_DCOMSTARTUP
SLEEP_MSDBSTARTUP
SLEEP_SYSTEMTASK
SLEEP_TASK
SLEEP_TEMPDBSTARTUP
SNI_CRITICAL_SECTION
SNI_HTTP_WAITFOR_0_DISCON
SNI_LISTENER_ACCESS
SNI_TASK_COMPLETION
SOAP_READ
SOAP_WRITE
SOS_CALLBACK_REMOVAL
SOS_DISPATCHER_MUTEX
SOS_LOCALALLOCATORLIST
SOS_MEMORY_USAGE_ADJUSTMENT
SOS_OBJECT_STORE_DESTROY_MUTEX
SOS_PROCESS_AFFINITY_MUTEX
SOS_RESERVEDMEMBLOCKLIST
SOS_SCHEDULER_YIELD
SOS_SMALL_PAGE_ALLOC
SOS_STACKSTORE_INIT_MUTEX
SOS_SYNC_TASK_ENQUEUE_EVENT
SOS_VIRTUALMEMORY_LOW
SOSHOST_EVENT
SOSHOST_INTERNAL
SOSHOST_MUTEX
SOSHOST_RWLOCK
SOSHOST_SEMAPHORE
SOSHOST_SLEEP
SOSHOST_TRACELOCK
SOSHOST_WAITFORDONE
SQLCLR_APPDOMAIN
SQLCLR_ASSEMBLY
SQLCLR_DEADLOCK_DETECTION
SQLCLR_QUANTUM_PUNISHMENT
SQLSORT_NORMMUTEX
SQLSORT_SORTMUTEX
SQLTRACE_FILE_BUFFER
SQLTRACE_FILE_READ_IO_COMPLETION
SQLTRACE_FILE_WRITE_IO_COMPLETION
SQLTRACE_INCREMENTAL_FLUSH_SLEEP
SQLTRACE_PENDING_BUFFER_WRITERS
SQLTRACE_SHUTDOWN
SQLTRACE_WAIT_ENTRIES
SRVPROC_SHUTDOWN
TEMPOBJ
THREADPOOL
TIMEPRIV_TIMEPERIOD
TRACE_EVTNOTIF
TRACEWRITE
TRAN_MARKLATCH_DT
TRAN_MARKLATCH_EX
TRAN_MARKLATCH_KP
TRAN_MARKLATCH_NL
TRAN_MARKLATCH_SH
TRAN_MARKLATCH_UP
TRANSACTION_MUTEX
UTIL_PAGE_ALLOC
VIA_ACCEPT
VIEW_DEFINITION_MUTEX
WAIT_FOR_RESULTS
WAITFOR
WAITFOR_TASKSHUTDOWN
WAITSTAT_MUTEX
WCC
WORKTBL_DROP
WRITE_COMPLETION
WRITELOG
XACT_OWN_TRANSACTION
XACT_RECLAIM_SESSION
XACTLOCKINFO
XACTWORKSPACE_MUTEX
XE_BUFFERMGR_ALLPROCESSED_EVENT
XE_BUFFERMGR_FREEBUF_EVENT
XE_DISPATCHER_CONFIG_SESSION_LIST
XE_DISPATCHER_JOIN
XE_DISPATCHER_WAIT
XE_MODULEMGR_SYNC
XE_OLS_LOCK
XE_PACKAGE_LOCK_BACKOFF
XE_SERVICES_EVENTMANUAL
XE_SERVICES_MUTEX
XE_SERVICES_RWLOCK
XE_SESSION_CREATE_SYNC
XE_SESSION_FLUSH
XE_SESSION_SYNC
XE_STM_CREATE
XE_TIMER_EVENT
XE_TIMER_MUTEX
XE_TIMER_TASK_DONE