title: How to find out why the status of a spid is suspended? What resources the spid is waiting for?
body: <p>I run <code>EXEC sp_who2 78</code> and I get the following <a href="https://stackoverflow.com/q/2234691/1501497">results</a>:</p>
  
  <p><img src="https://i.stack.imgur.com/0pmEI.jpg" alt="results of sp_who2 for spid 78"></p>
  
  <p>How can I find why its status is suspended?</p>
  
  <p>This process is a heavy <code>INSERT</code> based on an expensive query. A big <code>SELECT</code> that gets data from several tables and write some 3-4 millions rows to a different table.</p>
  
  <p>There are no locks/ blocks.</p>
  
  <p>The <code>waittype</code> it is linked to is <code>CXPACKET</code>. which I can understand because there are 9 78s as you can see on the picture below.</p>
  
  <p>What concerns me and what I really would like to know is why the number 1 of the <code>SPID</code> 78 is suspended.</p>
  
  <p>I understand that when the status of a <code>SPID</code> is suspended it means the process is waiting on a resource and it will resume when it gets its resource.</p>
  
  <p>How can I find more details about this? what resource? why is it not available?</p>
  
  <p>I use a lot the code below, and variations therefrom, but is there anything else I can do to find out why the <code>SPID</code> is suspended?</p>
  
  <pre><code>select * 
  from sys.dm_exec_requests r
  join sys.dm_os_tasks t on r.session_id = t.session_id
  where r.session_id = 78
  </code></pre>
  
  <p>I already used <a href="http://whoisactive.com/downloads/" rel="noreferrer">sp_whoisactive</a>. The result I get for this particular spid78 is as follow: (broken into 3 pics to fit screen)</p>
  
  <p><img src="https://i.stack.imgur.com/NaCZk.jpg" alt="enter image description here"></p>