title: Performance Issues - Parallelism - SQL Server
body: <p>SQL agent job never finishes.</p>
  
  <p>Server :  2.4hZ, 24 processor, 256GB RAM, Windows 2008 Server, SQL 2008</p>
  
  <p>As per the activity monitor: %ProcessTime is 100%</p>
  
  <p>With SP_Who found below:</p>
  
  <pre><code>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 RUNNABLE                        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
  </code></pre>
  
  <p>As per it is 24 process the query been split into 24 parallel threads.</p>
  
  <p>Settings for Cost Threshold for Parallelism is 30 and Max Degree of Parallelism is 0 (default).</p>
  
  <p>Cannot see anything going in Profiler and all the thread above are waiting for CPU Time.</p>
  
  <p>How to find what is SQL Server doing and what is holding up the CPU?</p>