SQL Server Performance Forum – Threads Archive

Windows 2000 Server with SP4 + 4 CPUs with HTT

Probably you are fed up with this questions, but I really need you help, guys, since Dell support people and Microsoft documentation are giving me inconsistent information. Consider a server with 4 Xeons (HTT enabled) running on top of Windows 2000 Server with latest SP installed. How many logical CPUs will the system use? 4 or 8?
How many logical CPUs will SQL Server use? 4 or 8?

---

8, as we have on one of our services. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

8 CPUs will be used by the OS and by SQL Server, right?

---

Represented as 8 CPUs and will be used. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

OK. Thanks a lot.

---

Win 2000 Server Standard or Enterprise Edition,
std should see only 4
EE should see 8

---

Here are your choices: Windows 2000 Standard/SQL Server 2000 Standard – 4 Processors
Windows 2000 Standard/SQL Server 2000 Enterprise – 4 Processors
Windows 2000 Advanced/SQL Server 2000 Standard – 4 Processors
Windows 2000 Advanced/SQL Server 2000 Enterprise – 8 Processors On any configuration that will use less than 8 processors, you need to disable HTT if this is a dedicated SQL Server. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

But do you mean physical CPUs or logical CPUs?

---

Logical. 2000 doesn’t really differentiate between the two. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

So I am confused again.<br /><br />I’ve got Satya and Dell guys on one side ot the barricade and Joe, Derrick and MS docs on the other. [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />Anyway, another question — I have been told that I would not be able to disable HTT on this machine (there is supposedly no such option in BIOS). Can this cause a problem with starting SQL Server?

---

??? Are you serious. You have a server from Dell that can’t disable HTT? What model of server is this? I could possibly have some docs on it if you let me know. I have a sizable archive of server specs on hand. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

I am surprised, too. But I called them and that is what they told me. I don’t know — maybe it is only a matter of BIOS update. I am doing a research on Power Edge 6600 servers, so if you have some docs on it, let me know.

---

On the DELL box look in the System BIOS under the CPU Information. It is called Logical Processing. You need to do it through the system BIOS…hit F12 (I think… but it says it on the Dell logo screen) on the Dell logo screen, and the option is in there, called Hyperthreading… set it to disabled. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

I have a spec on that server only. Do you already have that Marek? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

No. If you could send it to me (e-mail address removed by chopeen) or upload it somewhere, I’d be grateful.

---

Marek Have you treid to disable HTT on Dell box, as defined above. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

The server still exists only as a design. But I hope that I will be able to check your advice in a few days.

---

Going back to the orignal question about HT, if you are setting up a query intensive server, you have to be very carefull. SQL does not diffrentiate between a logical and physical unit. If you have enabled parralel processing, the shadows unit could slow down the response time considerably. YOu may want to do testing and perhaps use some switch (MAX DOP) in your query.

---

Do you know any articles / whitepapers, preferably Microsoft’s or Intel’s, that confirm this? Because I’m gonna need something official to convince my managers.

---

Interesting discussion which related to the issue
http://www.sqlservercentral.com/forums/shwmessage.aspx?messageid=100845 HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

I finally had a chance to check your advice. You were right. In ‘CPU Information’ section of BIOS there is option ‘Logical Processor’ that can be set to either ‘Enable’ or ‘Disable’. — ‘You’re so cute when you’re frustrated.’ — Interpol

---

So what was the action enable or disable?
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

It is a brand new machine, so it was set to enable. Last night I run a few very resource intensive jobs on this server. Tonight I will run them one more time with HTT disabled and I’ll post the results tomorrow. — ‘You’re so cute when you’re frustrated.’ — Interpol

---

I run the same jobs twice in similar conditions, only changing the HTT setting in BIOS. The jobs are mainly responsible for loading some very large reporting tables (very complex SELECTs + large INSERTs). The performance of SQL Server 2000 was slightly better (5-10%) with HHT disabled than with HTT enabled. — ‘You’re so cute when you’re frustrated.’ — Interpol

---

A very old topic, but I’ve found an interesting article: http://news.zdnet.co.uk/hardware/0,39020351,39237341,00.htm — "Recommended By Dr. Audioscrobbler."
http://www.last.fm/user/chopeen/

---

New KB article on the subject http://support.microsoft.com/default.aspx/kb/322385 Bert

---

Just some interesting reading regarding performance degradation and HT. http://news.zdnet.co.uk/0,39020330,39237341,00.htm
"Our customers observed very interesting behaviour on high-end HT-enabled hardware. They noticed that in some cases when high load is applied SQL Server CPU usage increases significantly but SQL Server performance degrades," wrote Ocks. Ocks then detailed testing which showed this behaviour where a system thread — in this case one cleaning out blocks of disk cache memory — is running at the same time as worker threads. "With Intel HT technology, logical processors share L1 & L2 caches. As you would guess [this] behaviour can potentially trash L1 & L2 caches," he said. The on-chip cache exists to speed operation by keeping copies of recently accessed data where it can be accessed without recourse to main system memory — which is much slower by comparison. Where multiple threads access different parts of memory but are simultaneously processed by the chip’s Hyperthreading Technology, the shared cache cannot keep up with their alternate demands and performance falls dramatically, according to analysis by Ocks and Ibbotson. "It’s ironic," said Ibbotson. "Intel had sold hyperthreading as something that gave performance gains to heavily threaded software. SQL Server is very thread-intensive, but it suffers. In fact, I’ve never seen performance improvement on server software with hyperthreading enabled. We recommend customers disable it when running Citrix and our software on the same server."

---

]]>