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
Scrolling to the right, there are multiple columns of prime significance.
- 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.
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.
