Most important SQL Server wait types

26 June,2018 by Jack Vamvas

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. 

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_SEMAPHORERESOURCE 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)

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Most important SQL Server wait types


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer