Can parallelism cause 100% CPU usage?
I realize this question is kind of vague, but I'm hoping someone can point me in a direction of things to look at.
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.
The query in question spent about 20 minutes running the server at 100% CPU, at which point I killed the query. I used
sys.dm_exec_query_stats 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.
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.
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.
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?
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.
For reference, I can run a
SELECT on that table using the nonclustered index, and the query takes a few seconds.