SQL Server Performance Forum – Threads Archive

CPU are not used properly…

Hello all, I’m currently facing a performance problem. I tried different things
without finding the problem… I have two machine on which I have installed sql server 2000. The first machine (A) is running windows server 2000, it has 1 Pentium
4 processor and 1Gig of memory. Here is the output of: select
@@version
Microsoft SQL Server 2000 – 8.00.194 (Intel X86) Aug 6 2000
00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Enterprise
Edition on Windows NT 5.0 (Build 2195: Service Pack 4) The second machine (B) is running windows server 2003, it has 2 XEON
processor and 2 Gigs of memory. Here is the output of:
select @@version
Microsoft SQL Server 2000 – 8.00.194 (Intel X86) Aug 6 2000
00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Standard
Edition on Windows NT 5.2 (Build 3790: ) On both machine, I have the same database and I am running the
following query which it returns 269000 rows : SELECT *
FROM T_Membres
INNER JOIN T_Cartes ON T_Membres.Mmb_Id_Membre =
T_Cartes.Cartes_Id_Membres_FK
INNER JOIN T_Clubs ON T_Cartes.Cartes_Id_Club_FK = T_Clubs.Clubs_Id
On the machine (A – 1 Pentium 4), I’m getting the result within 30
seconds. And when the query is executed, the CPU usage is going above
95%. But, on the machine (B – 2 XEON), I’m getting the result within 30
MINUTES. And when the query is executed, the CPU usage stays between
1 and 3%. Following this result, I configured machine (B) to use only 1
processor to see if this change the performance, but I got the same
result… Is anybody can help me explain this situation? Is that can be cause
by a compatibility issue between windows server 2003 and sql server
2000? Thank you in advance for your help…

---

SQL 2000 8.00.194 sound without service pack to me.
Windows server 2003 works with SQL 2000 service from pack 2.
So I suggest to install last service pack in both and try again.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Are the databases on both servers really exactly the same?
Is SQL Server configured exactly the same on both servers?
Have you updated the statistics on both databases before running this test?
Are the results consistant, time after time on each server?
Is the query plan identical between the two servers? If not, how are they different? I have not seen any issues with SQL Server 2000 on Windows 2003, so my assumption is that something is different between the two servers. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

---

Hi Luis, You were right, I was using SQL Server 2000 without service pack installed. I installed SP3 on my machine B (XEON Processors) and ran my tests again. The result is the same as before, it is still take around 30 minutes to execute vs. the 30 seconds on machine A (Pentium 4). Do you know if the hyper-threading technologie may cause a problem ??? Roger

---

Hi Brad, Thank for your response… Here the answers to your questions: 1) Yes, both databases are the same, structure and data…
2) I compared server properties, both are the same… Excepted for the memory since machine A has 1 Gig and Machine B has 2 Gig of RAM
3) Statistics has not been updated on both servers…
4) Results stays the same time after time…
5) Query plan are the same on both server…
6) There is a difference between the two servers has mentioned in my initial post… I hope that these answers are usefull to help find other ideas… Thank you in advance… Roger

---

I find the source of my problem but I did not find the solution to it yet… The problem is caused by the Hyper-Threading technology… I ran the same test on a third computer with a pentium 4, 3.2 GHz with 2 Megs of cache. When I run the same query on the same database with the Hyper-Threading enabled, my query take about 30 minutes to run and the cpu usage is around 2-3%. By disabling this option in the bios, the same query take about 30 seconds to be executed and the cpu usage is above 90% during the query execution… Anyone of you had a similar problem? Any solution for this? Thank you in advance.. Roger

---

I’ve got a very similar problem and I describe it soon, but I have to perform some tests first. Now I only have one quick question — is it possible to disable HTT in Xeon CPUs? — Marek ‘chopeen’ Grzenkowicz, MCP
Poland

---

I have perfomed some detailed tests and the results are really astonishing. Machine 1:
AMD Athlon XP 1600+
Windows XP Professional + SP1
MS SQL Server Personal Edition + SP3 Machine 2:
Intel Pentium 4 2.8GHz with HTT
Windows XP Professional + SP1
MS SQL Server Personal Edition + SP3 Monitored counters:
CPU % CPU Time _Total
Process % CPU Time sqlservr
(their actual names may be slightly different — I translated them from Polish) Test script:
declare @start datetime
declare @i int
declare @x float set @i = 0
set @start = getdate() while (@i < 1000000)
begin
set @x = sin(cos(sin(cos(sin(cos(sin(cos(sin(cos(@i))))))))))
set @x = sin(cos(sin(cos(sin(cos(sin(cos(sin(cos(@i))))))))))
set @x = sin(cos(sin(cos(sin(cos(sin(cos(sin(cos(@i)))))))))) set @i = @i + 1
end print datediff(ms, @start, getdate())
Results: Machine 1:
— CPU % CPU Time _Total shows 100% while the query is being executed
— Process % CPU Time sqlservr is close to 100% (almost all system resources are being used by SQL Server)
— it takes about 30 secs to run the test script
—http://www.wizny.ps.pl/HTT/machine1.png Machine 2 with HTT enabled:
— both counters are close to 0% all the time through
— it takes about 159 secs to run the test script
—http://www.wizny.ps.pl/HTT/machine2_HTT_on.png Machine 2 with HTT disabled:
— both counters are close to 100% all the time through
— it takes about 29 secs to run the test script
—http://www.wizny.ps.pl/HTT/machine2_HTT_off.png Conclusion:
Even though Windows XP + SP1 and MS SQL Server 2000 + SP3 constitute an environment that supposedly can use HyperThreading Technology, these test show that not only is there no performance gain, but even a degradation of system performance can be observed. — Marek ‘chopeen’ Grzenkowicz, MCP
Poland

---

You’re using Windows XP though. That’s crazy to compare the results of that to Windows 2000 Server or Windows 2003. XP was not ever designed as an efficient multi-processor, server class OS, regardless of what certain people might tell you. We saw the exact opposite results when we switched our Windows 2000 Advanced Server to use HTT. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

Thanks for this remark. I will check this and post my results. But I still think that results of my tests are quite astonishing. — Marek ‘chopeen’ Grzenkowicz, MCP
Poland

---

I’ve made some more testing with my server B (XEON Processors) and when I disable the HTT, unfortunatly, I do not have a gain performance. In fact, the required time to run my query is still 30 minutes. Now, I have to look somewhere else to find the my problem since the HTT is not causing the problem on my server… Is the Standard vs. Enterprise SQL sever may have an impact??? If anyone has some suggestion, please let me know… Roger

---

When you move the database between different servers make sure to update statistics or even better reindex the database or you can get really bad results. To compare performance also clear the sql cache before each run with DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE For performance difference between enterprise and standard edition see:
http://www.sql-server-performance.com/q&a98.asp In your test query there should be no difference between the editions.

---

my recommendation is that on systems with HT enabled, max degree of parallelism be restricted to the number of physical processors, not the number of logical procs

---

Why? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

because of the problems observed in this thread,
parallel execution across all logical processor sometimes results in a small performance gain, but will more likely cause horrible slow downs as observer in the original post

---

I have performed my tests using Windows 2000 Server. Turning HTT on and off does not cause so significant changes as in case of Windows XP. — Marek ‘chopeen’ Grzenkowicz, MCP
Poland

---

Intel says:
http://support.intel.com/support/platform/ht/os.htm — Marek ‘chopeen’ Grzenkowicz, MCP
Poland

---

I think you’re wrong on this Joe. You should limit on individual procedures. There is absolutely nothing in this thread or any other documentation that recommends or indicates you should limit to physical processors. If you can provide the evidence that this is a good idea, I would happily change my opinion. SQL Server running on server class hardware with a server class OS should have absolutely no issues running well on an HTT enabled server without using MAXDOP. If MAXDOP is ever used, it should be for heavy batch processes or badly designed procs until they can be fixed. People limit HTT many times because they have badly designed procedures and code in the first place. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

Why MAXDOP is used and what are its advantages.. what does mean setting MAXDOP = 1 Thanks
Syed Abid

---

Books Online:

–Look up the SELECT statement to see how it’s used. Here is information on parellelism, which is also from BOL:
MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

I ran the test script posted by chopeen and discovered the following. If you are using Query Analyzer, check to make sure you have the "Set statistics time" and "set statistics IO" options turned off under Connection Properties. I get 29 seconds with them turned off, and I stopped the query at 3 minutes with them turned on. I also noticed a high amount of kernel time with them turned on. Kevin

---

Anybody get any answer to help out Roditek? I would love to know the answer to this too, because i am finding some small issues with parallelism where both processors aren’t fully utilised by certain queries. Haven’t investigated anything about HTT yet (there’s loads more important problems to fix first ;() Dave Hilditch

---

What OS/SQL Server version and SP’s are you on? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

]]>