Understanding SQL Server Worker Threads and THREADPOOL Waits

Introduction

If you’ve ever used SQLQueryStress to test your SQL Server’s performance, you might have encountered a situation where you see THREADPOOL waits but still have available worker threads. This scenario can be perplexing, especially if you’re trying to understand how SQL Server manages its resources. In this article, we’ll demystify this situation and explain why it happens, helping you better grasp SQL Server’s thread management and scheduling.

SQL Server Worker Threads and THREADPOOL Waits

1. Worker Threads and Schedulers
SQL Server uses worker threads to execute tasks. Each task is assigned to a worker thread, which is then scheduled by a scheduler. SQL Server manages these threads and schedulers to optimize performance and ensure efficient task execution. The maximum number of worker threads is configured using the max worker threads option. However, system threads (used by internal processes like LazyWriter, Checkpoint, etc.) do not count towards this limit.

2. Understanding THREADPOOL Waits
THREADPOOL waits occur when SQL Server is unable to assign a worker thread to a new task because all available threads are busy. This usually indicates that the server is under heavy load and cannot keep up with the incoming requests. However, seeing THREADPOOL waits does not necessarily mean that there are no available worker threads. This can happen due to several reasons:

• Concurrency Issues: SQL Server might be handling multiple high-concurrency tasks that temporarily exceed the available worker threads, causing THREADPOOL waits.
• Blocked Threads: If many threads are blocked, waiting on resources like I/O or locks, they remain counted as active even though they are not doing any actual processing. This can lead to THREADPOOL waits even if there are technically available threads.
• Scheduler Limitations: Each scheduler in SQL Server can handle a limited number of worker threads at a time. Even if there are available threads in the system, individual schedulers might be fully occupied, leading to THREADPOOL waits.

3. Available Worker Threads vs. Active Worker Threads
When you sum up the active_workers_count in dm_os_schedulers, you might still see available worker threads because this count includes only those threads actively executing tasks. Threads waiting on I/O, blocked on locks, or in other wait states are not considered active, but they still contribute to THREADPOOL waits because they are not free to take new tasks.

Examples and Practical Insights

Let’s consider an example where you run SQLQueryStress with a high number of threads and iterations. You might observe THREADPOOL waits while querying dm_os_schedulers shows available worker threads. This discrepancy arises because the threads counted as available are not necessarily idle or free to take new tasks—they might be waiting for I/O operations or blocked on locks, thereby contributing to the THREADPOOL waits without showing up as active workers.

Here’s a query to help you monitor worker threads and their states:

SELECT 
    scheduler_id,
    active_worker_count,
    runnable_tasks_count,
    current_tasks_count,
    work_queue_count
FROM 
    sys.dm_os_schedulers
WHERE 
    scheduler_id < 255;

This query provides a snapshot of the state of worker threads and schedulers, helping you diagnose and understand the root causes of THREADPOOL waits.

Conclusion

Understanding the interplay between worker threads, schedulers, and THREADPOOL waits is crucial for diagnosing and resolving performance issues in SQL Server. By grasping why THREADPOOL waits occur even when worker threads are available, you can better optimize your server’s performance and resource management. Always ensure to monitor not just the active workers but also the states of all threads to get a comprehensive view of your server’s health.

For more detailed information, you can visit Microsoft’s official documentation on configuring max worker threads.