CPU Performance | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

CPU Performance

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

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
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
Few times EM will be flaky so for such admin tasks better to rely on Query Analyzer. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

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
SP_CONFIGURE from QA. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

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
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.
Now comes to the monitoring using PROFILER and see any differences. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

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
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
May submit the trace to INDEX TUNING WIZARD for any index recommendation. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

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
Take help from PERFMON and tips from this website [8D]. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

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?
As Processor: counters looks fine also capture Memory: Pages/sec, Memory: Available MBytes, PhysicalDisk: % Disk Time, PhysicalDisk: Avg. Disk Queue Length etc. KBAhttp://support.microsoft.com/defaul…port/kb/articles/Q243/5/89.ASP&NoWebContent=1 = troubleshoot slow running queries. http://support.microsoft.com/defaul…port/kb/articles/q243/5/88.asp&NoWebContent=1 = troubleshoot performance of adhoc queries. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

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

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.
Are using any query & index hints? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

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.
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
>> 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?
Can you post the query sample? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

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
Do you have index on Supplier_Search and how about primary key set/? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

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

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

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

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

there should be no more than a 20% performance between a 2×933 P3 and a 2×1.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
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.
The counters from Physical Disk : % Disk TIme, Avg. Disk Queue Length are most useful to assist.
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com

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
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?
How about the mdac versions on your machine and on slower machine? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

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
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
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
Download ComCheck tool from herehttp://support.microsoft.com/defaul…port/kb/articles/Q307/2/55.ASP&NoWebContent=1 and follow as described. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

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