Solved

MSQL 7 runs slower on a dual Xeon then on a Pentium 3

Posted on 2004-03-21
9
525 Views
Last Modified: 2006-11-17
Machine info.

2 - gigs of Kingston ram
2 - 2.6 Intel Xeon processors
Mother board Intel Server Board SE7500CW2
Raid (mirroring)

Running SQLserver, 7.00.623,  MS 2000 server, IIS

Problem:
Query's with complex joins and subquerrys are running at the same speed and slower then on a single processor exact same software configuration p3 with 500 megs of ram (the only difference software wise between the P3 and the dual xeon is that the p3 is running MS 2000 Advanced Server and does not use raid).  However the P3 has the exact same config relating to Data, IIS and MSQL all running on the same machine and off the same hard drive.

Another comparison machine that we have is a single P4 1.8 ghz 1 gig of ram using the same raid configuration running MS 2000 server, IIS and Oracle that is running similar query's approximately 100% faster then the dual Xeon (this machine allso has everything on the same drive).

We have run tests on all aspects of the machine it does help a small amount if we set up SQL server to use only one CPU but still performance is slower then the P3 or P4.

Tests that have been run include hardrive tests, load tests, sql workload tests.  The hardrive tests have proven to be the only thing that performs at the level expected.
I have checked the disk queue length and it is under 2.

In addition all database level performance issues have been addressed including rebuilding indexes etc.

Tests have been performed with Hyperthreading enabled and disabled with negligible difference in performance.  

I have not experimented with puting the dbs on a differnt drive becuase the comparison machines are running fine with everything on the same machine and drive.




0
Comment
Question by:memic001
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 34

Accepted Solution

by:
arbert earned 400 total points
ID: 10646529
First, you should really look at putting IIS on its own server....


You're going to find that disabling hyperthreading will probably be faster on Windows2000 that having it enabled.  Windows2000 isn't smart enough to know that these are logical processors and it treats every processor just like it is a physical processor.  I assume you have 2physical processors and 4logicals right?

How do you have the memory configured in this machine?  If you're running IIS and SQL on the same machine, you're going to be struggling with 2gig of memory.  I know you said you ran some tests and you have the disk queue figures, but did you run perfmon and look at context switching, memory use, and paging?

Have you looked at the query plans?  Do they choose paralellism for most of the plans?  You should experiment with using OPTION (MAXDOP 1) on your queries to see what the affect is.  You can also go into the SQL Server properties and change the cost to consider a paralell plan so SQL Server doesn't try and throw paralellism at everything--simple queries are usually much faster without the paralell plan.

I know you said everything is the same, but have you compared the plans or some of your queries on both servers to see what the differences are?

Brett
0
 
LVL 11

Expert Comment

by:Sven
ID: 10647190
Have you tested the server under the same amount of transactions? Was the traffic to your database the same? High access to a table could slowdown a statement which runs fine on testsystems (without high access).
0
 
LVL 13

Assisted Solution

by:danblake
danblake earned 50 total points
ID: 10647436
Are you running all the latest patchs/firmware drivers -- new machines often ship with old drivers.
0
 

Author Comment

by:memic001
ID: 10649115
I would gladly put IIS on another server if I thought that it would help but I have been running this type of instalation "all on one box and hardrive" for years with no problem (actualy I am amazed by the performance of this little P3 desktop).  (we dont get a ton of trafic but the trafic we do get is very db dependent).

I know its not the ultimate speed configuration but I have set up at least 10 other machines with the same config and it is a simple way to entirly isolate/ encapsulate a group of applications.  If I didnt have a p3 thats been in production with the same configuration for 4 years that is out performing my new Xeon equipment I would do that imediately but under the circumstances I would think that I would get some increase in performance using the same config on my new hardware.

The new box has no trafic as it is not ready for production in its curent condition so its not a trafic thing.
I have experemented with paralellism and there is no major effect.  I have disabled hyperthreading with no major effect.
I will check the version level patch and firmware drivers but I dont think this is it because of the comparison machines are at the same level and running fine for our purpose.

Memory wise I have over 4 times the memory on the new box configured the same way as the comparison box to alow SQL to dynamicaly configure and it is never hiting the top end.

When I set up this box I took an export of code and db so I have the exact same querrys on both machines.  I am running all tests directly from SQL querry analizer so ISS is out of the loop on the db tests (if I shut IIS down the db does not noticabley change performace).  I have run the update statisics command on the dbs in question with no change in performance.

Is it possible that the nature of my querys with complex like statments joins and sub querys will simply not run faster no mater how much processor speed and ram I through at it ?  The querrys are not more then 8 nested statments and I would think that the large upgrade in processor speed would run complex querrys faster.  Thats why we bought the new machine.

Should I try reinstalling Sql server, and/or the OS?

The manufacture of the box said I should pull out half the RAM and see if I have bad ram but I am hesitant to open the box because they have allready said that they probaly wont acept it as a return do to the time frame I have had it.


   
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 34

Expert Comment

by:arbert
ID: 10649892
"When I set up this box I took an export of code and db so I have the exact same querrys on both machines. "

Like I said above--look at the query plans on both machines and see what the difference is.  It's amazing the people that have "exact copies" but forget indexes or something simple--we need a starting point.

"The querrys are not more then 8 nested statments and I would think that the large upgrade in processor speed would run complex querrys faster."

Throwing hardware at something isn't always going to make it better--look at the query plans....
0
 
LVL 11

Assisted Solution

by:Sven
Sven earned 50 total points
ID: 10650025
"Is it possible that the nature of my querys with complex like statments joins and sub querys will simply not run faster no mater how much processor speed and ram I through at it ?  The querrys are not more then 8 nested statments and I would think that the large upgrade in processor speed would run complex querrys faster.  Thats why we bought the new machine."

Look at your query plans for missing indexes or missing statistics.
0
 
LVL 13

Expert Comment

by:danblake
ID: 10650650
This still does not answer if the drivers are correct on the newer machine, a incorrect driver would affect performance if we can identify where the problem is.

You may also be comparing a machine which has a fully loaded buffer cache, and one that does not.
This will really affect the results, and you would need to clear both the proc/buffer cache for simultaneous testing.
0
 

Author Comment

by:memic001
ID: 10654741
Hmm interesting... I did finaly check out the query plans and there was a radical differnce between the 2 machines.  The fact that they were differnt was suprising so I ran another export/import this time useing the backup and restore method and I belive I got all of the indexs this time because the querry plans are now almost identical and the box is using the processors allot better.  I have about a 3 to 4 times performace increase.  Still less then what I would expect for the increase in overall processor speed and ram but this is the first break through I have had on this issue.

Thanks allot !  

Now that I have goten some effect i will continue to tune SQL and atempt to get the rest of the performance out of this box.
I will close this ticket out tommrow after I run some more tests.

 
0
 
LVL 34

Expert Comment

by:arbert
ID: 10654809
Good deal....It's amazing how un-identical "identical" installations can be :)

What's the biggest difference that you see in plans now--the use of paralellism?
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now