Detecting A CPU Bottleneck

In SQL Server 2005 there several ways to detect a CPU bottlneck.  The first way to detect a bottleneck is by looking at the System Monitor counter Processor: % Processor Time.  If the value of this counter  is greater than 80% for  15 to 20 minutes in duration, then it is a good indication that you have a bottleneck.

You can also monitor System: Processor Queue Length.  A value of 2 or higher for a length of time also indicates a CPU bottleneck.  Another way to detect a CPU bottleneck is to utilize the new DMVs (Dynamic Management Views) in SQL Server 2005.  Execute the  following query in the master database and the query will return the count of number of worker threads in the runnable state:

Select Count(*) AS workers_waiting_for_cpu, s.scheduler_id
From sys.dm_os_workers as o
Inner Join sys.dm_os_schedulers as s
On o.scheduler_address = s.scheduler_address
And s.scheduler_id < 255
Where o.state = ‘Runnable’
Group By s.scheduler_id

I hope this helps… if you have another way to detect a CPU bottleneck post here and let us know!

Tags: cpu, processor time, SQL Server 2005, system monitor