title: Can parallelism cause 100% CPU usage?
body: <p>I realize this question is kind of vague, but I'm hoping someone can point me in a direction of things to look at.</p>
  
  <p>My problem is that I have a query within a program that is driving the CPUs on my SQL Server to 100%.  I think that the problem is related to parallelism, but I don't know how to confirm my suspicion, or the best way to fix the problem.</p>
  
  <p>The query in question spent about 20 minutes running the server at 100% CPU, at which point I killed the query.  I used <code>sys.dm_exec_query_stats</code> to look at the query execution plan.  There are no index scans.  The index operations are all seeks.  I'm confident that my indexes are well chosen for this query.  I do see a lot of parallelism going on within the query.</p>
  
  <p>For comparison, I tried running the same query in the query window in Management Studio.  The query runs in no more than a few seconds, and the Display Estimated Execution Plan button gives me a different execution plan.  One of the differences in this execution plan is that there is no parallelism.</p>
  
  <p><a href="https://www.brentozar.com/pastetheplan/?id=ByNDh3miQ" rel="nofollow noreferrer">Here is a link to the query execution plan for the bad query.</a>  One thing which is looks like a problem is the clustered index seek on <code>PS_TRANSACTION_INV</code> with a predicate on <code>PS_TRANSACTION_INV.BUSINESS_UNIT</code> (all the way to the right in the execution plan) shows 1 estimated row.  All but a few hundred rows have the value <code>TRUS1</code> and the rest of the rows have the value <code>TRCN1</code>.  The table has about 15 million rows and uses about 22 GB (so the rows are pretty large).  I did <code>DBCC SHOW_STATISTICS</code> for the table's clustered index, and the statistics look correct (as shown below).  The execution plan also has an estimated row size of 54 B, which is wrong.  The true row size is larger.</p>
  
  <p><a href="https://i.stack.imgur.com/VmP0O.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/VmP0O.png" alt="enter image description here"></a></p>
  
  <p>However, even with a bad execution plan, I don't understand how this query could run for 24+ hours without finishing.</p>
  
  <p>Looking at <code>sys.sysprocesses</code>, I can see that the last wait type is <code>SOS_SCHEDULER_YIELD</code>.</p>
  
  <p><a href="https://www.brentozar.com/pastetheplan/?id=ry20hhQiQ" rel="nofollow noreferrer">Here is a link to the query execution plan for the good query.</a></p>
  
  <p>My server has 8 CPUs and 24 GB of memory.  The query itself returns about 27,000 rows, so it should be well within the capability of the server to execute this quickly.  This program has been running for years with no problems, so I think something must have changed about the execution plan.  My server's DOP is 64.</p>
  
  <p>Am I right in thinking that the problem is likely related to parallelism?  If so, how could that be causing a query that should run in a second or two to use 100% of the CPU for over 20 minutes and still not finish?</p>
  
  <p>EDIT: In the execution plan for the bad query, I found that one of the steps is doing an index seek on a field where it should ideally do a clustered index seek.  The index seek is on a table that has about 20,000,000 rows, but the indexed field has high selectivity.  That index seek feeds into a nested loop (with parallelism) in the query plan.  I still think that the problem is in some way related to the parallelism.</p>
  
  <p>For reference, I can run a <code>SELECT</code> on that table using the nonclustered index, and the query takes a few seconds.  </p>