SQL Server Performance

HTT vs. Performance Monitor

Discussion in 'Performance Tuning for Hardware Configurations' started by chopeen, Aug 19, 2004.

  1. chopeen Member

    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
  2. Luis Martin Moderator

    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.

  3. derrickleggett New Member

    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
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  4. satya Moderator

    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.
  5. chopeen Member

    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
  6. chopeen Member

    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
  7. chopeen Member

    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
  8. chopeen Member

  9. joechang New Member

    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.
  10. derrickleggett New Member

    Joe, I'm not trying to be rude, but do you have ANY documentation for all the stuff you keep saying about HTT?

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  11. chopeen Member

    quote:Originally posted by joechang

    with HT enabled, anything over 45% total CPU may indicate the system is essentially saturated.

    I think that this is what I am observing, but I agree with Derrick -- can you prove this?

    --

    Marek 'chopeen' Grzenkowicz, MCP
    Poland
  12. Luis Martin Moderator

    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.

Share This Page