February 18, 2015 at 9:51 am
When I run 'Glenn Berry's query on my server, I get followings.
LogicalCPUCount --- 40
HyperthreadRatio ---- 20
PhysicalCPUCount -----2
When I look 'system information' on window (with two lines)
processor: Intel........10core(s) 20 logical processor
processor: Intel........10core(s) 20 logical processor
I also have 'Numa' enabled 0 and 1.
My questions are:
1. Do I have 2 cores, 20 physical processor and 40 logical processor?
2. If i want to go with Microsoft recommendation about MAXDOP should I set '8'?
---------------According to the Microsoft--------
"SQL Server 2005 and later versions"
For servers that use more than eight processors, use the following configuration:
MAXDOP=8
For servers that use eight or fewer processors, use the following configuration:
MAXDOP=0 to N
Note In this configuration, N represents the number of processors.
For servers that have NUMA configured, MAXDOP should not exceed the number of CPUs that are assigned to each NUMA node.
For servers that have hyperthreading enabled, the MAXDOP value should not exceed the number of physical processors.
For servers that have NUMA configured and hyperthreading enabled, the MAXDOP value should not exceed number of physical processors per NUMA node.
February 18, 2015 at 10:27 am
So I would say it this way 2 - processors each with 10 - physical cores each - hyper threaded for a total of 20 logical processors each.
I would also go with Maxdop = 8
February 18, 2015 at 10:55 am
I appreciate your inputs JeepHound!!!
February 18, 2015 at 11:21 am
On a side note, make sure you change the Cost Threshold for Parallelism to something other than the default. That's going to affect how your processors get used in as direct a fashion as the MAXDOP will.
"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
February 18, 2015 at 11:58 am
Hmm, the way I read it, you have only two physical processors, therefore MAXDOP should not exceed two.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 18, 2015 at 12:41 pm
Grant Fritchey, Can you please put your inputs about what exactly I have and what's the 'MaxDop' no. should be?
February 18, 2015 at 2:44 pm
Scott already said. You have two processors with 10 cores each. One has NUMA enabled and one does not. Two is the safest bet. I'm not a hardware expert, so I usually defer more detailed aspects of this stuff to others.
"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
February 19, 2015 at 6:08 am
Thank you Grant!!!
February 20, 2015 at 10:30 am
To tie in with what Grant said earlier about the cost threshold for parallelism, typically your workload is far more important in determining an appropriate MAXDOP setting than your hardware. Your hardware might help determine a ceiling on reasonable MAXDOP values (a max MAXDOP, if you will), but the optimal setting will be determined primarily by your workload.
There are some decent indications of how that would go in the Microsoft advice you mention (http://support.microsoft.com/kb/2806535). In general, large reporting-style queries will benefit more from running in parallel, while very short OLTP-style queries do not. That's also why adjusting the cost threshold of parallelism upwards from the default of 5 can be so helpful, as it allows SQL Server to more intelligently let only the more expensive queries run in parallel.
As always, though the devil's in the details, and nothing beats careful evaluation of and testing against a representative workload.
As far as the reasonable limit based on hardware alone, I'm unsure why it would be 2 in this case.
If I understand you correctly, you have 2 processors, each with 10 physical cores, with hyperthreading enabled, resulting in 40 logical cores exposed to SQL Server.
I think most of the confusion arises from Microsoft's language in that article and others about MAXDOP, as they seem to use "processor" to refer to processors/sockets and cores interchangeably.
Not setting MAXDOP higher than the number of physical cores is something I can understand, if there are worries about the performance implication of allowing a single operator to run with more threads than there are physical cores (it's intuitive, I suppose, although I'd prefer testing to intuitive guidelines).
Not setting MAXDOP higher than the number of physical cores on a single processor/socket has a similar sort of intuitive sense (maybe parallel operators generally perform best if all the threads are executing on one processor/socket), but again, I'd rather test.
Not setting it higher than the number of processors/sockets is something I don't even see the intuition behind, but perhaps my intuition is broken from always preferring to test 🙂
Honestly, with 10 physical cores per socket, your upper limit on reasonable MAXDOP is also probably more affected by how efficiently the SQL Server operators scale, which in my experience does tend to break down dramatically past 8 (although again, I have seen queries that ran significantly faster with 12 or 16 cores on some machines, so score another point for testing!).
I feel like I may have already said that testing's the way to go a bit too much (is that possible?), so I'll wrap this up with a recommendation to test your most expensive and most frequently run queries with various MAXDOPs to see which works best in your situation.
Cheers!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply