SQL Server Performance

CPU Performance

Discussion in 'Performance Tuning for Hardware Configurations' started by David Woodward, Nov 6, 2003.

  1. David Woodward New Member

    I have two servers both running SQL Server 2000 and both with an identical database loaded.

    Both servers have 2.5 Gb memory, one with twin 1.4 Ghz P4 processors and one with twin 933Mhz P3 processors.

    The same query run against each server gives results back in 10 secs on the slower machine and sub second on the faster.

    Can this huge difference in response be explained by the different processors, or should I keep looking for configuration etc?

    Note that on the slower machine, the extra processor was loaded after SQL Server had been installed, but the properties of both machines show that all processors are enabled.
  2. satya Moderator

    Any change of configuration between 2 servers, such as max degree of parallelism Option, memory settings etc.

    May monitor with PROFILER while running the query and also make a note of execution plan for assessment.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. bradmcgehee New Member

    As satya has suggested, take a look at the excution plan of each to see if there is a difference. Also, ensure that both of them have updated statistics, and that both have the same OS and SQL Server service packs.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  4. David Woodward New Member

    Thanks folks,

    well, the execution plan for the database on both servers is identical, and the auto update stats box is checked on both.

    I'll need to look at OS config differences between the boxes, but its probably worth noting that the whole Enterprise manager experience is a lot more sluggish on the slower box. That seems to point to the box itself being the problem rather than the database.

    I'll also check the OS and SQL Server service pack levels on the two boxes.

    Cheers

    Dave
  5. satya Moderator

    Few times EM will be flaky so for such admin tasks better to rely on Query Analyzer.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  6. David Woodward New Member

    Yep, Because I tend to run QA from within EM, I think of them being part of the same app. Obviously not the case.

    The whole QA experience is also sluggish in comparison.

    Excuse the ignorance, but how do you check the max degree of parallelism option?

    Everything looks the same on the two systems apart from the processors. In fact the fast machine has 1 x 1.4 Ghz and 1 x 933 Mhz processors in it. Both P4. As I say the slower machine has 2 x 933 P3.

    Cheers
  7. satya Moderator

    SP_CONFIGURE from QA.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  8. Luis Martin Moderator

    How about RAID configuration in both servers?



    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  9. David Woodward New Member

    OK. Both processors are enabled on both machines. All available processors will be used when the cost estimate rises above 5. I guess that these are standard settings, as we wouldn't have messed with them normally.
  10. satya Moderator

    Now comes to the monitoring using PROFILER and see any differences.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  11. joechang New Member

    P4 does not support dual processors, i assume you meant Xeon 1.4GHz

    in which case, i suggest you check if a parallel execution plan is involved,
    if so, try OPTION (MAXDOP 1)
    if the 2xXeon 1.4GHz has hyper-threading enabled, run the test with HT disabled.
    also, which system ran the query in 10sec, and <1sec
  12. David Woodward New Member

    Thanks to all for the help so far.

    OK, well i've run the query on profiler, although I'm not too sure what to read from the traces. The two traces look identical other than for the duration of the query: 0 in one case and 6263 in the other. If this is in milliseconds, then it would suggest that the query is taking 6.2 seconds in the slow case and nothing in the fast one, which looks right. Although zero is a very small number!

    Is there anything else I shoiuld try to get from the traces?

    Cheers

    Dave
  13. satya Moderator

    May submit the trace to INDEX TUNING WIZARD for any index recommendation.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  14. David Woodward New Member

    Thanks, done that already, no recommendations.

    If the difference in performance really could be down to the processors, then I'll need to obtain a different server.

    I still need to look into the RAID issue that someone raised earlier.

    Are there any standard performance tools that you can recommend? Something that would highlight the differences in HD or processor performance?

    Cheers

    Dave
  15. satya Moderator

    Take help from PERFMON and tips from this website [8D].

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  16. David Woodward New Member

    OK Thanks. I am now running Performance monitor on the server.

    TOO MANY OPTIONS!!!

    The total CPU runs almost flat when i run my query, never getting above 3%. So i guess that adding CPU's isn't going to be the answer. i am struggling to choose a counter that illustrates the fact that the box is under any sort of stress, although this may well be down to my lack of understanding.

    If I run a query that pulls back all the rows from a table with 100,000 rows, it takes well over a minute to get the data into QA. Is there a counter on Perf mon that illustrates where the bottle neck might be?

    Again the same, non-cpu intensive query runs very quickly on my other server.

    lastly, is there a standard set of perf mon counter that you would suggest?
  17. satya Moderator

  18. David Woodward New Member

    I found the article on this site on how to start monitoring of disk queue length, memory etc, but absolutley everything looks totally flat. Nothing that i do via QA seems to stress the machine, and yet the query is still slower.

    I'll have a look at the Microsoft links that you have provided.
  19. satya Moderator

    Yes track down the slow performance on queries with above KBAs and keep in mind system resources are also play vital role to improve the performance.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  20. David Woodward New Member

    OK, here's where i am now:

    The indexing of both databases is the same and the profiler output of each is the same.

    the performance monitor of the slow system shows nothing unusual and the system is completely unstressed. No processors or memory issues, and no issues with disk storage that i can see. All lines on the Perf monitor are essentially flat during the slow running query.

    i have run through the KBA stuff and can see nothing wrong. It all seems to come down to the fact that the duration of the query on one server is simply much longer than that on the other server.

    Yet the query runs much slower on one server than the other.

    I really don't know what to do next.
  21. satya Moderator

    Are using any query & index hints?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  22. David Woodward New Member

    Not intentionally. I don't know how to find out where they are set. I'm looking in the help now to find out, but if you can point me to it, that would be great.
  23. Luis Martin Moderator

    Both server has same controlers ,channels, RAID configuration and disks?.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  24. David Woodward New Member

    >> Both server has same controlers ,channels, RAID configuration and disks?.

    I don't know. Wouldn't an issue with this show up on the performance monitor? If there is nothing on the perf mon, what difference does it make?
  25. satya Moderator

    Can you post the query sample?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  26. David Woodward New Member

    of course. Its very simple.....


    SELECT
    INV.RECORD_ID, INV.INVOICE_NO, INV.SUPPLIER_NAME,
    INV.INVOICE_AMOUNT, INV.INVOICE_DATE, INV.STATUS,
    INV.PURCHASE_ORDER_NO, INV.DATE_SCANNED, INV.SENSITIVE,
    INV.INVOICE_CURRENCY, INV.PAYMENT_DUE_DATE,
    INV.SUPPLIER_SEARCH,INV.VENDOR_NUMBER,INV.CURRENT_AUTH_PID
    FROM Chequers..INVOICE INV
    WHERE Supplier_Search = 'IBM'
    ORDER BY INV.SUPPLIER_SEARCH,INV.INVOICE_DATE
  27. satya Moderator

    Do you have index on Supplier_Search and how about primary key set/?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  28. Luis Martin Moderator

    If you have disks Ultra 3 15000RPM in one server and with 2 channels, etc. And in other server Ultra 2 7500 RPM in 1 channel, same CPU, you will note the differents.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  29. David Woodward New Member

    Satya - The indexing on both servers is identical. No specific index on Supplier_search, and no primary key set in either case. i have allowed the index tuning wizard to dictate indexes, and it does not think that one is required.

    Luis - Yes, I agree. But why would you put in 15000 rpm disks unless there was a requirement? How do you tell if there is a requirement? Perf Mon? If there is nothing unusual on Perf monitor, why would the type of disk make a difference? Is the difference in disk speed invisible to the performance monitor counters?
  30. Luis Martin Moderator

    No what I ment is may be, just may be, the performance difference between 2 servers is no because processor only. Perhaps server with CPU slower has best input/output than server with CPU faster, and for profiler or monitor everthing is OK.



    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  31. David Woodward New Member

    Wouldn't the i/o bottle neck appear on perf mon?

    I don't really want to go too far with respect to a single query. i am just using this query as an example. The performance of the slow server is poor for ALL queries that my application uses.

    It definitely looks like a hardware issue, or a config issue rather than a query design issue. I want to have a good look at the disk configuration of the 2 servers.

    Its just very frustrating at the minute. Thanks for all the help so far.
  32. Luis Martin Moderator

    Please don't frustate, there is a razon for almost all. Keep feedback on this mather, and wait for Satya (The Wizard) I'm sure he has some else to said.

    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  33. joechang New Member

    i don't think you have stated explicitly which system is running the query faster, the 933MHz PIII, or the 1.4GHz

    also, is hyper-threading enabled on the 1.4GHz
    does the query plan show a parallel execution plan

    this is the only thing that could explain the difference in performance, and nothing in the disk config
  34. satya Moderator

    Based on the query above I suggest to have non-clustered index atleast or a primary key set on most queried columns which would help to speed-up query and gain performance.

    As most of the hardware resources seems to be fit enough, its ideal to concentrate on the design on database and query.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  35. Luis Martin Moderator

    Regarding last two post. I'm wrong.
    Same query in differents server, must run faster because one have more CPU. And there is no question about input/output?.
    I'm no traing to be ironic here. Just I take the opportunity to clarify others and my self.



    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  36. satya Moderator

    Luis,

    Your opinion counts and the response must be acknowledged.
    I don't see any kind of confusion from your post.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  37. Twan New Member

    Hi ya,

    Is there any reason why you can't reformat the slow machine and rebuild it from scratch? If you have a scripted type build then this might be an option...?

    based on what you've said, you have a problem where the same query runs on two servers. The servers are the same in every respect except that the slower one has faster CPUs. There is no stress on any component from what perfmon tells you, there is no difference in indexing either.

    I'd suggest scrap and rebuild the box as it sounds like something's gone funny, perhaps as a result of adding the CPU (the OS will need a different HAL, and on some machines this is not automatically upgraded)

    Cheers
    Twan
  38. David Woodward New Member

    Hi everyone.

    Sorry I have confused you all.

    The machine with slower CPUs runs the example query in 5-6 seconds. the one with faster CPUs runs the example query in less than 1 second.

    My question is: can this difference be just be down to the CPUs? Even when the monitor does not show that the CPU goes to 100% on the slower machine?

    If there is a bottleneck somewhere in the hardware setup, then should I be able to see it on perf mon somewhere?


    Thanks for everyone's help so far.
  39. satya Moderator

    In all aspects if the h/w and sql server setup is similar then we can assess the situation, if not any change in h/w or even SQL setup that counts.

    And also it will be down to MDAC version applied and service packs.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  40. joechang New Member

    there should be no more than a 20% performance between a 2x933 P3 and a 2x1.4GHz Xeon, of course, it depends on which Xeon you have (or is it a 1.4GHz P3?)

    the difference of 5-6X is more likely in SW
  41. David Woodward New Member

    Thanks, that is what I would have thought. I am certain that the delay is not down to the CPU because the perf mon for the slower machine never gets above 5% CPU used.

    My infrastructure colleague has just told me that the disks for the slower box are in a RAID array, while the faster machine has native disks. i am sure that this is where the difference is, although I would have liked to see something on the perf mon to suggest the difference. Any ideas for a counter that would show this?

    In the meantime, my colleague is going to fiddle with the HD arrangement to see if I can get an improvement that way.

  42. satya Moderator

    The counters from Physical Disk : % Disk TIme, Avg. Disk Queue Length are most useful to assist.


    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  43. Luis Martin Moderator

    Also Disk Write Bytes and Disk Read Bytes will help in order to compare both disk servers.



    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  44. David Woodward New Member

    OK, an interesting piece of information:

    Example query: Select * from invoice (invoice table has 74774 rows)

    If I bring up a terminal services session to the QUICKER box, then run the query in QA, it returns 74774 rows in 6 seconds.

    If I bring up a terminal services session to the SLOWER box, then run the query in QA, it returns 74774 rows in 6 seconds. (the same)

    If I bring up QA on MY machine and run a query against the QUICKER box it returns 74774 rows in 6 seconds.

    If I bring up QA on MY machine and run a query against the SLOWER box it returns 74774 rows in 11 minutes! (not the same)

    Any ideas?

  45. satya Moderator

    How about the mdac versions on your machine and on slower machine?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  46. joechang New Member

    i would say the network connection to the slower machine is in half-duplex,
    if you have Fast Ethernet and duplex capability on all switches, check the network status to see if it is in full-duplex or half
  47. Luis Martin Moderator

    Both are SQL dedicated?


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  48. David Woodward New Member

    Thanks guys,

    I don't know how to check the mdac versions on the two machines so I could do with some help there.

    The machines are both dedicated SQL servers.

    I'll ask one of the infrastructure guys to check the network duplex/half duplex thing to see if there is a setup problem.

    Cheers
  49. satya Moderator

  50. David Woodward New Member

    OK folks, I have the answer!

    The router was being previously used for a box that could only handle 10 mb transmission rate, and when the prt was reused for my box, it was never reset.

    Setting the port to 100mb has solved the problem!

    So I guess that Joe gets the prize.

    Many many thanks for all the help on this problem. this is a great site, and I have pointed it out to many colleagues over the past few days.

    There is no dobt that i will be back here for more help over the next few months. Until then, bye and thanks again.

Share This Page