Poor Performance on Dual Xeon Machine

[alex4groups]

Hi,

I've been creating a db application using MS Access and MSDE. Only two
of us are using the application, and the server and the app both run
great on my laptop (1.6 GHz Pentium M, 2GB RAM, W2KPro). Only problem
is when I take my laptop home, my coworker loses access to the server.

We recently purchased a dedicated server to run the db on at the
office. It's a 2.8 GHz Dual Xeon, 2GB RAM, running XPPro. We also
bought SQL Server, but I installed the Personal Edition becuase we are
not using a server OS. It's my understanding that XP can utilize both
processors, and the Personal Edition can use both processors as well.
(On a side note, why is Enterprise Manager showing that I have 4
processors - why?) In addition, I understand PE has a work-load
governor that cripples performance when more than 5 TSQL commands are
being run simultaneously.

I backed up the db on my laptop and restored it on our new server. But
when I run the exact same queries with the exact same number of rows,
my queries on the new server are take 3x longer(!?). Can someone
please offer a few suggestions for why this is happening? What can I
do to improve performance on the server machine?
Please let me know if I need to supply more information.

Thanks,
Alex

[louis]

>>>We recently purchased a dedicated server to run the db on at the
office. It's a 2.8 GHz Dual Xeon, 2GB RAM, running XPPro

You really should install a Server OS not XPPro. If you're stuck with
XPPro, you should install Developer Edition not PE, which is meant for
laptops.

On a side note, why is Enterprise Manager showing that I have 4 processors - why?

MSSQL 2000 was written before Pentium 4 hyperthreading came out. The 2
chips appears as 4 to the software.
I backed up the db on my laptop and restored it on our new server.

But when I run the exact same queries with the exact same number of
rows, my queries on the new server are take 3x longer(!?).
Try running sp_updatestats.

[alex4groups]

Louis,

We have MSSQL Standard Edition (which includes Personal and MSDE), and
for the time being we have XPPro.

It seems to me that I'm running a very similar configuration on the
server to what I am on my laptop. PE should perform about the same if
not a little better than MSDE; same w/ XPPro over W2kPro.

I tried updating the statistics; that had no effect.

I've also been using the performance monitor a little and haven't found
anything out of the ordinary; the simple select queries I'm using as
benchmarks between the two machines are not accessing the physical disk
on either.

Alex

[louis]

