SQL SERVER – RESOURCE SEMAPHORE and THREADPOOL

A client of mine of Comprehensive Database Performance Health Check recently sent me an email about two of the wait issue which she is frequently seeing in the top 5 wait statistics – RESOURCE SEMAPHORE and THREADPOOL. They are indeed very troublemaking wait statistics and they indeed talk about Memory and CPU issues. Let us learn what each of them means and what can be potential solutions for them.

SQL SERVER - RESOURCE SEMAPHORE and THREADPOOL RESOURCE-SEMAPHORE-800x191

Here is my latest SQL Server Wait Statistics script which you can run for your server. Here is an important blog post about SQL SERVER – Identifying Query Generating Malicious Wait Type.

RESOURCE SEMAPHORE

This indicates that queries are waiting for memory to receive to execute. Essentially, queries are waiting for more memory to be available so they can start running.

There are two potential solutions to this problem.

  1. Add More Memory – Honestly, I prefer this as a last resort. It is quite possible to solve your short-term problem with this approach but as more queries are addressed by your system, very soon once again the resource semaphore issue may show up again.
  2. Fix Trouble Making Queries – Find all the queries which are actually waiting for having large memory grants and try to tune them so eventually, they run faster and do not take more memory.

THREADPOOL

This indicates that queries are waiting for more CPU threads to execute. Essentially, queries are waiting for CPU threads to be available so they can start running.

There are two potential solutions to this problem.

  1. Add More CPU – Honestly, I prefer this usually very difficult to do unless you are running on VM and have a license for your SQL Server with more CPU.  It is quite possible to solve your short-term problem with this approach but as more queries are addressed by your system, very soon once again the threadpool issue may show up again.
  2. Fix Trouble Making Queries – Find all the queries which are actually waiting for taking more CPU resources and try to tune them so eventually, they run faster and do not take more CPU power.

Additional Resources

Here are few important resources:

If you have any questions please reach out to me on Twitter.

Reference: Pinal Dave (https://blog.sqlauthority.com)

CPU Scheduler, CPU Threads, Memory Grant, SQL CPU, SQL Memory, SQL Server, SQL Wait Stats
Previous Post
SQL SERVER – Speed Up Index Rebuild with SORT IN TEMPDB
Next Post
SQL SERVER – Wait Stats Collection Scripts : Updated March 2021

Related Posts

Leave a Reply