SQL Server Performance

CPU are not used properly...

Discussion in 'Performance Tuning for Hardware Configurations' started by Roditek, Aug 20, 2004.

  1. Roditek New Member

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

  2. Luis Martin Moderator

    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.

  3. bradmcgehee New Member

    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
  4. Roditek New Member

    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
  5. Roditek New Member

    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
  6. Roditek New Member

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

    quote:Originally posted by Roditek

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

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

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

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

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

    quote:Originally posted by derrickleggett

    We saw the exact opposite results when we switched our Windows 2000 Advanced Server to use HTT.

    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
  11. Roditek New Member

    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
  12. Argyle New Member

    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.
  13. joechang New Member

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

    Why?

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  15. joechang New Member

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

    quote:Originally posted by derrickleggett

    We saw the exact opposite results when we switched our Windows 2000 Advanced Server to use HTT.

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

  18. derrickleggett New Member

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

    When life gives you a lemon, fire the DBA.
  19. abid.syed New Member



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



    Thanks
    Syed Abid
  20. derrickleggett New Member

    Books Online:


    quote:
    MAXDOP number

    Overrides the max degree of parallelism configuration option (of sp_configure) only for the query specifying this option. All semantic rules used with max degree of parallelism configuration option are applicable when using the MAXDOP query hint. For more information, see max degree of parallelism Option.

    --Look up the SELECT statement to see how it's used.

    Here is information on parellelism, which is also from BOL:


    quote:

    max degree of parallelism Option
    Use the max degree of parallelism option to limit the number of processors (a maximum of 32) to use in parallel plan execution. The default value is 0, which uses the actual number of available CPUs. Set max degree of parallelism to 1 to suppress parallel plan generation. Set the value to a number greater than 1 to restrict the maximum number of processors used by a single query execution. If a value greater than the number of available CPUs is specified, the actual number of available CPUs is used.



    Note If the affinity mask option is not set to the default, it may restrict the number of CPUs available to Microsoft® SQL Server™ on a symmetric multiprocessor (SMP) systems.


    Change max degree of parallelism rarely for servers running on an SMP computer. If your computer has only one processor, the max degree of parallelism value is ignored.

    max degree of parallelism is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change max degree of parallelism only when show advanced options is set to 1. The setting takes effect immediately (without a server stop and restart).

    In addition to queries, this option also controls the parallelism of DBCC CHECKTABLE, DBCC CHECKDB, and DBCC CHECKFILEGROUP. Parallel checking can be overridden by using trace flag 2528. For more information, see Trace Flags.


    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  21. khaugen New Member

    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
  22. dhilditch New Member

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

    What OS/SQL Server version and SP's are you on?

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.

Share This Page