WAIT STATs cheat sheet for SQL Server Performance

WAIT STATs cheat sheet for SQL Server Performance

When you have Performance issues you can tell a lot from the Wait Stats. But there are so many that I created a list of the important ones.

Run this to get the Wait Stats

SELECT *
FROM sys.dm_os_wait_stats; -- Local Servers

--To query a dynamic management view or function requires SELECT permission on object
--and VIEW SERVER STATE or VIEW DATABASE STATE permission.

SELECT *
FROM sys.dm_pdw_nodes_os_wait_stats; --This is for Azure/Cloud.

Most Wait Stats don't have major impact on Performance. But here are the ones that are most important:

The Performance killers:

ASYNC_NETWORK_IO - When this is high it is the App lagging and throttleing performance. It means SQLis waiting on App to take the data. So when they are saying the Database is slow this lets you blame the APP ;-)

BROKER_RECEIVE_WAITFOR        Receiving mail issue but is very rare.

CXPACKET Check the Cost Threshold for Parallelism and adjust than or = to one less than   number of cores.

MAXDOP.. Check the predicates, and tune the indexes to use the predicates. Try setting MAXDOP to one less

IO_COMPLETION  It is waiting for thread synchronization. Check for incompleate where clause. If you see this with PAGEIOLATCH_* check for table scans and bad query plan.

                                         LATCH_DT Exclusive latch, blocks other threads from writing to or reading from the referenced structure.

LATCH_EX Exclusive latch, blocks other threads from writing to or reading from the referenced structure.

LATCH_KP Lightweight works with others except DT

LATCH_NL Null Latch no longer used But I found it in a 2014 result =0

LATCH_SH Shared latch, required to read a page structure.

LATCH_UP Update latch, is compatible with SH (Shared latch) and KP, but no others and therefore will not allow an EX latch to write to the referenced structure.

All LCKs look to Indexes too few, Missing or unused.

LCK_M_BU "
LCK_M_BU_ABORT_BLOCKERS
LCK_M_BU_LOW_PRIORITY
LCK_M_IS "
LCK_M_IS_ABORT_BLOCKERS
LCK_M_IS_LOW_PRIORITY
LCK_M_IU "
LCK_M_IU_ABORT_BLOCKERS
LCK_M_IU_LOW_PRIORITY
LCK_M_IX "
LCK_M_IX_ABORT_BLOCKERS
LCK_M_IX_LOW_PRIORITY
LCK_M_RIn_NL "
LCK_M_RIn_NL_ABORT_BLOCKERS
LCK_M_RIn_NL_LOW_PRIORITY
LCK_M_RIn_S "
LCK_M_RIn_S_ABORT_BLOCKERS
LCK_M_RIn_S_LOW_PRIORITY
LCK_M_RIn_U "
LCK_M_RIn_U_ABORT_BLOCKERS
LCK_M_RIn_U_LOW_PRIORITY
LCK_M_RIn_X "
LCK_M_RIn_X_ABORT_BLOCKERS
LCK_M_RIn_X_LOW_PRIORITY
LCK_M_RS_S "
LCK_M_RS_S_ABORT_BLOCKERS
LCK_M_RS_S_LOW_PRIORITY
LCK_M_RS_U "
LCK_M_RS_U_ABORT_BLOCKERS
LCK_M_RS_U_LOW_PRIORITY
LCK_M_RX_S "
LCK_M_RX_S_ABORT_BLOCKERS
LCK_M_RX_S_LOW_PRIORITY
LCK_M_RX_U "
LCK_M_RX_U_ABORT_BLOCKERS
LCK_M_RX_U_LOW_PRIORITY
LCK_M_RX_X "
LCK_M_RX_X_ABORT_BLOCKERS
LCK_M_RX_X_LOW_PRIORITY
LCK_M_S "
LCK_M_S_ABORT_BLOCKERS
LCK_M_S_LOW_PRIORITY
LCK_M_SCH_M "
LCK_M_SCH_M_ABORT_BLOCKERS
LCK_M_SCH_M_LOW_PRIORITY
LCK_M_SCH_S
LCK_M_SCH_S_ABORT_BLOCKERS
LCK_M_SCH_S_LOW_PRIORITY
LCK_M_SIU "
LCK_M_SIU_ABORT_BLOCKERS
LCK_M_SIU_LOW_PRIORITY
LCK_M_SIX "
LCK_M_SIX_ABORT_BLOCKERS
LCK_M_SIX_LOW_PRIORITY
LCK_M_U "
LCK_M_U_ABORT_BLOCKERS
LCK_M_U_LOW_PRIORITY
LCK_M_UIX "
LCK_M_UIX_ABORT_BLOCKERS
LCK_M_UIX_LOW_PRIORITY
LCK_M_X "
LCK_M_X_ABORT_BLOCKERS
LCK_M_X_LOW_PRIORITY

OLEDB       Can indicate that the SPID is waiting for remote procedure calls, linked server queries, BULK INSERT commands or full-search queries. Look to see if systems you are linked to are up and responding normally.

PAGEIOLATCH_NL PAGEIOLATCH_* is IO related contention

PAELATCH_NL PAGELATCH_* SQL Server is waiting to read data pages from storage. Memory access contention. This used t be common but rarely if ever seen with a modern SAN.

RESOURCE_SEMAPHORE      Waiting for query execution memory. In other words you are out of Memory. I don't see this as much as I used to with cheap RAM.

SOS_SCHEDULER_YIELD    This means that the Query needs more CPU Cores. Look for parallelism inhibitors. Check what Maxdop is set to. If it is set to 1 or two you are likely limiting parallelism too much. You can see this in PerfMon if you look at all the Cores you might see one running at 100% and the others doing almost nothing. So spread the load through increasing Parallelism.

UDF - Take them out and rewrite code. UDFs inhibit Parallelism

TRAN_MARKLATCH_NL Tran_Mark_Latch_* are used for synchronization of commits.

WAIT_FOR_RESULTS    Waiting for a query notification to be triggered.

Various types in the order I found them on my LapTop:

MISCELLANEOUS

ABR

AM_INDBUILD_ALLOCATION

AM_SCHEMAMGR_UNSHARED_CACHE

ASSEMBLY_LOAD

ASYNC_DISKPOOL_LOCK

ASYNC_IO_COMPLETION

ASYNC_NETWORK_IO   The App cannot take the result set fast enough

ASYNC_OP_COMPLETION

ASYNC_OP_CONTEXT_READ

ASYNC_OP_CONTEXT_WRITE

AUDIT_GROUPCACHE_LOCK

AUDIT_LOGINCACHE_LOCK

AUDIT_ON_DEMAND_TARGET_LOCK

AUDIT_XE_SESSION_MGR

BACKUP Slow backup device

BACKUP_OPERATOR

BACKUPBUFFER

BACKUPIO

BACKUPTHREAD

BAD_PAGE_PROCESS

BMPALLOCATION

BMPBUILD

BMPREPARTITION

BMPREPLICATION

BROKER_CONNECTION_RECEIVE_TASK

BROKER_DISPATCHER

BROKER_ENDPOINT_STATE_MUTEX

BROKER_EVENTHANDLER

BROKER_FORWARDER

BROKER_INIT

BROKER_MASTERSTART

BROKER_RECEIVE_WAITFOR        Receiving mail very rare

BROKER_REGISTERALLENDPOINTS

BROKER_SERVICE

BROKER_SHUTDOWN

BROKER_TASK_SHUTDOWN

BROKER_TASK_STOP

BROKER_TASK_SUBMIT

BROKER_TO_FLUSH

BROKER_TRANSMISSION_OBJECT

BROKER_TRANSMISSION_TABLE

BROKER_TRANSMISSION_WORK

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

COLUMNSTORE_BUILD_THROTTLE

COMMIT_TABLE

COUNTRECOVERYMGR

CREATE_DATINISERVICE

CXPACKET Check the Cost Threshold for Parallelism and adjust MAXDOP.. Check the predicates, and tune the indexes to use the predicates. Try setting MAXDOP to one less than or = to one less than number of cores.

CXROWSET_SYNC

DAC_INIT

DBCC_SCALE_OUT_EXPR_CACHE

DBMIRROR_DBM_EVENT

DBMIRROR_DBM_MUTEX

DBMIRROR_EVENTS_QUEUE

DBMIRROR_SEND

DBMIRROR_WORKER_QUEUE

DBMIRRORING_CMD

DBSEEDING_FLOWCONTROL

DBSEEDING_OPERATION

DBSTATE

DEADLOCK_ENUM_MUTEX

DEADLOCK_TASK_SEARCH

DEBUG

DIRTY_PAGE_POLL

DIRTY_PAGE_SYNC

DISABLE_VERSIONING

DISKIO_SUSPEND

DISPATCHER_PRIORITY_QUEUE_SEMAPHORE

DISPATCHER_QUEUE_SEMAPHORE

DLL_LOADING_MUTEX

DROP_DATABASE_TIMER_TASK

DROPTEMP

DTC             Not local

DTC_ABORT_REQUEST

DTC_RESOLVE

DTC_STATE          Not Local

DTC_TMDOWN_REQUEST

DTC_WAITFOR_OUTCOME

DTCPNTSYNC

DUMP_LOG_COORDINATOR

DUMP_LOG_COORDINATOR_QUEUE

DUMPTRIGGER

EC

EE_PMOLOCK

EE_SPECPROC_MAP_INIT

ENABLE_EMPTY_VERSIONING

ENABLE_VERSIONING

ERROR_REPORTING_MANAGER

EXCHANGE

EXECSYNC

EXECUTION_PIPE_EVENT_INTERNAL

FABRIC_HADR_TRANSPORT_CONNECTION

FABRIC_REPLICA_CONTROLLER_LIST

FABRIC_REPLICA_CONTROLLER_STATE_AND_CONFIG

FABRIC_REPLICA_PUBLISHER_EVENT_PUBLISH

FABRIC_REPLICA_PUBLISHER_SUBSCRIBER_LIST

FABRIC_WAIT_FOR_BUILD_REPLICA_EVENT_PROCESSING

FAILPOINT

FCB_REPLICA_READ

FCB_REPLICA_WRITE

FEATURE_SWITCHES_UPDATE

FFT_NSO_DB_KILL_FLAG

FFT_NSO_DB_LIST

FFT_NSO_FCB

FFT_NSO_FCB_FIND

FFT_NSO_FCB_PARENT

FFT_NSO_FCB_RELEASE_CACHED_ENTRIES

FFT_NSO_FILEOBJECT

FFT_NSO_TABLE_LIST

FFT_NTFS_STORE

FFT_RECOVERY

FFT_RSFX_COMM

FFT_RSFX_WAIT_FOR_MEMORY

FFT_STARTUP_SHUTDOWN

FFT_STORE_DB

FFT_STORE_ROWSET_LIST

FFT_STORE_TABLE

FILESTREAM_CACHE

FILESTREAM_CHUNKER

FILESTREAM_CHUNKER_INIT

FILESTREAM_FCB

FILESTREAM_FILE_OBJECT

FILESTREAM_WORKITEM_QUEUE

FILETABLE_SHUTDOWN

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_MASTER_MERGE_COORDINATOR

FT_METADATA_MUTEX

FT_PROPERTYLIST_CACHE

FT_RESTART_CRAWL

FULLTEXT GATHERER

GDMA_GET_RESOURCE_OWNER

GHOSTCLEANUPSYNCMGR

GUARDIAN

HADR_AG_MUTEX

HADR_AR_CRITICAL_SECTION_ENTRY

HADR_AR_MANAGER_MUTEX

HADR_AR_UNLOAD_COMPLETED

HADR_ARCONTROLLER_NOTIFICATIONS_SUBSCRIBER_LIST

HADR_BACKUP_BULK_LOCK

HADR_BACKUP_QUEUE

HADR_CLUSAPI_CALL

HADR_COMPRESSED_CACHE_SYNC

HADR_CONNECTIVITY_INFO

HADR_DATABASE_FLOW_CONTROL

HADR_DATABASE_VERSIONING_STATE

HADR_DATABASE_WAIT_FOR_RESTART

HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING

HADR_DB_COMMAND

HADR_DB_OP_COMPLETION_SYNC

HADR_DB_OP_START_SYNC

HADR_DBR_SUBSCRIBER

HADR_DBR_SUBSCRIBER_FILTER_LIST

HADR_DBSEEDING

HADR_DBSEEDING_LIST

HADR_DBSTATECHANGE_SYNC

HADR_FABRIC_CALLBACK

HADR_FILESTREAM_BLOCK_FLUSH

HADR_FILESTREAM_FILE_CLOSE

HADR_FILESTREAM_FILE_REQUEST

HADR_FILESTREAM_IOMGR

HADR_FILESTREAM_IOMGR_IOCOMPLETION

HADR_FILESTREAM_MANAGER

HADR_GROUP_COMMIT

HADR_LOGCAPTURE_SYNC

HADR_LOGCAPTURE_WAIT

HADR_LOGPROGRESS_SYNC

HADR_NOTIFICATION_DEQUEUE

HADR_NOTIFICATION_WORKER_EXCLUSIVE_ACCESS

HADR_NOTIFICATION_WORKER_STARTUP_SYNC

HADR_NOTIFICATION_WORKER_TERMINATION_SYNC

HADR_PARTNER_SYNC

HADR_READ_ALL_NETWORKS

HADR_RECOVERY_WAIT_FOR_CONNECTION

HADR_RECOVERY_WAIT_FOR_UNDO

HADR_REPLICAINFO_SYNC

HADR_SYNC_COMMIT

HADR_SYNCHRONIZING_THROTTLE

HADR_TDS_LISTENER_SYNC

HADR_TDS_LISTENER_SYNC_PROCESSING

HADR_TIMER_TASK

HADR_TRANSPORT_DBRLIST

HADR_TRANSPORT_FLOW_CONTROL

HADR_TRANSPORT_SESSION

HADR_WORK_POOL

HADR_WORK_QUEUE

HADR_XRF_STACK_ACCESS

HTBUILD

HTDELETE

HTMEMO

HTREINIT

HTREPARTITION

HTTP_ENUMERATION

HTTP_START

HTTP_STORAGE_CONNECTION

IMPPROV_IOWAIT

INTERNAL_TESTING

IO_AUDIT_MUTEX

IO_COMPLETION

IO_RETRY

IOAFF_RANGE_QUEUE

It is waiting for thread synchronization. Check for incompleate where clause. If you see this with PAGEIOLATCH_* check for table scans and bad query plan.

KSOURCE_WAKEUP

KTM_ENLISTMENT

KTM_RECOVERY_MANAGER

KTM_RECOVERY_RESOLUTION

LATCH_DT Exclusive latch, blocks other threads from writing to or reading from the referenced structure.

LATCH_EX Exclusive latch, blocks other threads from writing to or reading from the referenced structure.

LATCH_KP Lightweight works with others except DT

LATCH_NL Null Latch no longer used But I found it in a 2014 result =0

LATCH_SH Shared latch, required to read a page structure.

LATCH_UP Update latch, is compatible with SH (Shared latch) and KP, but no others and therefore will not allow an EX latch to write to the referenced structure.

LAZYWRITER_SLEEP

LCK_M_BU "

LCK_M_BU_ABORT_BLOCKERS

LCK_M_BU_LOW_PRIORITY

LCK_M_IS "

LCK_M_IS_ABORT_BLOCKERS

LCK_M_IS_LOW_PRIORITY

LCK_M_IU "

LCK_M_IU_ABORT_BLOCKERS

LCK_M_IU_LOW_PRIORITY

LCK_M_IX "

LCK_M_IX_ABORT_BLOCKERS

LCK_M_IX_LOW_PRIORITY

LCK_M_RIn_NL "

LCK_M_RIn_NL_ABORT_BLOCKERS

LCK_M_RIn_NL_LOW_PRIORITY

LCK_M_RIn_S "

LCK_M_RIn_S_ABORT_BLOCKERS

LCK_M_RIn_S_LOW_PRIORITY

LCK_M_RIn_U "

LCK_M_RIn_U_ABORT_BLOCKERS

LCK_M_RIn_U_LOW_PRIORITY

LCK_M_RIn_X "

LCK_M_RIn_X_ABORT_BLOCKERS

LCK_M_RIn_X_LOW_PRIORITY

LCK_M_RS_S "

LCK_M_RS_S_ABORT_BLOCKERS

LCK_M_RS_S_LOW_PRIORITY

LCK_M_RS_U "

LCK_M_RS_U_ABORT_BLOCKERS

LCK_M_RS_U_LOW_PRIORITY

LCK_M_RX_S "

LCK_M_RX_S_ABORT_BLOCKERS

LCK_M_RX_S_LOW_PRIORITY

LCK_M_RX_U "

LCK_M_RX_U_ABORT_BLOCKERS

LCK_M_RX_U_LOW_PRIORITY

LCK_M_RX_X "

LCK_M_RX_X_ABORT_BLOCKERS

LCK_M_RX_X_LOW_PRIORITY

LCK_M_S "

LCK_M_S_ABORT_BLOCKERS

LCK_M_S_LOW_PRIORITY

LCK_M_SCH_M "

LCK_M_SCH_M_ABORT_BLOCKERS

LCK_M_SCH_M_LOW_PRIORITY

LCK_M_SCH_S All LCKs look to Indexes to few or unused

LCK_M_SCH_S_ABORT_BLOCKERS

LCK_M_SCH_S_LOW_PRIORITY

LCK_M_SIU "

LCK_M_SIU_ABORT_BLOCKERS

LCK_M_SIU_LOW_PRIORITY

LCK_M_SIX "

LCK_M_SIX_ABORT_BLOCKERS

LCK_M_SIX_LOW_PRIORITY

LCK_M_U "

LCK_M_U_ABORT_BLOCKERS

LCK_M_U_LOW_PRIORITY

LCK_M_UIX "

LCK_M_UIX_ABORT_BLOCKERS

LCK_M_UIX_LOW_PRIORITY

LCK_M_X "

LCK_M_X_ABORT_BLOCKERS

LCK_M_X_LOW_PRIORITY

LOGBUFFER

LOGCAPTURE_LOGPOOLTRUNCPOINT

LOGGENERATION

LOGMGR

LOGMGR_FLUSH

LOGMGR_QUEUE

LOGMGR_RESERVE_APPEND

LOGPOOL_CACHESIZE

LOGPOOL_CONSUMER

LOGPOOL_CONSUMERSET

LOGPOOL_FREEPOOLS

LOGPOOL_MGRSET

LOGPOOL_REPLACEMENTSET

LOGPOOLREFCOUNTEDOBJECT_REFDONE

LOWFAIL_MEMMGR_QUEUE

MD_AGENT_YIELD

MD_LAZYCACHE_RWLOCK

MSQL_DQ

MSQL_XACT_MGR_MUTEX

MSQL_XACT_MUTEX

MSQL_XP

MSSEARCH

NET_WAITFOR_PACKET

NODE_CACHE_MUTEX

OLEDB       Can indicate that the SPID is waiting for remote procedure calls, linked server queries, BULK INSERT commands or full-search queries.

ONDEMAND_TASK_QUEUE

PAGEIOLATCH_DT "

PAGEIOLATCH_EX

PAGEIOLATCH_KP "

PAGEIOLATCH_NL PAGEIOLATCH_* is IO related contention

PAGEIOLATCH_SH

PAGEIOLATCH_UP

PAGELATCH_DT

PAGELATCH_EX

PAGELATCH_KP

PAGELATCH_NL PAGELATCH_* waiting to read data pages from storage. Memory access contention.

PAGELATCH_SH

PAGELATCH_UP

PARALLEL_BACKUP_QUEUE

PERFORMANCE_COUNTERS_RWLOCK

PHYSICAL_SEEDING_DMV

PREEMPTIVE_ABR

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_HADR_LEASE_MECHANISM

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_QUERYCONTEXTATTRIBUTES

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_SP_SERVER_DIAGNOSTICS

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_CX_FILE_OPEN

PREEMPTIVE_XE_CX_HTTP_CALL

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

PRU_ROLLBACK_DEFERRED

PWAIT_ALL_COMPONENTS_INITIALIZED

PWAIT_COOP_SCAN

PWAIT_EVENT_SESSION_INIT_MUTEX

PWAIT_HADR_ACTION_COMPLETED

PWAIT_HADR_CHANGE_NOTIFIER_TERMINATION_SYNC

PWAIT_HADR_CLUSTER_INTEGRATION

PWAIT_HADR_FAILOVER_COMPLETED

PWAIT_HADR_JOIN

PWAIT_HADR_OFFLINE_COMPLETED

PWAIT_HADR_ONLINE_COMPLETED

PWAIT_HADR_POST_ONLINE_COMPLETED

PWAIT_HADR_SERVER_READY_CONNECTIONS

PWAIT_HADR_WORKITEM_COMPLETED

PWAIT_LOG_CONSOLIDATION_IO

PWAIT_LOG_CONSOLIDATION_POLL

PWAIT_MD_LOGIN_STATS

PWAIT_MD_RELATION_CACHE

PWAIT_MD_SERVER_CACHE

PWAIT_MD_UPGRADE_CONFIG

PWAIT_PREEMPTIVE_AUDIT_ACCESS_WINDOWSLOG

PWAIT_QRY_BPMEMORY

PWAIT_REPLICA_ONLINE_INIT_MUTEX

PWAIT_RESOURCE_SEMAPHORE_FT_PARALLEL_QUERY_SYNC

PWAIT_SECURITY_CACHE_INVALIDATION

PWAIT_XTP_FSSTORAGE_MAINTENANCE

PWAIT_XTP_HOST_STORAGE_WAIT

QDS_ASYNC_CHECK_CONSISTENCY_TASK

QDS_ASYNC_PERSIST_TASK

QDS_ASYNC_PERSIST_TASK_START

QDS_BCKG_TASK

QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP

QDS_CTXS

QDS_DB_DISK

QDS_DYN_VECTOR

QDS_LOADDB

QDS_PERSIST_TASK_MAIN_LOOP_SLEEP

QDS_SHUTDOWN_QUEUE

QDS_STMT

QDS_STMT_DISK

QDS_TASK_SHUTDOWN

QDS_TASK_START

QPJOB_KILL

QPJOB_WAITFOR_ABORT

QRY_MEM_GRANT_INFO_MUTEX

QRY_PARALLEL_THREAD_MUTEX

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_TASK_ENQUEUE_MUTEX

QUERY_TRACEOUT

RECOVER_CHANGEDB

REDO_THREAD_PENDING_WORK

REDO_THREAD_SYNC

REPL_CACHE_ACCESS

REPL_HISTORYCACHE_ACCESS

REPL_SCHEMA_ACCESS

REPL_TRANFSINFO_ACCESS

REPL_TRANHASHTABLE_ACCESS

REPL_TRANTEXTINFO_ACCESS

REPLICA_WRITES

REQUEST_DISPENSER_PAUSE

REQUEST_FOR_DEADLOCK_SEARCH

RESMGR_THROTTLED

RESOURCE_GOVERNOR_IDLE

RESOURCE_QUEUE

RESOURCE_SEMAPHORE      Waiting for a query execution memory grant. Check sorts and hatches.

RESOURCE_SEMAPHORE_MUTEX

RESOURCE_SEMAPHORE_QUERY_COMPILE

RG_RECONFIG

RTDATA_LIST

SCAN_CHAR_HASH_ARRAY_INITIALIZATION

SEC_DROP_TEMP_KEY

SECURITY_CRYPTO_CONTEXT_MUTEX

SECURITY_KEYRING_RWLOCK

SECURITY_MUTEX

SECURITY_RULETABLE_MUTEX

SEMPLAT_DSI_BUILD

SEQUENCE_GENERATION

SEQUENTIAL_GUID

SERVER_IDLE_CHECK

SERVER_RECONFIGURE

SHUTDOWN

SLEEP_BPOOL_FLUSH

SLEEP_DBSTARTUP

SLEEP_DCOMSTARTUP

SLEEP_MASTERDBREADY

SLEEP_MASTERMDREADY Super Rare

SLEEP_MASTERUPGRADED

SLEEP_MSDBSTARTUP

SLEEP_SYSTEMTASK

SLEEP_TASK

SLEEP_TEMPDBSTARTUP

SLO_UPDATE

