March 11, 2014 at 2:51 pm
Something has been bugging me. I recently added a second processor to one of our SQL server boxes. In doing so, we had to divide the existing memory evenly between the memory banks attached to each processor. The processors are both quad core intel processors. If I set the degree of parallelism to 4 (rather than 0 so SQL can determine the number of processors to use in the Query execution) will the process only be allowed to see the memory attached to the one bank? Or does SQL still use the memory attached to the other CPU?
Thanks in advanced for you input.
March 11, 2014 at 5:27 pm
If the memory is available to the OS, SQL Server can also use it.
As for changing from 0 to 4, 0, the default, uses all processors available. You don't need to "turn it on" by setting the value. Instead, those values are set to you can limit the amount of parallelism. One change I do recommend, change the default value for Cost Threshold for Parallelism from 5 to a much higher value, 35 to 50.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 12, 2014 at 2:24 am
If you want the nitty-gritty technical details, google for NUMA SQL Server, there should be a few white papers available which discuss how SQL handles a NUMA architecture (which is what it sounds like you have).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2014 at 6:53 am
Thank you.
Out of curiosity what would upping the Cost threshold for parallelism do?
March 12, 2014 at 6:54 am
Thank you.
There is a good amount of info for me to sift through. I'll be an expert soon.
March 12, 2014 at 6:56 am
Each query has an associated cost which the query optimiser estimates for it. Upping the cost threshold changes the cost at which the optimiser will generate a parallel plan for the query (if possible) to see if the parallel plan works out cheaper than the serial.
With cost threshold at 5, any query who's estimated cost is 5 or higher gets a parallel plan generated for it (if possible), then the optimiser will check and see if the parallel plan is cheaper than the serial plan and if it is, the parallel plan is the plan that is cached and reused.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2014 at 7:02 am
Awesome. Thanks
March 12, 2014 at 7:06 am
But a cost of 5 is so cheap that plans that shouldn't be parallel can get set up as parallel, running longer and using more resources than they should.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply