Monitoring Pressure – Observing Task Count

In this blog, we will be looking into the topic of Monitoring Pressure in SQL Server. This can be done by observing the Task Count.
When workloads are submitted to SQL Server, they are converted to tasks. The resources in SQL Server, primarily CPU, Memory, and IO work in collaboration with each other to execute these tasks. When the number of tasks goes beyond a value that the resources cannot handle, it is presumed that the SQL engine is under some form of pressure. There are multiple ways to monitor the pressure in SQL Server with our personal favorite being Dynamic Management Views (DMVs).

From sys.dm_os_scheduler, the following results are observed.

SELECT * FROM sys.dm_os_schedulers

monitoring

Scrolling to the right, there are multiple columns of prime significance.

monitoring2

   
  • current_task_count – the number of tasks that the scheduler had when the DMV had run.
  • runnable_task_count –the number of tasks that are listed in the runnable queue. These tasks have the resources required to run but do not have a scheduler assigned to them at the moment. SQL Server with a runnable task count of 0, is considered to be in its idle state.
  • current_workers_count –the number of workers that are present on a particular scheduler.
  • work_queue_count – the number of threads that are waiting for a worker to get picked up.
  • pending_disk_io_count– tells us if the disk is experiencing pressure.

A simple query like the below can be used for monitoring purposes.

SELECT 
AVG(current_tasks_count) AS [avg_task_count],
AVG(runnable_tasks_count) AS [avg_runnable_tasks_count],
AVG(work_queue_count) AS [avg_work_queue_count],
AVG(pending_disk_io_count) AS [avg_pending_disk_io_count]
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255

Upon executing the above query, the results show an avg_task_count of 7, and all other values resting at 0.

Next, a few simulated users are added to the system and the process is repeated, to obtain the following results.

monitoring3

The results set shows an increment in the avg_task_count to a value of 19, with 11 tasks waiting in the queue for a scheduler to be assigned to them.

In this way, observing task counts helps us monitor the pressure experienced by SQL Server at any given point in time.

 

   

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

Leave a Reply

Your email address will not be published.