Max Worker Threads

In SQL Server, worker threads represent logical operating system threads used by the SQL Server Database Engine to execute tasks. The max worker threads configuration option allows SQL Server to control the number of threads available for query processing and general task execution.

Potential Issues

Manually configuring the max worker threads option can lead to significant performance problems if not done correctly:

  • Too Low: If the value is set too low, SQL Server could experience thread starvation, limiting its ability to handle concurrent tasks, resulting in poor performance.
  • Too High: If the value is set too high, it can lead to excessive context switching, which occurs when the system spends more time switching between threads than executing queries. This, too, can degrade performance.

Suggested Action

SQL Server is generally good at determining the optimal number of worker threads automatically based on the system’s hardware and workload. Therefore, it is recommended to leave the max worker threads setting at the default value of 0, which allows SQL Server to dynamically manage the worker threads.

How to Reset Max Worker Threads to Default

EXEC sys.sp_configure @configname = N'show advanced options',
                      @configvalue = N'1';
RECONFIGURE;
GO
EXEC sp_configure 'max worker threads', 0;
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure @configname = N'show advanced options',
                      @configvalue = N'0';
RECONFIGURE;
GO

How Aireforge Detects Worker Thread Issues

Aireforge automatically checks the worker threads configuration to detect potential performance bottlenecks related to thread exhaustion or over-allocation.

Here's how Aireforge performs the check:

  1. Worker Thread Monitoring: Aireforge queries the system to retrieve the current number of worker threads being used (sys.dm_os_threads) and compares this to the maximum worker threads limit (MaxWorkerCount).

  2. Percentage Thresholds: Aireforge monitors the system for worker thread utilization using specific thresholds:

    • 60% Threshold: If worker thread utilization exceeds 60% of the maximum value, Aireforge raises a low-severity warning.
    • 80% Threshold: If worker thread utilization exceeds 80%, a medium-severity warning is raised, indicating that action should be taken s oon.
    • 90% Threshold: If utilization reaches 90%, Aireforge flags this as a critical issue, indicating that immediate action is required to prevent performance degradation.

  3. Manual Configuration Warning: If the max worker threads setting is manually configured (i.e., not set to the default 0), Aireforge flags this as a potential stability risk, especially if the worker thread utilization is high. Aireforge will suggest reverting the setting to 0 for automatic thread management by SQL Server.

  4. Automatic Reset Script: If issues are detected, Aireforge generates a script to reset the max worker threads setting to its default value, ensuring that SQL Server can automatically manage the threads based on system requirements.

Example SQL Query to Check Worker Threads

SELECT COUNT(1) AS current_worker_threads
FROM sys.dm_os_threads
WHERE started_by_sqlservr = 0;

This query shows the number of worker threads in use that were not started by SQL Server itself.