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!

When your application experiences a performance problem chances are some aspect of your application has or is hitting a bottleneck. What exactly is a bottleneck? Per Wikipedia a bottleneck is a phenomenon where the performance or capacity of an entire system is severely limited by a single component. A common single component in a SQL Server system could be any of the following:

• CPU
• Memory
• I/O
• tempdb
• Blocking

There are several tools within SQL Server that you can use to monitor and identify which of these single components are the bottleneck. You can use System Montior, SQL Profiler, sp_who or sp_who2. You can now access the sys.dm (Dynamic Management) procedures and write SQL queries to obtain system information in SQL Server 2005. In my upcoming blog posts I will cover these in more detail so please check back often!