November 11, 2010 at 4:59 am
Hi all
Our new environment has 2 processors each of which are quad core. When I went to the server properties in SQL I could see it was returning 16 processors as the number of processors installed. I checked and the processors are capable of spawning 8 threads each so I am assuming SQL is picking up this total?
If this is the case then for things like TempDB I should be creating a file per thread rather than per core as I previously thought?
Thanks
November 11, 2010 at 5:28 am
2 quad core processors means that you have 8 physical cores in the server. Are they recent processors (i7 for eg) If so, they may have hyperthreading which means there are a total of 16 'logical cores'.
As for how many tempDB files, that's another matter.
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
November 11, 2010 at 5:36 am
Sounds like the cores are hyperthreaded.
Which brings up a tangent of mine - I have heard that it is "best practice" to disable hyperthreading on a windows server running sql server. Is that still true in sql 2008? Couldn't you get away with not disabling it by configuring the affinity mask?
November 11, 2010 at 5:41 am
It was true of the old hyperthreaded processors (the ones that were released prior to the true multi-core processors). It may not be true of the new hyperthreading that's in the i7 (and similar) processors. Is worth a lot more reading up on.
No, changing the affinity mask is not the same as disabling the hyperthreading in the bios.
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
November 11, 2010 at 5:49 am
These are Intel Xeon E5540 chips. Thanks for the link about TempDB btw.
November 11, 2010 at 5:56 am
getoffmyfoot (11/11/2010)
Sounds like the cores are hyperthreaded.Which brings up a tangent of mine - I have heard that it is "best practice" to disable hyperthreading on a windows server running sql server. Is that still true in sql 2008? Couldn't you get away with not disabling it by configuring the affinity mask?
Its a good question, I used to disable Hyperthreading on all my SQL Servers, prior to the new multi-core CPUs. Not sure if the "rule" should stand with the new CPUs.
November 11, 2010 at 7:08 am
grahamc (11/11/2010)
getoffmyfoot (11/11/2010)
Sounds like the cores are hyperthreaded.Which brings up a tangent of mine - I have heard that it is "best practice" to disable hyperthreading on a windows server running sql server. Is that still true in sql 2008? Couldn't you get away with not disabling it by configuring the affinity mask?
Its a good question, I used to disable Hyperthreading on all my SQL Servers, prior to the new multi-core CPUs. Not sure if the "rule" should stand with the new CPUs.
I definitely read somewhere (may have been a recent database weekly link) that the very,very latest CPUs handle hyperthreading much better, so the old 'turn it off for OLTP' rule would no longer be a given.
---------------------------------------------------------------------
November 11, 2010 at 7:17 am
ta da!
---------------------------------------------------------------------
November 11, 2010 at 1:00 pm
I had a relatively new box (a year and a half old by now) running a very heavily hit OLTP system fine for months. All of a sudden performance ground to a halt, and I couldn't find the issue.
It ended up being hyperthreading. There is a certain load point at which the system can't handle it, and the logical cores end up getting overloaded and threads start hanging. This was with MAXDOP 1 also.
Leave it on for now, but if all of a sudden you see a massive performance drop, disable it. After that happened, I blanket disable HT on all boxes. When our next hardware refresh comes around, I may re-eval that.
November 15, 2010 at 9:30 am
If I can follow this up a bit further now. It seems our server use NUMA although we only have 2 physical CPUs with the 4 cores as mentioned in the title. When running the BPA it tells me that my MAXDOP isn't set correctly.
The question I have now is based on the following article
http://support.microsoft.com/kb/2023536
If my server is using NUMA which looks to be 2 node with 1 physical CPU and 4 cores then it seems the recommended value for MAXDOP should be 4.
However if I am also using Hyper Threading the the recommended value looks to be 2 to represent the number of physical processors.
So which is the correct value?
Thanks
November 15, 2010 at 1:33 pm
Set your MAXDOP to half of your physical core count, ignoring hyperthreaded cores.
If you're running 2x Dual Core procs with HT, then you have 4 physical cores. Maxdop 2 would probably be ideal, but of course it depends on the kind of work the engine is doing.
November 15, 2010 at 1:48 pm
There's no single ideal setting for MAXDOP. This is one of those 'It Depends' scenarios. It depends what you're running.
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
November 15, 2010 at 2:01 pm
GilaMonster (11/15/2010)
There's no single ideal setting for MAXDOP. This is one of those 'It Depends' scenarios. It depends what you're running.
As with everything in SQL 🙂
But, one thing you really should never do in an OLTP system is set maxdop 0 or to your total number of cores. You can and will get a runaway process that will inevitably lock every core and your front end will grind to a halt. I also still do not trust the sql/ht relationship enough to keep HT enabled on any of my boxes just yet.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply