MSSQL Wait Events
Resource Waits - time spent waiting for a resource to be available e.g. Locks, Latches, I/O etc
Signal Waits - the equivalent of the UNIX run queue - time spent waiting for the CPU to become available (as opposed to time spent actually processing)
Queue Waits - time spent waiting for something to do
External Waits - time spent waiting for something outside the database
Dynamic Views
sys.dm_os_wait_stats
sys.dm_os_waiting_tasks
"Posion" Waits
This is a term coined by Kendra Little (1) which describes waits that make SQL Server feel unusable whilst they are happening. They may not have the highest wait time but may have the highest visible impact, If you use the FirstResponder kit these waits will be flagged as "Posion Wait Detected".CMEMTHREAD
IO_RETRY
PREEMPTIVE_DEBUG
RESOURCE_SEMAPHORE
RESOURCE_SEMAPHORE_QUERY_COMPILE
THREADPOOL
Azure DB
IO_QUEUE_LIMIT
LOG_RATE_GOVERNOR
POOL_LOG_RATE_GOVERNOR
REGMGR_THROTTLED
SE_REPL*
Tools
sp_WhoIsActive
sp_BlitzFirst
sp_PressureDetector
Get-DbaWaitStatistic
Waits
CXCONSUMER
Introduced in SQL 2016 SP2 and SQL 2017 RTM CU3 and later."Benign and can be ignored" (2)
CXPACKET
SOS_SCHEDULER_YIELD
If it doesn't need to wait for anything else, a SQL Server thread will run for a maximum of 4ms then voluntarily yield the processor. High SOS_SCHEDULER_YIELD waits can be an indicator of a SQL Server where all other waits have been eliminated. However, in a virtual environment, they can also be an indicator of hypervisor performance problems (i.e. cpu contention - check RDY%).
Bibliography & References
Toolshttps://github.com/amachanic/sp_whoisactive (sp_whoisactive - Adam Machanic)http://whoisactive.com/docs/https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit#sp_blitzfirst-real-time-performance-advice (sp_BlitzFirst - Brent Ozar)https://github.com/erikdarlingdata/DarlingData#pressure-detector (sp_pressuredetector - Erik Darling)https://docs.dbatools.io/Get-DbaWaitStatistic.html
Poison Waitshttps://www.brentozar.com/blitz/poison-wait-detected/(1) https://www.littlekendra.com/2020/03/19/the-learners-guide-to-sql-server-performance-triage/
CXCONSUMERhttps://sqlperformance.com/2015/06/sql-performance/knee-jerk-wait-statistics-cxpackethttps://www.sqlskills.com/help/waits/cxconsumer/
CXPACKEThttps://www.sqlskills.com/help/waits/cxpacket/(2) https://sqlperformance.com/2015/06/sql-performance/knee-jerk-wait-statistics-cxpacket (Paul Randal)
LATCH_EXhttps://www.sqlskills.com/help/waits/latch_ex/
SOS_SCHEDULER_YIELDhttps://www.sqlskills.com/help/waits/sos_scheduler_yield/https://sqlperformance.com/2014/02/sql-performance/knee-jerk-waits-sos-scheduler-yieldhttps://www.sqlskills.com/blogs/paul/increased-sos_scheduler_yield-waits-on-virtual-machines/https://www.sqlskills.com/blogs/jonathan/cpu-ready-impact-on-sos_scheduler_yield/https://sqlespresso.com/2019/03/06/ouch-chronic-high-cpu/https://www.erikdarlingdata.com/sql-server/automatic-soft-numa-and-sos_scheduler_yield-waits/https://app.spotlightcloud.io/waitopedia/waits/SOS_SCHEDULER_YIELD
PAGEIOLATCH_SHhttps://www.sqlskills.com/help/waits/pageiolatch_sh/https://www.sqlshack.com/handling-excessive-sql-server-pageiolatch_sh-wait-types/
RESOURCE_SEMAPHOREhttps://www.mssqltips.com/sqlservertip/2827/troubleshooting-sql-server-resourcesemaphore-waittype-memory-issues/