Parallel.ForEach error: The query processor could not start the necessary thread resources for parallel query execution

Question

I am having an issue when executing multiple threads using TPL Parallel.ForEach() method in c#. The multi-processing accesses the database, and I also wrap the each action with a using statements so that it disposes the connection after each execution.

The error I get says: The query processor could not start the necessary thread resources for parallel query execution

After a lot of googling, SQL is out of resources due to it being too busy and or causing some memory leaks.

I have also tried setting the ParallelOptions parameter in the Parallel.ForEach() and setting the MaxDegreeOfParallelism = 2 but this does not help. i.e.

Parallel.ForEach(customerNumbers, cno =>
{
   using (var ctx = new MyContext())
   {
       // do database call
   }
}, new ParallelOptions { MaxDegreeOfParallelism = 2 });

Can anyone provide me with a light on how to fix this?

Why are you not asking this very obvious programming question on Stackoverflow? — Security Hound
@Maxwell Maake: How many worker threads is your SQL Server set to use? See msdn.microsoft.com/en-us/library/ms190219%28v=sql.100%29.aspx — James
Also it might be helpful if you post an example of the SQL query in case you are doing something very complex that could be simplified. You can use Display Estimated Execution Plan when editing a query to see if a join or subquery is using a lot of resources. — James
Lastly, does it still happen if you move the code out of the Parallel.ForEach block? — James

DanTheMan DanTheMan · Accepted Answer · 2014-10-10T15:59:51

The problem is not on C#'s side, it's with your SQL Server. Your SQL server is rejecting multiple simultaneous calls.

Please make sure:

You have 'Multiple Active Result Sets' turned on in your connection string. See here. (This is the most likely/obvous)
You're still hitting your Max Degree of Parallelism (MAXDOP). Make sure it's set to '0' initially (meaning - use all the resources you want). If that works but you're not happy with 0, start cutting it back from there.
Take your query and move it over to SSMS and run it there. It may be the query's just too complicated, although that's unlikely. See here for more info.

Parallel.ForEach error: The query processor could not start the necessary thread resources for parallel query execution

1 Answers