First published on MSDN on Dec 07, 2017
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.
https://blogs.msdn.microsoft.com/sql_pfe_blog/2013/07/15/monitoring-sql-server-2012-alwayson-availab...
We created this prototype for him to use. Feel free to modify it to fit your needs.
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)[1]', 'varchar(32)') as time_stamp,
T.xml_data.query('.').value('(/event/data/value)[1]', 'int') as worker_limit,
T.xml_data.query('.').value('(/event/data/value)[2]', 'int') as idle_workers,
T.xml_data.query('.').value('(/event/data/value)[3]', 'int') as active_workers,
T.xml_data.query('.').value('(/event/data/value)[4]', '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 = 'youradmin@company.com',
@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
Namaste,
Joseph
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.