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.

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

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

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.

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

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

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?

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.

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.

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

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?

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

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?

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.

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

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.

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

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.

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?

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

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

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.