CODEWITHSUNDEEP
c#parallel-processingmultithreading
Maxwell Maake
Maxwell Maake

Reputation: 193

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

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?

Upvotes: 2

Views: 5250

Answers (1)

DanTheMan
DanTheMan

Reputation: 3277

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:

  1. You have 'Multiple Active Result Sets' turned on in your connection string. See here. (This is the most likely/obvous)
  2. 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.
  3. 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.

Upvotes: 3

Related Questions