SQL Server Worker Threads Management and Understanding
0
votes
1
answer
814
views
Hello SQL Server Community,
I'm trying to deepen my understanding of SQL Server's worker threads, especially with regard to the max worker threads setting in a busy OLTP system. I'm currently working with the
sys.dm_os_threads
DMV, and I'm exploring how best to optimize thread management on our server.
My current understanding is that SQL Server automatically manages the worker threads based on the system's workload if the default max worker threads setting is unchanged. SQL Server may allocate more worker threads to handle increased workloads and may reclaim or destroy these threads once tasks are completed, though this might not occur immediately.
However, I'm not entirely sure if my understanding is correct, particularly when it comes to situations where SQL Server may have reached its limit for creating new threads. If this happens and there are still new incoming requests, could this result in a 'deadlock scheduler' condition? We've recently experienced such an issue in our environment, and I'm trying to understand if this could be the cause.
Given this context, I have a few questions:
- On a busy OLTP system, should we consider altering the max worker threads setting from its default value calculated by Microsoft's formula?
- If we should change it, should this be to a number greater than the default?
- How can we best determine the optimal number of max worker threads for our specific environment to prevent the 'deadlock scheduler' issue?
Your insights or advice on this matter would be greatly appreciated. Thank you.
Asked by Fajela Tajkiya
(1239 rep)
Oct 18, 2023, 10:24 AM
Last activity: Oct 18, 2023, 03:48 PM
Last activity: Oct 18, 2023, 03:48 PM