Before I discuss the most important wait type list , I want to say that really there is no definitive list of most important sql wait types. Understanding what is normal and the context of the wait type is essential when discussing wait types.
Related Articles
For example , you may see the sql wait type WRITELOG as high on the list of waits on a Sql Server. This may or may not be important depending on the "normal" behaviour. Benchmarking your systems is a great way of understanding the patterns your systems.
Based on monitoring and analysing various SQL Server performance troubleshooting situations , these are some sql wait types which are worth reviewing . In fact , if I'm collecting regular wait stats for a SQL Server system - I'll flag these wait types when occuring regularly.
PAGELATCH_EX - pointing to some type of performance bottleneck . Read more on SQL Server – Pageiolatch_ex and how to reduce it
LCK_M_IX - I've flagged this wait type because if there is a high level of locking waits than this may mean some sort of blocking problems requiring analysis. Specifically , A transaction is waiting to acquire an intent exclusive lock. A high level of lock waits is associated with busy SQL Servers , multiple transactions requesting the same data Resource - ultimately leading to decrease in SQL Server performance
RESOURCE_SEMAPHORE_QUERY_COMPILE - A query is waiting for a memory grant to allow compilation to progress. How you respond will depend on the system but generally this wait type happens frequently - either analyse the memory aspect of the queries or cast a wider view on other resources which may be taking buffer pool memory pages.
RESOURCE_SEMAPHORE - RESOURCE SEMAPHORE "usually" correlates with poor response time to all the users , so a high percentage of RESOURCE SEMAPHORE wait time is a critical indicator.Read more on SQL WAIT TYPE – RESOURCE SEMAPHORE and how to reduce it
THREADPOOL - The THREADPOOL wait type is recorded when a task is waiting to get assigned to a worker thread. You would see this wait type appear in sys.dm_os_waiting_tasks.Read more on How to reduce THREADPOOL wait type (SQL Server DBA)
