Information about the DBSS - Worker Thread alarm (4290002)

I am getting DBSS - Worker Thread alarm for a SQLServer instance

The percentage of Worker Threads used (100.00%) is greater than the defined threshold 95.00%.

I can't find an explanation for the alarm. What does it mean?

SQL Server uses the native thread services of the Windows operating systems so that one or more threads support each network that SQL Server supports simultaneously, another thread handles database checkpoints, and a pool of threads handles all users.

Thread pooling helps optimize performance when large numbers of clients are connected to the server. Usually, a separate operating system thread is created for each query request. However, with hundreds of connections to the server, using one thread per query request can consume large amounts of system resources. The max worker threads option enables SQL Server to create a pool of worker threads to service a larger number of query request, which improves performance.

What is the DBSS - Worker Thread alarm?

The DBSS - Worker Thread alarm is part of the SQL Server Connections Summary collection. The associated rule fires an alarm upon detecting that the percentage of Worker Threads used exceeds a predefined threshold. 

The alarm fires with the message 

"SQLServer instance -value: The percentage of Worker Threads used (-value%) is greater than the defined threshold -value%."

The unit of measurement being used would be in percent with the Used Percent for five minutes over 80 percent firing a Warning alarm, and a Used Percent for five minutes over 95 percent firing a Warning alarm.

How is the value calculated?

In general, the calculation is based on a determination of the active workers in the system (the difference in the count of the active_workers from sys.dm_os_schedulers that are visible online) from the count of system workers (from sys.dm_exec_requests and sys.dm_exec_sessions) / divided by the MaxWorkerThreads from sys.configurations.

Where can topology information for the SQL Server Worker Thread collection be found?

• Management Server | All Data | DBSS_Data_Model | SQL-Server Data Model | Clusters | {Instance Name} | Instances | {Instance Name} | SQL Server Connections Summary | SQL Active Threads | History
• Management Server | All Data | DBSS_Data_Model | SQL-Server Data Model | Clusters | {Instance Name} | Instances | {Instance Name} | SQL Server Connections Summary | Worker Threads Used Percent | History

Information on worker threads from the Microsoft website: http://msdn.microsoft.com/en-us/library/ms187024.aspx

Refer to the following external web pages for more details on some of the metrics used in this calculation

• https://www.sqlshack.com/monitoring-sql-server-with-dynamic-management-objects-requests/
• https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-ver15
• https://techcommunity.microsoft.com/t5/core-infrastructure-and-security/monitoring-sql-server-2012-alwayson-availability-groups-worker/ba-p/370510

Here is some additional information on changing worker threads to improve performance: http://www.vircom.com/security/how-to-change-the-sql-max-worker-threads-to-improve-performance/