I doubt performance monitor will say much -- as the new server
shouldn't have any hardware bottlenecks. More likely, it has to do
with the way MSSQL is configured/setup on the machine. (And the fact
it's XP professional with MSSQL PE). What does the execution plans say?

[alex4groups]

:(

The execution plan on the laptop and the server are identical. Perhaps
you are right about the OS/MSSQL combinations we're using.

On a positive note, I tracked down a copy of Developer in the office;
I'm going to test XPPro w/ that.

[Leythos]

In article <11**********************@z14g2000cwz.googlegroups .com>,
al*********@gmail.com says...

Hi,

I've been creating a db application using MS Access and MSDE. Only two
of us are using the application, and the server and the app both run
great on my laptop (1.6 GHz Pentium M, 2GB RAM, W2KPro). Only problem
is when I take my laptop home, my coworker loses access to the server.

We recently purchased a dedicated server to run the db on at the
office. It's a 2.8 GHz Dual Xeon, 2GB RAM, running XPPro. We also
bought SQL Server, but I installed the Personal Edition becuase we are
not using a server OS. It's my understanding that XP can utilize both
processors, and the Personal Edition can use both processors as well.
Here's the issue - XP Pro can only use 2CPU's, so it's going to use the
first CPU on each Chip and the second (the HT part) virtual CPU will not
be used - at least if I understand it correctly.

While XP makes good use of HT, it does not make as good a use of it as
does Server 2003 Standard does. In fact, XP is not optimised for a
server running Dual Xeons.

In many cases, having HT enabled on a motherboard, but not having full
support in the OS for it (as is W2000), you will see a decrease in
performance.

Disable HT in your BIOS until you can get Server 2000 Standard. OEM cost
for server 2003 standard with 5CAL should be under $500.
(On a side note, why is Enterprise Manager showing that I have 4
processors - why?) In addition, I understand PE has a work-load
governor that cripples performance when more than 5 TSQL commands are
being run simultaneously.
HT based CPU's always show up as 2 CPU's under Windows 2000 and above -
this is the nature of Xeon and HT type CPU's on Windows. In some cases,
when the OS and App are HT aware, you can see a boost in performance of
as much as 30%, but most people never see it in real life.

I have a 4CPU Xeon and never see any difference in 2000 Server (SP5) and
SQL 2000 between HT enabled or disabled on our 200GB databases with
hundreds of users.
I backed up the db on my laptop and restored it on our new server. But
when I run the exact same queries with the exact same number of rows,
my queries on the new server are take 3x longer(!?). Can someone
please offer a few suggestions for why this is happening? What can I
do to improve performance on the server machine?
Please let me know if I need to supply more information.

There are several things - drop XP Prof, get MSDE 2000 Version, or
better yet, get SQL 2000 Standard and Windows 2003 server standard.

Also, you need to setup your drives properly:

Drive 0 + 1: Mirror (C: 12GB, D:40+GB)
Drive 2,3,4,5,6: RAID-5 (E: big GB)
Drive 7: Single drive - (F: Size as DB backup * 5+)

With the OS on Drive C and the SQL transaction logs on D and the
database data files on E and then online backups nightly to E with tape
backing up E.

You also need to leave 128MB for the OS and as much other RAM for the
SQL Server. Also set your swap file to something like a fixed size of
1GB, set SQL memory to a fixed size of MIN/MAX = TOTAL RAM - 128MB

--
--
sp*********@rrohio.com
(Remove 999 to reply to me)

[Erland Sommarskog]

louis (lo************@gmail.com) writes:

You really should install a Server OS not XPPro. If you're stuck with
XPPro, you should install Developer Edition not PE, which is meant for
laptops.

Wait! That depends on what Alex is using the database for. If he is
developing an application, it's alright. But if he is using an application,
no, that's a license breach. Developer Edition is *not* for running
applications.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Leythos]

In article <Xn*********************@127.0.0.1>, es****@sommarskog.se
says...

louis (lo************@gmail.com) writes:

You really should install a Server OS not XPPro. If you're stuck with
XPPro, you should install Developer Edition not PE, which is meant for
laptops.

Wait! That depends on what Alex is using the database for. If he is
developing an application, it's alright. But if he is using an application,
no, that's a license breach. Developer Edition is *not* for running
applications.

Actually, you might want to check - depending on what you used to design
the application/database with you can redistribute MSDE with your
product.
--
--
sp*********@rrohio.com
(Remove 999 to reply to me)

[Erland Sommarskog]

Leythos (vo**@nowhere.lan) writes:

In article <Xn*********************@127.0.0.1>, es****@sommarskog.se
says...

louis (lo************@gmail.com) writes:

> You really should install a Server OS not XPPro. If you're stuck with
> XPPro, you should install Developer Edition not PE, which is meant for
> laptops.

Wait! That depends on what Alex is using the database for. If he is
developing an application, it's alright. But if he is using an
application, no, that's a license breach. Developer Edition is *not*
for running applications.

Actually, you might want to check - depending on what you used to design
the application/database with you can redistribute MSDE with your
product.

MSDE is another thing. The issue at hand is Developer Edition which costs
50 USD and is exactly the same as Enterprise Edition - except for the
restriction in license.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Leythos]

In article <Xn**********************@127.0.0.1>, es****@sommarskog.se
says...

Leythos (vo**@nowhere.lan) writes:

In article <Xn*********************@127.0.0.1>, es****@sommarskog.se
says...

louis (lo************@gmail.com) writes:
> You really should install a Server OS not XPPro. If you're stuck with
> XPPro, you should install Developer Edition not PE, which is meant for
> laptops.

Wait! That depends on what Alex is using the database for. If he is
developing an application, it's alright. But if he is using an
application, no, that's a license breach. Developer Edition is *not*
for running applications.

Actually, you might want to check - depending on what you used to design
the application/database with you can redistribute MSDE with your
product.

MSDE is another thing. The issue at hand is Developer Edition which costs
50 USD and is exactly the same as Enterprise Edition - except for the
restriction in license.

Sorry, I thought that MSDE was mentioned by the OP in another part of
the thread.

--
--
sp*********@rrohio.com
(Remove 999 to reply to me)

[alex4groups]

Everyone, thanks for the information.

I installed Developer, and lo and behold, it solved my performance
issues (thanks for your persistance Louis). I'm now querying faster on
the server than I was on my laptop. I'm thinking either the workload
governor for PE was kicking in, or PE was having trouble w/ the
processors like Leythos suggested.

This is a very small tool that I'm developing as we go; only two of us
are using it, and we do not have a huge budget. We are not deploying
it or selling it; we are using it for inhouse analysis. In an effort
to save money, we opted for good processors and 2GB RAM, but we have
only 2 SATA 160 GB harddrives and no RAID. It was my error that we
bought the Standard edition expecting to use it with XP Pro (which my
company owns a ton of licenses already). See, if we had bought the
Server OS, I would have had to pare down my hardware configuration.

I will work on getting W2K3 Server. Until then, would anyone like to
comment on how I have my drives set up?

Disk 1: C, OS and Apps; D, MSSQL Backups; E, Sharedrive for our team
Disk 2: Z, 6GB Virtual Mem; F, MSSQL DB; G, OS & Apps, Sharedrive
backups

In addition, we will also be conducting db backups on a network drive
once a week.
Is this a good configuration given that I only have two drives?

[Leythos]

In article <11**********************@c13g2000cwb.googlegroups .com>,
al*********@gmail.com says...

Everyone, thanks for the information.

I installed Developer, and lo and behold, it solved my performance
issues (thanks for your persistance Louis). I'm now querying faster on
the server than I was on my laptop. I'm thinking either the workload
governor for PE was kicking in, or PE was having trouble w/ the
processors like Leythos suggested.
Glad to hear that it's working now.

[snip]
I will work on getting W2K3 Server. Until then, would anyone like to
comment on how I have my drives set up?

Disk 1: C, OS and Apps; D, MSSQL Backups; E, Sharedrive for our team
Disk 2: Z, 6GB Virtual Mem; F, MSSQL DB; G, OS & Apps, Sharedrive
backups

In addition, we will also be conducting db backups on a network drive
once a week.
Is this a good configuration given that I only have two drives?

Not knowing what you have to a motherboard, I would strongly suggest
that you consider at least a RAID controller card and set that up with
the drives in a Mirror. The PromiseTX card, or if the motherboard
supports RAID, either will work fine.

It appears that you use SQL and your apps and a common sharing point for
everyone, a MIRROR would provide redundancy that would help in the case
of a fault.

You should also consider nightly backups - even a tape backup to a cheap
Travan drive would help in the event of a fault.

My guess would be that if you are using 2xSATA on a Dual Xeon, that the
board supports RAID with an onboard controller.
--
--
sp*********@rrohio.com
(Remove 999 to reply to me)

[alex4groups]

Leythos,

I forgot to mention that I am doing full backups once a week,
differential backups daily, and transaction logs hourly (overkill, I
know). The backups are made to the first disk, while the database
resides on the second disk. We are also doing a network backup once a
week.

So given that we are already doing all these backups, and given that
this is not a highly time critical application, I'm not too worried
about implementing a fault tolerant RAID configuration. We'd rather
have the extra disk space for now.
I did ask for your advice, though, and I thank you for it.

Alex

[Erland Sommarskog]

(al*********@gmail.com) writes:

I installed Developer, and lo and behold, it solved my performance
issues (thanks for your persistance Louis). I'm now querying faster on
the server than I was on my laptop. I'm thinking either the workload
governor for PE was kicking in, or PE was having trouble w/ the
processors like Leythos suggested.

This is a very small tool that I'm developing as we go; only two of us
are using it, and we do not have a huge budget. We are not deploying
it or selling it; we are using it for inhouse analysis.

Since I brought up licensing, I guess I should comment. Although I have
to say that I have no idea what applies in this case. It you were
developing it, and then would sell it to people, who would run it on
anything but Developer, it would be OK. But when development and
usage is on the same machine it's more difficult. My gut feeling says
that if you use the information from the analisys for business decisions
you are violating the license terms.

But if you really want to find out to avoid being caught red-handed, I
can only recommend you to contact your local Microsoft representative.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Leythos]

In article <11*********************@f14g2000cwb.googlegroups. com>,
al*********@gmail.com says...

Leythos,

I forgot to mention that I am doing full backups once a week,
differential backups daily, and transaction logs hourly (overkill, I
know). The backups are made to the first disk, while the database
resides on the second disk. We are also doing a network backup once a
week.

So given that we are already doing all these backups, and given that
this is not a highly time critical application, I'm not too worried
about implementing a fault tolerant RAID configuration. We'd rather
have the extra disk space for now.
I did ask for your advice, though, and I thank you for it.

Alex, we're cool - I would have opted for the Dual 250GB SATA drives and
then mirrored them.

What motherboard are you using in the server? ASUS PC-DL Deluxe?
--
--
sp*********@rrohio.com
(Remove 999 to reply to me)

[alex4groups]

Cool. Not sure what make/model the motherboard is - the machine is a
Dell SC1420, and the motherboard is RAID capable; there are RAID
options in the bios and it came w/ raid management software.

Erland, thanks for the licensing tips; I'm looking into getting the
server OS and running Standard Ed.