December 2, 2008 at 9:35 am
Hi Guys
We have currently upgraded our SQL machine to 2005 64 bit standard. Clustered.
The idea was to move production databases and back office to one machine, there is a second identical machine Node in the cluster as a fail over.
To see if I’m understanding this correctly I want to use only 1 node in my example, we'll probably digress from there.
Nice machine, quad core duo. It has 4 physical processors but sees it as 16.
The engineers who did the installation recommended that that we set the max degree of parallelism to 8.
After reading countless web pages, still don’t get it. 8 of the 16 Cpu’s will handle a single query execution but in parallel, so more than one query execution runs over the 8 parallel processors at the same time..
My confusion is this..
1.) Are the remaining 8 Cpu's being used in query executions run in series or are they idle?
2.) If the 8 parallel Cpu's are used to their full capacity, does it overflow to the Cpu's in series, and how would that be handled?
3.) How does SQL determine what queries should be handled as parallel and what in series?
4.)Is the max degree of parallelism needed to manage concurrent connections or to better use the Cpu power available on the machine?
Thanx.
December 2, 2008 at 9:50 am
Mark Kinnear (12/2/2008)
My confusion is this..
1.) Are the remaining 8 Cpu's being used in query executions run in series or are they idle?
Setting the degree of parallelism to 8 means that if a query runs in parallel (which not all will) it will only spread over max 8 processors. Which 8 (and in fact the exact number) is dependent on the load at the time of the query's execution, it's not fixed that these 8 are for parallel and these 8 are not.
If one query's running parallel over 8 processors, the other 8 may all be running non-paralleled queries, they may all be running another query paralleled over 8 processors, some of them could be running a parallel query (but over fewer processors), etc.
They'll be doing something else though
2.) If the 8 parallel Cpu's are used to their full capacity, does it overflow to the Cpu's in series, and how would that be handled?
It won't overflow. If the maxdop is 8, that means that max 8 threads will be running for one query at a time. Which 8 processor those threads are running on is not important, nor is it determined before the query starts running.
3.) How does SQL determine what queries should be handled as parallel and what in series?
Depends on the complexity of the query, the amount of data and those threthe operations within that query.
4.)Is the max degree of parallelism needed to manage concurrent connections or to better use the Cpu power available on the machine?
Concurrency. It's so that one query can't take all of the processing power in the server.
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply