Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ERROR : The query processor could not start the necessary thread resources for parallel query execution. Expand / Collapse
Author
Message
Posted Sunday, June 06, 2010 6:16 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 10:04 AM
Points: 410, Visits: 996
job failed due to below error.

ERROR : The query processor could not start the necessary thread resources for parallel query execution.

how to resolve this?

I have checked in the server, there in no blocking and cpu usage is very low.
Post #933217
Posted Sunday, June 06, 2010 8:14 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 10:04 AM
Points: 410, Visits: 996
any help on this?
Post #933230
Posted Monday, June 28, 2010 5:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 27, 2011 6:38 AM
Points: 18, Visits: 76

I was looking at your problem and what i found out was heavy utilization of the server . Here is something from pinal dev and Amit Pandey , please see if it is helpful .
http://blog.sqlauthority.com/2008/01/30/sql-server-fix-error-the-query-processor-could-not-start-the-necessary-thread-resources-for-parallel-query-execution/
Post #943818
Posted Tuesday, February 11, 2014 9:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 18, 2014 4:02 PM
Points: 137, Visits: 403
I too have experienced this error from time to time in an SSAS job and I do not accept the 'There is no real solution for this error.' solution. Why you ask?

- This job runs daily normally without a problem. For the history I have available since 2014-01-26, the job has only failed four times, three due to this error and one was a deadlock error. Roughly, that is about 25% total failure rate (4 failures / 17 executions ), less if I exclude the deadlock error.

- This job runs at off hours (daily at 0355) when this server is barely used, if at all. Looking at the available data collected in Confio Ignite and NIMSoft, this server is NOT stressed at this time. CPU tops at less than 20% on the failure dates; it spikes at 80% on successful days but this is NOT maintained. It is a 16 CPU with 2 cores each server on Server 2008 R2 with SQL Server 2008 R2; it should be able to handle a max 960 threads.

So, I dug a little deeper. I noticed a pattern from the metrics records by Ignite but I cannot explain the cause (screenshots are attached in Word Doc):

- When the job succeeds, Ignite records one main hash (hashes in this context are an internal Ignite term, not related to query or query plan hashes) is executed for approximately five minutes. It appears to finish and then between 5 to 15 additional hashes are executed.

- When the job fails due to the thread resource error, Ignite records 10+ hashes (including the main hash) executed at one time for the first minute of the job and then spends the next four minutes processing the main hash. At the 'sixth' minute, the main has appears to have finished and several other hashes start and promptly quit due to the thread resource issue.

So it appears that parallelism in SQL Server is occasionally used up front and other times not. Perhaps the evaluated cost to go parallel is occasionally less and thus SQL chooses to do so causing the job to fail?

Anyway, I am looking for additional meaningful guidance. Any help would be appreciated. Thank you.


  Post Attachments 
dbMPSDW_ProcessSalesCube.docx (6 views, 276.26 KB)
Post #1540282
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse