August 17, 2011 at 1:46 pm
BOL: max degree of parallelism Option
you can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution.
Question is :
What is the "number of processors" here really mean ? It's physcial or logical ? I have box with 2 Physical Inter Xeon Processors, but both are 4 Cores, so 8 Logical Processors, do i have 2 processors or 8 processors ?
we got lots of timeouts, and CXPACKET is No 1 contributed to waitng list, so , i am thinking: should I limit the max degree of parallelism to 1 ( if we have 2 processors ) or to 4 ( if we have 8 processors) ?
Any imput will be highly appreciated !
ddfg
August 17, 2011 at 2:00 pm
You have 2 sockets and 8 processors.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 17, 2011 at 2:01 pm
If you look at how many processors SQL sees, you will see it says 8. If you want to set MAXDOP to try elliminate the CXPacket waits then 1 is the correct setting. Try 4 and you will see the problem doesn't go away....
However, from SQL 2008 on I'd not be in a hurry to set this to 1 (unlike 2005 where I'd tend to recommend it). It may be worth your while to investigate the problem queries and rather use the MAXDOP = 1 query hint just on that those sections of the query that are causing an issue. This has a less impact on the rest of the environment, much of which may be benifitting from the high MADOP setting.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 17, 2011 at 2:03 pm
SQLRNNR (8/17/2011)
You have 2 sockets and 8 processors.
Except if you licence Per Processor, you only pay for 2 licences, not 8.
This took me for ever to get my head around.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 17, 2011 at 2:08 pm
Leo.Miller (8/17/2011)
SQLRNNR (8/17/2011)
You have 2 sockets and 8 processors.Except if you licence Per Processor, you only pay for 2 licences, not 8.
This took me for ever to get my head around.
Leo
Correct. Licensing is a bit different (and may be changing again from rumors I heard). Licensing is socket based for now.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 17, 2011 at 2:24 pm
Oracle licence per core and get away with it, shouldn't think it will be long before MS capitalise on it too
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 17, 2011 at 5:56 pm
From BOL:
"The maximum value for thedegree of parallelism setting is controlled by the edition of SQL Server, CPU type, and operating system. If a value greater than the number of available processors is specified, the actual number of available processors is used. If the computer has only one processor, the max degree of parallelism value is ignored. and for SQL Server 2008 Standard, the value is 4"
Now for my server which has 2 physical CPUs and 8 logical processors, how many "the actual number of available processors" is ? is it 8 or 2 ?
ddfg
August 17, 2011 at 6:01 pm
awu (8/17/2011)
From BOL:"The maximum value for thedegree of parallelism setting is controlled by the edition of SQL Server, CPU type, and operating system. If a value greater than the number of available processors is specified, the actual number of available processors is used. If the computer has only one processor, the max degree of parallelism value is ignored. and for SQL Server 2008 Standard, the value is 4"
Now for my server which has 2 physical CPUs and 8 logical processors, how many "the actual number of available processors" is ? is it 8 or 2 ?
I answered that question. You have 8 physical cpus and 2 sockets. Each core is a cpu. Logical cpus comes into play if you have hyper-threading enabled.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 17, 2011 at 7:08 pm
Thanks Jason, but I am still puzzling over something here, if you right , it's 8, then i can set : max degree of parallelism = 6, leave two CPUs to something else all the time , right ? But on the other hand, it's saying max numbers of CPUs the SQL Server 2008 R2 Standard edition support is 4, is that mean i should set the number less than 4 ? Confusing ....
ddfg
August 17, 2011 at 9:01 pm
I would start with setting it at 4, test, baseline and then try a different value if necessary.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 18, 2011 at 1:14 am
awu (8/17/2011)
it's saying max numbers of CPUs the SQL Server 2008 R2 Standard edition support is 4, is that mean i should set the number less than 4 ? Confusing ....
SQL server will only ever use 4 cores
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 18, 2011 at 2:15 am
awu (8/17/2011)
Thanks Jason, but I am still puzzling over something here, if you right , it's 8, then i can set : max degree of parallelism = 6, leave two CPUs to something else all the time , right ?
That's not how MaxDop works. With MaxDop you're setting what the maximum number of threads any particular query operator can parallel to, not the number of cores in use.
But on the other hand, it's saying max numbers of CPUs the SQL Server 2008 R2 Standard edition support is 4, is that mean i should set the number less than 4 ? Confusing ....
You can set to whatever you like, but Standard Edition will never use more than 4 cores in total. It's a limitation of the edition.
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
August 18, 2011 at 1:30 pm
GilaMonster (8/18/2011)
but Standard Edition will never use more than 4 cores in total.
Can you point me to a document on this? I'm not convinced that standard can only use 4 cores since I know of sites with 2 x 4 core procs and SQL Std is using all 8 cores. It seems to me since licensing is per socket that std is capable of using 4 sockets. I've yet to find a definitive document that explicitly states "SQL Server Std can use a maximum or 4 cores". All the official docs I've seen say 4 CPUs which is really ambiguous.
To be sure I just downloaded the SQL 2008 Enterprise and Standard Feature Compare PDF from Microsoft and it says CPUs.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 19, 2011 at 3:07 pm
Another setting to play around with: cost threshold for parallelism.
Default value is 5 sec; may be too low for most systems.
Setting it to higher values (say 15-20 sec) may help prevent most queries from parallelizing except for the most expensive ones.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 19, 2011 at 5:08 pm
Leo.Miller (8/18/2011)
Can you point me to a document on this?
All I'll do is hit google. I'm sure that you can do that as well as I can.
The point to note is that SQL can't tell the difference between a processor and a core. If Windows tells it that it has 8 cores available, SQL cannot tell whether that is one 8 core processor, 2 quad-cores, 4 dual-cores or 8 single-core chips.
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 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply