Solved

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

Posted on 2004-03-21
9
528 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

791 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