SQL Server Worker Thread Alert

, 2019-05-02

We have been toying around with Availability Group Auto Seeding across replicas. So far so good we have been successful and even implementing TDE encryption in the mix across 4 Azure replicas. One issue we faced was the testing VM's did not have much horse power and we ran out of worker treads. As a convenience we decided to build a quick worker thread alert so we know when we are near the limitations. This can have other useful purposes. In testing this seems to work pretty well. Hope some others can make use of the code base. I grabbed a quick query from Dr. Google and went at it, 10 min had a working alert.

--Author - Ed Pochinski 04232019
--Purpose - AG Worker thread alert
--SSC posting copy
Declare @availableThreads varchar(25), @workers_waitingForCPU varchar(25), @Request_waiting_ForThread varchar(25),@msgbody varchar(500), @msgsubject varchar(500)
Select @availableThreads = (select  max_workers_count from sys.dm_os_sys_info)-sum(active_Workers_count) from  sys.dm_os_Schedulers where status='VISIBLE ONLINE'
Print @availableThreads
Select @workers_waitingForCPU = sum(runnable_tasks_count) from  sys.dm_os_Schedulers where status='VISIBLE ONLINE'
Print @workers_waitingForCPU
Select @Request_waiting_ForThread = sum(work_queue_count)  from  sys.dm_os_Schedulers where status='VISIBLE ONLINE'
Print @Request_waiting_ForThread
--edit threshold to accomodate this is a test machine with no load so the number is high to facilitate the alert for testing
If @availableThreads <=545 
Print ' Send a email alert here !!!!! Worker threads are low: ' + @availableThreads + ' Workers Waiting on CPU: ' + @workers_waitingForCPU + ' Requests Waiting for thread: ' + @Request_waiting_ForThread
set @msgbody ='Worker threads are low on Server: ' + @@servername + ' - Available Thread Count: ' + @availableThreads 
set @msgbody = @msgbody + char(13);
set @msgbody = @msgbody + ' Worker Threads Waiting on CPU count: ' + @workers_waitingForCPU + char(10);
set @msgbody = @msgbody + ' Requests Waiting for worker thread count: ' + @Request_waiting_ForThread
set @msgsubject = '!!! AG Worker Thread Alert on SQL Server: ' + @@servername + ' !!!'
EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'Your_Email_Public_Profile',
  @from_address = 'YourEmail@here.com',
  @recipients= 'YourEmail@here.com',
  @subject= @msgsubject, 
  @body = @msgbody





Related content

Service Broker Part 1: Service Broker Basics

This article covers SQL Server 2008 Service Broker, an asynchronous messaging framework that is directly integrated within the relational engine of SQL Server. The series will provides you with the basics about implementing Service Broker applications and how you can transparently scale them out to support any required workload.

5 (1)


2,418 reads