dual core CPU and hyperthreading
-
Hi, general question.
I have a new windows 2003 advanced server with SQL 2000 standard edition SP4 installed.
the server has 4 dual core CPUs with hyperthreading enabled, so the CPUs appear as 16 to the OS.
My question is is it a good thing/bad thing/doesn't matter to SQL to have hyperthreading enabled with dual core CPUs, and what should the value for maxdop be (I am presuming 4 = to no. of physical processors)
cheers
george
---------------------------------------------------------------------
-
Pretty beefy box...are you running the 64bit version? I would say the more the merrier but it might be overkill depending on how much processing is going to take place here. Also one thing that seems to be huge for this is RAM. SQL is a hog when it comes to RAM sometimes, i've even had to throttle it down to be limited on some boxes becuase it would just eat up all the free memory and never give it back.
Cheers,Ben Sullins
bensullins.com
Beer is my primary key... -
I tend to frown on enabling hyperthreading on a SQL server box unless the environment is primarily quick transactions. While the logic for insuring processor affinity of a task/process/thread is much improved in Server 2003, hyperthreading is still really a poor implementation of SMT compared to other hardware out there.
The best way in your environment (if possible) is obviously to experiment with it. I tend to think in an environment where heavier longer processes are running the task switching at the processor level to support the SMT will end up reducing performance, but your mileage may vary.
-
thanks for the comments, keep 'em coming.
Its 32 bit and the box (and 2 others like it) are to be used as SQL consolidated servers supporting multiple OLTP apps, but including things like MOM and athene which are heavy users with large inserts. Not to mention things like index defrags which can be long running on these databases. Its SQL standard edition (a cost consideration) so SQL limited to 2GB RAM, the box has 4GB.
---------------------------------------------------------------------
-
Have you guys ever considered a 64 bit system
Mike
-
"I am presuming 4 = to no. of physical processors"
You are correct in your assumption. See MSKB 322385: SQL Server support in a hyper-threaded environment. It states: "Performance gains may be achieved by using a MAXDOP setting that is equal to the number of physical processors that are being used.", but also suggests testing different settings, as different application workloads can cause different cache invalidation results on hyper-threaded processors vs multiprocessor machines.
Hope this helps.
Mark -
Hi,
the best way, speaking from my experience (dozen of cluster sql installations on 4-way.8-way boxes), is to leave hyperthreading disabled. SQL 2000 is simply not written well to use parralelism and hyperthreading whitout any problems. Even MS has suggested to us couple of times to turn off paralelism and hyperthreading, as a only solution,workarround for occasional exceptions on SQL.
What I would do is to leave it off and after that, in production environment, monitor and adjust maxdop as needed. Some queries will have benefits of using paralelism, some not. It needs to be monitored and analyzed.
hope it helps,
rgds
Sinisa
-
We recently purchased a similar 4 processor dual core box for a project and I spent several days performance testing with and without hyperthreading. I found about a 4% increase in performance when I had hyperthreading off. I also found slightly better performance (with SQL 2005) AWE memory on and allowing SQL to dynamically manage memory - a feature that was not available in SQL 2000.
The only real answer is that you should come up with some scripts that you can run with and without hyperthreading and with different memory settings and see what performs best in your situation.
-
1) All but one of the numerous situations I have tested using hyperthreaded CPUs on SQL Server boxes resulted in OFF being faster - often significantly so. The remaining situation was a negligible difference.
2) SQL 2000 Standard Edition is limited to 4 CPUs, so you have way more box than it can access anyway. You should definitely turn hyperthreading off. Here is a snippet from BOL: "For example, you can install SQL Server Standard Edition on an eight-processor computer running Windows 2000 Advanced Server, but the database engine will not use more than four of the processors."
3) Like others have said, consider an upgrade to SQL 2005. RAM limit for Std Edition is "OS maximum", which would pick you up a 50% RAM increase - in addition to numerous other "for free" benefits. Unfortunately the 2005 Std Edition is also limited to 4 CPUs.
4) Perhaps you could use a second instance on this box? That would allow you to use processor affinity and pin each edition to specific cores and effectively use all 8 "cpus" (with hyperthreading disabled!!). This really requires sufficient disk I/O capability (which is VASTLY different from CAPACITY!!).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
thankyou very much for all the replies.
The 4 CPU limit on standard edition is a good point. But is it limited to 4 slots, or 4 cores?
SQL standard is able to take advantage of hyperthreading on all 4 CPUS, are we saying it cannot use all 8 cores when there are only 4 physical slots?
cheers
george
---------------------------------------------------------------------
-
Ummm... upgrade to Enterprise Edition??? Why have a muscle car if you're just going to use if for a golf cart?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Isn't Enterprise Edition 5 times as expensive as Standard?? That could be one reason for not upgrading! 🙂
I actually don't know the answer to the 4CPU or 4Core question. I recall that licensing is per CPU (processor), but I don't know if Standard edition will fully utilize all 8 cores of a 4 CPU, Dual Core machine. Anyone have direct knowledge of the answer?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
I do, I do!! The 4 CPU limit is logical processors. So that's 4 single CPU's, 2 double core CPU's, 2 single core hyperthreaded CPU's, 1 2 core hyperthreaded CPU, or 1 quad core CPU. The easiest way to remember it is that it is based on the number of processors that SQL Server sees.
If you're really concerned with performance, you should make the move to SQL 2005. This is a much better return on your investment than upgrading SQL 2000 standard to Enterprise. For the best performance, you should use 64 bit SQL Server 2005 SP2 + on Windows 2003 SP1 +. I have not yet tested SQL Server on Vista, though it is what I am running on my laptop.
If your system is dealing with many small to medium transactions, you'll see the benefits of the hyperthreaded logical CPU's. If you process large data sets or other very large transactions, you will notice some performance degradation.
The reason for this is that in a non-hyperthreaded architecture, SQL Server manages the worker threads in a way that keeps the workload fairly spread out across all logical processors. With hyperthreading, a worker thread gets tied directly to a specific processor. When processing a very large transaction, your process could completely peg out the 1 CPU that it is tied to while the remaining CPU's remain relatively lifeless. With the NUMA architecture (hyperthreading -- non-uniform memory access), Specific logical CPU's are mapped to specific areas of memory and specific I/O channels. So it is relatively easy for a hyperthreaded CPU to get maxed out. The plus side is if the process maxes out its CPU, the other CPU's are still completely free to process other transactions.
8 logical processors without hyperthreading is better than 8 logical processors with hyperthreading, in my opinion.
The maxdop option is one of those things that is best left alone unless you have a specific reason to mess with it. Maxdop should be set to 0 to allow SQL Server to make the decision. As quoted, Books Online says that you MAY get a performance boost by setting the maxdop option. In BOL terms, MAY = WILL NOT 99.99999999% of the time.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
They bought a monster machine... thought they should have some monster software that can actually use all of it
And, of course, it should be SQL Server 2005 if they have the time to change some of your code that will break.The company I work for did something similar... 8 CPU box with memory and disk space out the wazoo... they couldn't understand why there was little performance gain. Got them to switch to the Enterprise Edition (they didn't upgrade to 2005 because of 3rd party requirements
) and the performance of even poor code greatly increased (more than doubled in most cases and a lot fewer waits).Still, not the panacea that some would think... no substitute for proper database design and good code
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Robert;
I'm not sure you are entirely accurate here. According to MSKB 322385: SQL Server support in a hyper-threaded environment, SQL Server (2000 SP3 and later or 2005) will recognize hyperthreaded processors and adjust the licensing code to handle the additional logical processors. So SQL Server Std will allow and use more than the 4 CPUs that the OS shows.
Also, on Microsoft's SQL Server Multicore Licensing Policy article, they indicate that it a dual-core processor counts as 1 processor for SQL Server per-processor licensing. However, that seems to only address licensing costs, not actual performance. But if SQL Server does follow these articles, then SQL Server Std will recognize and use all 16 logical processors on George's server.
The questions are (given that SQL Server adjusts itself to allow multiple logical processors per physical processors):
- Does SQL Server count a dual-core processor as 1 or 2 physical processors?
- Does SQL Server count physical processors as number of cores, or number of slots that a processor chip resides in?
- Does a dual-core with hyperthreading on allow SQL Server logical-to-physical ratio to be 4-1, or is it still 2-1 if it cannot tell if the dual-core is not separate physical processors?
Mark
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply