Troubleshooting CPU bottleneck


sys.dm_os_schedulers is a useful DMV to find out the CPU pressure. It returns one row per scheduler in SQL Server where each scheduler is mapped to an individual processor.

We can use this DMV to monitor the condition of a scheduler or to identify runaway tasks. It will help us to identify if there is any CPU bottleneck in the SQL Server machine.

We have to check for “runnable_tasks_count” column value which indicates the count of workers that have tasks assigned to them that are just waiting for their share of time on the scheduler (logical CPU). The value of runnable_tasks_count should be as low as possible.

Permission
Requires VIEW SERVER STATE permission on the server


SELECT scheduler_id,
status,
cpu_id,
is_online,
current_tasks_count,
runnable_tasks_count,
current_workers_count,
active_workers_count,
work_queue_count,
pending_disk_io_count,
load_factor
FROM sys.dm_os_schedulers
GO


If you find the the avg(runnable_tasks_count) greater then 0 that means system is waiting for CPU time. If  Pending_disk_io_count is greater then 0, that means system is bound by IO you need to get disks to perform better.

Advertisements

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s