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.
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.
- 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.
- 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.
- Memory-Optimized TempDB and RESOURCE_SEMAPHORE Wait Type
- SQL SERVER 2019 – Memory-Optimized TempDB and RESOURCE_SEMAPHORE Wait Type
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.
- 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.
- 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.
- SQL SERVER – Optimal Value Max Worker Threads
- SQL SERVER – 5 Important Steps When Query Runs Slow Occasionally
Additional Resources
Here are few important resources:
- SQL SERVER – Wait Stats – Wait Types – Wait Queues – Day 0 of 28
- SQL SERVER – Wait Stats Collection Scripts for 2016 and Later Versions
- SQL SERVER – Get Wait Stats Related to Specific Session ID With sys.dm_exec_session_wait_stats
- 28 Links for Learning SQL Wait Stats from Beginning
If you have any questions please reach out to me on Twitter.
Reference: Pinal Dave (https://blog.sqlauthority.com)
