viernes, 23 de octubre de 2009 10:57
I’m having some problems with the processing of my cubes in production! From time to time I’m getting the following error: “OLE DB error: OLE DB or ODBC error: The query processor could not start the necessary thread resources for parallel query execution.; 42000. “
The process fails 2 times on 3 which is unacceptable. After updating the DWH, I do a Full Process of my OLAP database and changed the Processing Order to: “Sequential” with Transaction mode: “One Transaction”
It’s a SQL2005 standard version running on a 64 bit environment.
Todas las respuestas
viernes, 23 de octubre de 2009 13:29ModeradorHi, I have found this blog entry that might explain but is of limited help: 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/ BR Thomas Ivarsson
viernes, 23 de octubre de 2009 13:48
That is what worries me, in the workaround they are telling that there is no real solution for this error. Isn’t there a setting where I can decrease the resources that analysis services is using. We never had any problems in our test environment, which is a lot less powerful than the production server. Note that the production server is a dedicated SQL Server. However I have to mention that our test environment was a 32 bit server and the production is a 64 bit. Are there any other logs, system logs that I can consult? The application log gives the same result as mentioned above.
Thanks in advance,
viernes, 23 de octubre de 2009 14:15Check your SQL Error log. Odds are that you'll find an error stating "Limit on 'Max Worker threads' reached."
If so, try setting MAXDOP to throttle the degree of parallelism.
- Marcado como respuesta Thomas IvarssonModerator miércoles, 23 de mayo de 2012 17:07
viernes, 23 de octubre de 2009 15:06Dear,
The error in the logfile was the same as the error mentioned above.
I've checked the Max degree of paralism and it's set to "0"
Thx in advance,
viernes, 23 de octubre de 2009 15:38ModeradorI would check the memory resources consumed by the database engine. SQL Server takes all memory it can get. SSAS only takes a percentage of what is not reserved by other applications.
sábado, 24 de octubre de 2009 1:35Hi Bram,
Try setting MAXDOP=4 to throttle the degree of parallelism.
martes, 27 de octubre de 2009 8:12
Hi John, Thomas,
Thank u for looking at my problem, at this moment I didn’t changed anything and the process is running successfully for 4 day’s now. If the problem reoccurs, I will test the proposed solutions and keep you informed!
Thanks a lot,
miércoles, 23 de mayo de 2012 11:13
I just had this isue today 05/23/2012, I have a robust environment but seems like Analysis Engine can't figure out how many parallel Processes it can run to handle the Cube processing, seems like a bug to me.
ole db error: ole db or odbc error: the query processor could not start the necessary thread resources for parallel query execution.; 42000
So to work around it, I went under "Change settings" and forced the parallel execution to 4 and it is now processing... i hope it will go back to normal after help desk installs the pending patches or it seems that I will need to restart SSAS services