title: SQL query hangs without error (parallel subquery?)
body: <p>I have a stored procedure running on a Microsoft SQL server 2012 database, which often (but not always!) hangs at a certain point. When I execute the specific update query itself, it runs fine, but if it's executed in the procedure as a whole, nothing happens after this point. This is where it goes wrong:</p>
  
  <pre><code>    UPDATE dbo.gesch_gzp
      SET red_ges=1
      WHERE uitg_verr_id IN (
          SELECT uitg_verr_id
          FROM dbo.gzp_flagging_2
          WHERE flag BETWEEN 2200 AND 2299
              AND flag IN (
                  SELECT flag 
                  FROM flagging 
                  WHERE lts=1
                  )
              )
  </code></pre>
  
  <p>While looking at the waiting tasks (dm_os_waiting_tasks) I strongly got the impression that it has something to do with parallellism, since multiple subprocesses with the same session_id but different exec_context_id where waiting for each other. 
  Therefore I already tried adding the OPTION(MAXDOP 1) and added a DISTINCT to the first select, but not with the desired effect (after restarting the stored procedure, it hang at exactly the same point). </p>
  
  <pre><code>    UPDATE dbo.gesch_gzp
      SET red_ges=1
      WHERE uitg_verr_id IN (
          SELECT DISTINCT(uitg_verr_id)
          FROM dbo.gzp_flagging_2
          WHERE flag BETWEEN 2200 AND 2299
              AND flag IN (
                  SELECT flag 
                  FROM flagging 
                  WHERE lts=1
                  )
              )
      OPTION(MAXDOP 1)
  </code></pre>
  
  <p>So I'm wondering if there is a way to rewrite this stored procedure to prevent it from hanging. Complicating matter is that this troubled update query happens to be at the end of a long program wich takes >24 hours to execute. And executing the update query itself does not reproduce the problem (it takes a few minutes), so this makes testing a difficult thing.</p>