dual core CPU and hyperthreading
-
Yes, I am entirely accurate. SQL Server's 4 processor limit has absolutely nothing to do with licensing. Licensing is based on physical processor, but SQL Server itself sees logical processors.
You are confusing licensing with architecture. A single double-core hyperthreaded processor will fill the 4 processor limit of SQL Server even though you only have to have a license for 1 CPU. In this case, SQL Server sees 4 processors, but MS licensing sees only 1.
Make sense?
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] -
But, Robert, did you read the MSKB article?
When hyper-threading is enabled, the Basic Input Output System (BIOS) uses a logical to physical CPU ratio. Current implementations are using 2:1 ratios. This means that there are two (2) logical CPUs for each physical CPU. These ratios may change in the future; however, Microsoft SQL Server 2000 Service Pack 3 (SP3) has additional licensing code that adjusts the licensing restrictions to handle the ratio. For example, if the SQL Server product code that you have installed permits a 4 CPU license at a 2:1 ratio, the SQL Server 2000 SP3 build adjusts and lets you use 8 CPUs.Licensing and architecture are interrelated. The architecture will limit the number of processors based on licensing (the product code entered at installation). SQL Server architecture does not "play dumb" and only allow the logical processors the OS presents. It does have code to extend the architecture to utilize multiple logical processors.
Let's extend your example and have 2 dual-core hyperthreaded processors. Windows Server 2003 will present 8 logical processors. If you have a 1 processor license Standard edition, SQL Server will only use 4 (of the 8) logical processors. If you have a 2 processor license on SQL Server Std edition, SQL Server will see and use all 8 logical processors, and will not be limited to the 4 processors stated for Standard edition.
Mark -
The quote you posted is ONLY talking about licensing. It's not addressing what your SQL Server can use. In order for your SQL Server 2000 SP3 machine to be able to use the 8 logical CPU's, you have to be using Enterprise Edition, because Standard Edition only supports 4 CPU's.
If I wanted to risk the financial penalties, I could buy a single processor license for Enterprise Edition and run the actual SQL Server on 8 processors.
Did you see this part of that KB article? It means that SQL Server would see 4 hyperthreaded CPU's as 8 CPU's. In order to utilize all 8, SQL Server would have to support that many CPU's, i.e. Enterprise Edition. If your SQL Server only supports 4 CPU's, you would need to buy licenses for 2 CPU's.
Processor mapping
By default, SQL Server creates a logical User Mode Scheduling (UMS) scheduler for each processor according to the ordinal processor order. In hyper-threaded enabled environments, Microsoft Windows makes all the processors available to the SQL Server process as if they are true physical processors.
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] -
I think the Q&A from MSDN should shed some light on the issue:
http://www.microsoft.com/technet/technetmag/issues/2007/05/SQLQA/
Q In addition to hyperthreading and dual-core technology, chip vendors are beginning to release processors with additional cores (four, eight, and more). I am considering purchasing a new server with multi-core processors to support a SQL Server 2005 Standard Edition deployment and am curious if, when using a four-core processor, I will only be able to make use of a single physical CPU (since Standard Edition is limited to four CPUs)?
A For the purposes of both licensing and CPU edition support, SQL Server considers only the number of physical sockets/CPUs, regardless of the number of cores on the processor. So, for example, the fact that SQL Server 2005 Standard Edition supports up to 4 CPUs means it will support 4 physical CPU sockets, regardless of the number of cores in each one (if you have 4 physical CPUs with 4 cores each, your Standard Edition deployment would have 16 logical CPUs to make use of). Moreover, even though you have 16 cores/logical CPUs, the licensing requires only that you pay for the 4 physical CPUs, not all 16 cores. For more information on SQL Server and multi-core, see microsoft.com/sql/howtobuy/multicore.mspx.
-
Robert;
1) The quote I posted did address licensing, and it said what SQL Server will use: "...the SQL Server 2000 SP3 build adjusts and lets you use 8 CPUs." (emphasis added).
2) The product key for 1 processor license and 2 processor license are different, and SQL Server Enterprise will restrict the number of available processors it uses bases on the product key entered. Of course, this may not be true for Volume License keys. Only if you have a quad core hyperthreaded processor will you be able have a single processor license be able to use 8 processors.
3) Yes, I saw that part of the article also. The point is, SQL Server will extend the number of logical CPUs it can and will use based on detecting multi-core and hyperthreaded physical processors. These articles seem to indicate that SQL Server does not "play dumb" and think the logical processors are physical processors. It calculates the ratio of logical to physical, multiples the number of allowed physical processors by this ratio, and allows that number of OS logical processors to be used. So, in George's server, with dual-core hyperthreaded processors, the ratio would be 4 logical to 1 physical, and SQL Server Standard Edition would use all 16 of logical processors the OS presents.
Mark -
Thanks, Carl. That says it succinctly and seems to clear up our questions. Good link.
Mark -
I'll try to track someone down at MS that can answer the "use" question.
At TechEd, in one of the tech talks, someone asked about hyperthreading, given all the multi-core options, and the response from a few technical developers at MS was leave it off. They had never considered it when SS2K was being developed and their code didn't run well in many situations. The ones in which it did were not so common, so the general advice is hyperthreading off.
The licensing/use question is interesting because of whether the limitations in Standard will be enforced. Even if you can "use" 8 virtual CPUs (not sure what to call them), I wonder if the query processor will use them. The code between Enterprise and Standard is the same, AFAIK, with things either being disabled in Std or added in Ent.
-
Wow, there's a whole bunch of stuff oozing out of Pandora's box in here!
Let's see if I can summarize some wrap up points real quick:
Standard Edition will support up to 4 physical sockets, but generally-speaking, if they're dual core CPUs, SQL Server will behave as if it's an 8-way for the purposes of creating schedulers, parallelization, etc. I've never tried to install Standard Edition on a 4-way dual-core myself, but only because I don't generally use Standard Edition for anything. If it does NOT behave as described in the Licensing FAQ, then you probably have a genuine greivance and we'll get it fixed. Multi-core processors are a good way to ALMOST get an 8-way on a 4-way budget. There are some downsides, depending on the L2 cache structure, how the cores share it, etc, so don't expect to benchmark it side-by-side with a "real" 8-way and break even. A 4-way dual-core will most likely always lose to an 8-way, all other things being equal. See the Licensing FAQ and SQL Q&A re: multiple cores and Standard Edition features.
Turn off hyper-threading unless and until you have personally and thoroughly tested your application with hyper-threading turned on in a pre-production environment. Running a few queries is NOT enough. If you don't have an automated test suite or 1-million QA-trained monkey ninjas at your disposal... Slava is pretty authoritative on the subject: Slava on HT. It boils down to the 12th Commandment: Thou shalt know thine workload. (Which comes right after that little chant about the key, the whole key and nothing but the key in #11.) If hyper-threading works for you, great! Consider it a bonus!! (I've overheard PFEs talking about it, and odds are good that you'll be turning it off if you ever have to call Premier for support.) Remember that the CPU support depends largely on the o/s, so be sure you're on the latest Windows Server 2003 Service Pack. See Performance & Scaling.
I'm sure that there are questions, so... I surrender the balance of my time to the chairman for further debate.
Obligatory Dislcaimer: These opinions are my own, based on my professional experience, secret insider knowledge, yadda, yadda. They are provided here as-is and without warranty.
-
Wow. Thanks, David. We appreciate the info.
Mark -
a good link on HT: (but a bit thick...) http://blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx
-
Thanks David, and for the record, I pinged him on this. He's a SQL Ranger, a highly specialized consultant for MS that's gone through a 6 (or 8?) week intensive study course in Redmond with lectures and classes taught by the likes of Paul Randal and other developers.
Course, he's kind of a high end guy, so I had to remind him that Standard Edition still exists and is used by us normal folks
-
Hey, how can I get to be one of those SQL Ranger type dudes?!?!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
I'm sure that y'all're cool enough, so it's really simple. And like all really simple things, it's really hard. That's a corollary to Murphy's Law, or one of the commandments, I forget which.
Here's the formula:
Your SQL Server Coolness
+ having all of the prerequisite ITPRO exams
+ five weeks of onsite training by the SQL Server product team
+ passing all of the course exams > 80%
+ passing all of the hands-on logical and physical course labs
+ passing the SQL Ranger review board (the most fun part!)
+ $25,000 (to pay for the course)
= SQL Ranger a.k.a. MCA: Database
Here's the official skinny on the MCA: Database. There have been three rotations of SQL Ranger class so far. Rumor has it that the pass rate is around 20% to 25% of candidates. I think 5 of us out of 19 passed during Rotation 3. Because of the time commitment and the cost, it's really only targeted at Microsoft employees and employees of partner consulting companies.
I did manage to find time for one blog post about it, after the fact.
I've been through lots of training (and delivered lots as an MCT), and there is no training like this anywhere else for SQL Server.For the record, I have one more ITPRO exam to "catch up" before I can be granted the official blessing and be annointed with sacrificial lamb oil or some such.
(I actually just want the leather jacket and new business cards.) -
many thanks to everybody for their comments.
I will be recommending hyperthreading be turned of at the BIOS level.
I am loathe to push this any further but I have a strong feeling the customer could come back and refuse to turn off HT - so is using processor affinity in SQL server to only use the physical processors (slots and cores) and not the logical CPUS a viable alternative?
---------------------------------------------------------------------
-
David Reed: I had looked into the MCA and decided not to pursue it due to cost and the month+ onsite at Redmond. I was hoping that SQL Ranger was something other than MCA! Oh well, guess I will have to stick to getting the MVP as the pinacle of my SQL Certifications. Congrats on passing though - sounds like a real PITA!!
George: I too wondered if you could pin sql server to the actual 'physical' core and keep it off of the 'hyperthreaded-virtual' core using affinity. I would think you could, although you are still open to OTHER processes causing the same thrashing that can hinder performance. I would expect the net result to be positive however if the box is a dedicated SQL machine.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply