SQL Server Performance Forum – Threads Archive

HTT vs. Performance Monitor

I am trying to perform an SQL Server performance audit (SQL Server 2000 Standard Edition + Windows 2000 Server). The machine has 2 Intel Xeon CPUs with HTT (HyperThreading) enabled. One of the counters that I am monitoring is a _Total instance of ‘% Processor Time’ counter. For most of the time is shows values like 50-60%. However, I think that both CPUs are fully utilised. Has anyone ever encountered a problem like this?
I suspect that maybe Windows counters do not work properly with HTT CPUs. — Marek ‘chopeen’ Grzenkowicz, MCP
Poland

---

Why do you think both processor are full?
How about % of SQL process?
How about processor queue length?
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Why would you think that? Normally if the counters are off, they don’t show up at all or they show something ridiculous like 2000%. We run SQL Server 2000 MSDE, Standard, and Enterprise on Windows 2000 Server, Windows 2000 Advanced Server, and Windows 2003. We haven’t seen any problems at all with HTT and the counters. What performance problems are you having?
MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

Also collect stats for PROCESS counter using PERFMON, this will tell you what process was running during that time and hiking the Processor % time. As mentioned, are you getting any performance issues? 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 will describe you my situation more precisely soon. Now if you have a machine with HTT CPU, you can make a simple test. Run this script with HTT enabled and disabled (you can turn it off in BIOS). Note down the time it took to run it (given by the script in ms).
———————————
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())
What are the results? — Marek ‘chopeen’ Grzenkowicz, MCP
Poland

---

I am having performance problems and I am not sure what the reason is. Buffer Cache Hit Ratio is constantly higher than 98% and there is about 100MB of free RAM — RAM does not seem to be a bottleneck. (100% – % Idle Time) shows beetween 10-20%
http://support.microsoft.com/default.aspx?scid=kb;en-us;310067&sd=tech); number of reads and writes is usually below 100 (RAID5 — 3x SCSI 15000 rpm disk) — HDDs seem to be fine, too. That’s why I think it may be CPUs — the counters seem to be OK. However, if you tested my script from the previous post on a machine with HTT, you saw that the % CPU Time never reaches 100% (on my computer it was 15-20%). And I think that CPU load should be around 100%, since CPU is for sure trying to execute the script as soon as possible. And what is REALLY strange — it takes 120 s to execute this script when HTT is enabled and 30 s when HTT is disabled! Can anyone explain this to me? — Marek ‘chopeen’ Grzenkowicz, MCP
Poland

---

I checked the counters you asked about. % CPU Time for sqlservr process:
40-50% for most of the time
sometimes it peaks to 200% CPU queue lenght:
0 for most of the time
the highest peak is 48 (but all are peaks are below 5) — Marek ‘chopeen’ Grzenkowicz, MCP
Poland

---

Since I think you are right and there may be nothing wrong with the counters, I move here:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=5262 — Marek ‘chopeen’ Grzenkowicz, MCP
Poland

---

with HT enabled, anything over 45% total CPU may indicate the system is essentially saturated.
without HT, 80% CPU is a heavily loaded system.
with HT, you may or may not achieve higher peak throughput, but the CPU will frequently read 1/2 the reading w/HT disabled at a given user load.

---

Joe, I’m not trying to be rude, but do you have ANY documentation for all the stuff you keep saying about HTT? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

I think that this is what I am observing, but I agree with Derrick — can you prove this? — Marek ‘chopeen’ Grzenkowicz, MCP
Poland

---

This post is only because I would like to read Joe oppinion. I read all articles by Joe and, guys in my oppinion, one post from Joe has more usefull information than 100 mine.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

]]>