March 6, 2012 at 3:48 pm
Hello,
We recently built a new machine to host our SQL database. This new machine contains dual processors (our old machine was a single processor). Since we have made the switch, I have come across several instances where we see this error:
"The query processor could not start the necessary thread resources for parallel query execution."
We never saw this error on our old server and the database has not gone through any changes since the switch. Does anyone have an idea on how to solve this problem? There isn't much information on this error message.
-ATC
March 6, 2012 at 3:57 pm
atchristian (3/6/2012)
Hello,We recently built a new machine to host our SQL database. This new machine contains dual processors (our old machine was a single processor). Since we have made the switch, I have come across several instances where we see this error:
"The query processor could not start the necessary thread resources for parallel query execution."
We never saw this error on our old server and the database has not gone through any changes since the switch. Does anyone have an idea on how to solve this problem? There isn't much information on this error message.
-ATC
What is the sql version / windows version and how many CPU's ?
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
March 6, 2012 at 4:00 pm
On your old hardware, I suspect that SQL Server didn't calculate a parallel query plan (since there was only one CPU); on your new hardware, the plan is being constructed to use multiple processors, but during the actual execution one (or both) processors are overwhelmed.
What's the nature of the query you are trying to run? How much RAM do you have on the box? What are the specs on the processors?
March 6, 2012 at 4:03 pm
SQL: Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) Sep 21 2011 22:45:45 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Windows: Server 2008 R2 x64 (Enterprise)
There are two physical CPUs viewed as 24 available to the OS
March 6, 2012 at 4:14 pm
We have 48GBs of RAM and 38 of that is dedicated to SQL (nothing else runs but I wanted to leave some breathing room).
The processors are Xeon E5645 (two total)
The query that this happens on it a very intense analysis query. Would it be possible/ideal to inform this sproc to use only one processor for the execution? Is that possible do do? Thanks again for all of the help!
I forgot to mention that the analysis query is invoked by a running application. We run 40+ instances of this query at once (threaded on the application side).
March 6, 2012 at 4:51 pm
atchristian (3/6/2012)
We have 48GBs of RAM and 38 of that is dedicated to SQL (nothing else runs but I wanted to leave some breathing room).The processors are Xeon E5645 (two total)
The query that this happens on it a very intense analysis query. Would it be possible/ideal to inform this sproc to use only one processor for the execution? Is that possible do do? Thanks again for all of the help!
I forgot to mention that the analysis query is invoked by a running application. We run 40+ instances of this query at once (threaded on the application side).
You might have to look at MAX DOP setting and try to adjust it.
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
March 6, 2012 at 5:30 pm
atchristian (3/6/2012)
The query that this happens on it a very intense analysis query. Would it be possible/ideal to inform this sproc to use only one processor for the execution? Is that possible do do? Thanks again for all of the help! I forgot to mention that the analysis query is invoked by a running application. We run 40+ instances of this query at once (threaded on the application side).
Yes, you can set the maximum number of logical execution units to use concurrently per query with the MAXDOP query hint, the server-wide max degree of parallelism option, or Resource Governor. Adding OPTION (MAXDOP 12) to the query would simulate the situation you had before, with one processor (and 12 cores). You might like to experiment with lower numbers to find the sweet spot for your particular needs. MAXDOP 4 might be a reasonable starting point, though you could also try MAXDOP 1 for serial execution considering you run 40+ instances of this query at once.
In case you are interested, the error message you received was caused by SQL Server running out of worker threads. The server reserves the number of threads it expects to need just before query execution starts, but the algorithm is not perfect and may underestimate the requirement in some cases. SQL Server does contain logic to dynamically reduce the degree of parallelism to reduce the thread count, but timing issues can result in threads being over-committed regardless. The solution is generally NOT to increase size of the worker thread pool - you should instead look to tune the query to use resources more efficiently, and reduce the DOP each query runs at. All the normal query tuning rules apply - measure your server to identify the resource bottleneck and direct your tuning efforts appropriately.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply