How to monitor the workers used over a period of time?



  • Assume I have the default setting of max workers or say I have x number of max worker threads configured.

    Is there any windows or sql performance counter to monitor the number of workers used over a period of time?



  • You can run this query at a frequent interval of your choice and save the result in a table.

    /*
    Copied from
    https://dba.stackexchange.com/questions/188102/who-is-using-my-worker-threads-sql-server-2014-hadr
    */
    DECLARE @max int
    SELECT @max = max_workers_count FROM sys.dm_os_sys_info
    

    SELECT
    @max as 'TotalThreads',
    SUM(active_Workers_count) AS 'CurrentThreads',
    @max - SUM(active_Workers_count) AS 'AvailableThreads',
    SUM(runnable_tasks_count) AS 'WorkersWaitingForCpu',
    SUM(work_queue_count) AS 'RequestWaitingForThreads' ,
    SUM(current_workers_count) AS 'AssociatedWorkers'
    FROM sys.dm_os_Schedulers WHERE STATUS='VISIBLE ONLINE'

    Remember:

    The actual number of query requests can exceed the value set in max worker threads in which case SQL Server pools the worker threads so that the next available worker thread can handle the request. A worker thread is assigned only to active requests and is released once the request is serviced. This happens even if the user session/connection on which the request was made remains open.

    I suggest you read the following:

    • https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-worker-threads-server-configuration-option?redirectedfrom=MSDN&view=sql-server-ver15
    • https://systemcenter.wiki/?GetElement=Microsoft.SQLServer.Windows.Monitor.DBEngine.ThreadCount&Type=UnitMonitor&ManagementPack=Microsoft.SQLServer.Windows.Monitoring&Version=7.0.24.0
    • https://docs.microsoft.com/en-us/answers/questions/709980/perfmon-thread-count-vs-sql-workers-sql-server-201.html



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2