High Cpu accross all cores - how to investigate?

Microsoft SQL Server

Hi all,

We have an 8 core 32Gb windows 2008 ent x64 machine running SQL 2005
Enterprise SP3 CU5. This server supports a Dynamics 2009 AX environment with
a few hundred users on currently.

Occasionally we have a situation where all the cores max to 100%, a lot
(10+) of our processes go into Runnable but there's no apparent locking and
it's extremely hard to find what exactly is causing the issue.

Is there a 'silver bullet' to find exactly what SPID is causing the issues
when you hit a problem like that or is it a comparison of a number of
pointers?

Occasionally i've tracked it down to be a Dynamics user which has locked 2
of the cpus (MAXDOP = 2) and that manifests in that their user is
Ending-Blocked in the Dynamics client. The most recent occasion though there
were no clues at all, no suspended/blocking threads and even taking the
drastic step of killing all the 'Runnable' processes only provided a
temporary respite.

There is nothing else running on the SQL server save AV etc.

Any help appreciated!
Chris
---This first thing to check if CPU is at 100% is to look for parallel
queries:

-- Tasks running in parallel (filtering out MARS requests below):

select * from sys.dm_os_tasks as t

where t.session_id in (

select t1.session_id

from sys.dm_os_tasks as t1

group by t1.session_id

having count(*) > 1

and min(t1.request_id) = max(t1.request_id));

-- Requests running in parallel:

select *

from sys.dm_exec_requests as r

join (

select t1.session_id, min(t1.request_id)

from sys.dm_os_tasks as t1

group by t1.session_id

having count(*) > 1

and min(t1.request_id) = max(t1.request_id)

) as t(session_id, request_id)

on r.session_id = t.session_id

and r.request_id = t.request_id;
Thanks for your quick replies there folks!

Uri, what would be the thinking behind looking for parellel queries? Could
they be blocking each other? A quick explanation or point me towards useful
literature would be much appreciated.

Chris
Chris
SQL Server can decide to divide the execution of the query into more than
one threads depending on affinity mask settings

Sometimes it can be good , however , sometimes usually because of lack of
indexes it could fill up the CPU