SNI_CONN_DUP

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_MEMORY_TOPLEVELBLOCKALLOCATOR

SOS_MEMORY_USAGE_ADJUSTMENT

SOS_OBJECT_STORE_DESTROY_MUTEX

SOS_PHYS_PAGE_CACHE

SOS_PROCESS_AFFINITY_MUTEX

SOS_SCHEDULER_YIELD  look for parallelism inhibitors like a serial UDF Take them out and rewrite code.

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

SP_PREEMPTIVE_SERVER_DIAGNOSTICS_SLEEP

SP_SERVER_DIAGNOSTICS_BUFFER_ACCESS

SP_SERVER_DIAGNOSTICS_INIT_MUTEX

SP_SERVER_DIAGNOSTICS_SLEEP

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

STARTUP_DEPENDENCY_MANAGER

TEMPOBJ

TERMINATE_LISTENER

THREADPOOL

TIMEPRIV_TIMEPERIOD

TRACE_EVTNOTIF

TRACEWRITE

TRAN_MARKLATCH_DT

TRAN_MARKLATCH_EX

TRAN_MARKLATCH_KP

TRAN_MARKLATCH_NL  Tran_Mark_Latch_* are used for synchronization of commits

TRAN_MARKLATCH_SH

TRAN_MARKLATCH_UP

TRANSACTION_MUTEX

UCS_ENDPOINT_CHANGE

UCS_MANAGER

UCS_MEMORY_NOTIFICATION

UCS_SESSION_REGISTRATION

UCS_TRANSPORT

UCS_TRANSPORT_STREAM_CHANGE

UTIL_PAGE_ALLOC

VDI_CLIENT_COMPLETECOMMAND

VDI_CLIENT_GETCOMMAND

VDI_CLIENT_OPERATION

VDI_CLIENT_OTHER

VERSIONING_COMMITTING

VIA_ACCEPT

VIEW_DEFINITION_MUTEX

WAIT_FOR_RESULTS    waiting for a query notification to be triggered

WAIT_SCRIPTDEPLOYMENT_REQUEST

WAIT_SCRIPTDEPLOYMENT_WORKER

WAIT_XTP_ASYNC_TX_COMPLETION

WAIT_XTP_CKPT_AGENT_WAKEUP

WAIT_XTP_CKPT_CLOSE

WAIT_XTP_CKPT_ENABLED

WAIT_XTP_CKPT_STATE_LOCK

WAIT_XTP_GUEST

WAIT_XTP_HOST_WAIT

WAIT_XTP_OFFLINE_CKPT_BEFORE_REDO

WAIT_XTP_OFFLINE_CKPT_LOG_IO

WAIT_XTP_OFFLINE_CKPT_NEW_LOG

WAIT_XTP_PROCEDURE_ENTRY

WAIT_XTP_RECOVERY

WAIT_XTP_TASK_SHUTDOWN

WAIT_XTP_TRAN_DEPENDENCY

WAITFOR

WAITFOR_PER_QUEUE

WAITFOR_TASKSHUTDOWN

WAITSTAT_MUTEX

WCC

WINFAB_API_CALL

WINFAB_REPLICA_BUILD_OPERATION

WORKTBL_DROP

WRITE_COMPLETION

WRITELOG Logs too slow

XACT_OWN_TRANSACTION

XACT_RECLAIM_SESSION

XACTLOCKINFO

XACTWORKSPACE_MUTEX

XDES_HISTORY

XDES_OUT_OF_ORDER_LIST

XDES_SNAPSHOT

XDESTSVERMGR

XE_BUFFERMGR_ALLPROCESSED_EVENT

XE_BUFFERMGR_FREEBUF_EVENT

XE_CALLBACK_LIST

XE_CX_FILE_READ

XE_DISPATCHER_CONFIG_SESSION_LIST

XE_DISPATCHER_JOIN

XE_DISPATCHER_WAIT

XE_LIVE_TARGET_TVF

XE_MODULEMGR_SYNC

XE_OLS_LOCK

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

XTP_HOST_DB_COLLECTION

XTP_HOST_LOG_ACTIVITY

XTPPROC_CACHE_ACCESS

XTPPROC_PARTITIONED_STACK_CREATE

b