A customer approached us asking for help on automatic a monitoring process. The goal was to send some type of notification when a particular threshold was reached.
He had already discovered this blog but still needed some guidance on how to get notified.
We created this prototype for him to use. Feel free to modify it to fit your needs.
- The script below creates an Xevent to track hadr_thread_pool_worker_start event. There are multiple targets for an Xevent – file, histogram, ETW, ring buffer. I selected a Ring Buffer as a target because it is in memory and does not require you to maintain a file – delete it, re-created it, etc.
- I also created a limit for how much data this buffer can hold, so that you don’t impact other things on the server - set it to 500 KB
- Next, the core of this functionality. This creates a table variable (in memory again) so no need for you to create a table on disk. However, if you decide to implement your solution with a table on-disk, you are welcome to do so.
- The script queries the Xevent ring buffer created, parses the XML output it produces into a rowset (using the .NODES() function) and then parses each individual XML row using the .VALUE() function.
- This data is filtered out to when WORKER_START_SUCCESS = ‘false’ or when Active_workers reaches 100 and that is what you see in the WHERE clause, but you can modify that to fit your needs.Since only rows that contain WORKER_START_SUCCESS = ‘false’ or Active_Workers > 100 will be inserted into the @tbl_variable_filtered, the rowset there will be small. But also, checking for ANY rows in that table will mean that you have encountered this condition. That’s where the IF EXISTS plays a role
- If you find any rows in that able, then I suggest you send yourself an email via sp_send_dbmail so you can take action.
- You can create an SQL Agent job that fires every so often and does the querying of the Ring buffer and notification below.
CREATE EVENT SESSION HadrThreadPoolWorkerStart on server ADD EVENT sqlserver.hadr_thread_pool_worker_start ADD TARGET package0.ring_buffer (SET max_memory = 500 -- Units of KB. ) with ( startup_state = on ); GO ALTER EVENT SESSION HadrThreadPoolWorkerStart ON SERVER STATE = START
/*-----------------------------------------------------------------------------------------------------*/ --the actual monitoring code, which you can consider adding to a SQL Agent job DECLARE @TBL_VARIABLE AS TABLE (time_stamp varchar(32), active_workers int, idle_workers int, worker_limit int, worker_start_success varchar(5)) DECLARE @TBL_VARIABLE_FILTERED AS TABLE (time_stamp varchar(32), active_workers int, idle_workers int, worker_limit int, worker_start_success varchar(5)) ;WITH XE_Hadr_ThrdPool as ( SELECT execution_count, CAST(target_data AS XML) AS [target_data_XML] FROM sys.dm_xe_session_targets WHERE event_session_address IN ( SELECT address FROM sys.dm_xe_sessions WHERE name = 'HadrThreadPoolWorkerStart' ) ) --JUST PUT EVERYTING IN THE TABLE VARIABLE. It is faster to filter out tabular results than filter XML data via XML parsing INSERT INTO @TBL_VARIABLE (time_stamp , worker_limit, idle_workers, active_workers , worker_start_success ) select top 20 T.xml_data.query('.').value('(/event/@timestamp)', 'varchar(32)') as time_stamp, T.xml_data.query('.').value('(/event/data/value)', 'int') as worker_limit, T.xml_data.query('.').value('(/event/data/value)', 'int') as idle_workers, T.xml_data.query('.').value('(/event/data/value)', 'int') as active_workers, T.xml_data.query('.').value('(/event/data/value)', 'varchar(5)') as worker_start_success FROM XE_Hadr_ThrdPool cross apply [target_data_xml].nodes('RingBufferTarget/event') as T(xml_data) if exists (select top 1 * from @TBL_VARIABLE where worker_start_success = 'false' or active_workers >100) begin INSERT INTO @TBL_VARIABLE_FILTERED select * from @TBL_VARIABLE where worker_start_success = 'false' or active_workers >100 order by time_stamp desc end if exists (select top 1 * from @TBL_VARIABLE_FILTERED) --Configure DB Mail here to get notified EXEC msdb.dbo.sp_send_dbmail @profile_name = 'YourProfileHere', @recipients = 'firstname.lastname@example.org', @body = 'This SQL Server may be running low on AlwaysOn worker threads.', @subject = 'HADR Worker Pool'
When you are ready to stop the session, you can issue this command:
alter event session HadrThreadPoolWorkerStart on server state = stop
For more information see "Thread Usage by Availability Groups" in this Books Online article