Performance Issues - Parallelism - SQL Server

SQL agent job never finishes.

Server : 2.4hZ, 24 processor, 256GB RAM, Windows 2008 Server, SQL 2008

As per the activity monitor: %ProcessTime is 100%

With SP_Who found below:

SPID    Status  DBName  Command CPUTime ProgramName SPID
141 SUSPENDED                       STL UPDATE  5429    .Net SqlClient Data Provider    141
141 RUNNABLE                        STL UPDATE  22362478    .Net SqlClient Data Provider    141
141 RUNNABLE                        STL UPDATE  21178290    .Net SqlClient Data Provider    141
141 RUNNABLE                        STL UPDATE  22590708    .Net SqlClient Data Provider    141
141 RUNNABLE                        STL UPDATE  21682298    .Net SqlClient Data Provider    141
141 RUNNABLE                        STL UPDATE  20652239    .Net SqlClient Data Provider    141
141 RUNNABLE                        STL UPDATE  22315694    .Net SqlClient Data Provider    141
141 RUNNABLE                        STL UPDATE  22362120    .Net SqlClient Data Provider    141
141 RUNNABLE                        STL UPDATE  21867097    .Net SqlClient Data Provider    141
141 RUNNABLE                        STL UPDATE  22746381    .Net SqlClient Data Provider    141
141 RUNNABLE                        STL UPDATE  20301456    .Net SqlClient Data Provider    141
141 RUNNABLE                        STL UPDATE  22236445    .Net SqlClient Data Provider    141
141 RUN开发者_如何学GoNABLE                        STL UPDATE  19998611    .Net SqlClient Data Provider    141
141 RUNNABLE                        STL UPDATE  21139087    .Net SqlClient Data Provider    141
141 RUNNABLE                        STL UPDATE  22628117    .Net SqlClient Data Provider    141
141 RUNNABLE                        STL UPDATE  21512351    .Net SqlClient Data Provider    141
141 RUNNABLE                        STL UPDATE  21354649    .Net SqlClient Data Provider    141
141 RUNNABLE                        STL UPDATE  20256823    .Net SqlClient Data Provider    141
141 RUNNABLE                        STL UPDATE  21185138    .Net SqlClient Data Provider    141
141 RUNNABLE                        STL UPDATE  21272530    .Net SqlClient Data Provider    141
141 RUNNABLE                        STL UPDATE  22084796    .Net SqlClient Data Provider    141
141 RUNNABLE                        STL UPDATE  20745996    .Net SqlClient Data Provider    141
141 RUNNABLE                        STL UPDATE  21812185    .Net SqlClient Data Provider    141
141 RUNNABLE                        STL UPDATE  21738442    .Net SqlClient Data Provider    141
141 RUNNABLE                        STL UPDATE  20895694    .Net SqlClient Data Provider    141

As per it is 24 process the query been split into 24 parallel threads.

Settings for Cost Threshold for Parallelism is 30 and Max Degree of Parallelism is 0 (default).

Cannot see anything going in Profiler and all the thread above are waiting for CPU Time.

How to find what is SQL Server doing and what is holding up the CPU?

---

I would start with looking at the waiting tasks dmv to see the types of resources which the query is waiting. Also the query plan being used by the current executing statement.

SELECT      *
FROM        sys.dm_os_waiting_tasks
WHERE       session_id = 141

SELECT      q.query_plan,
            r.*
FROM        sys.dm_exec_requests                    r
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle)   q
WHERE       session_id = 141