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

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

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

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?

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.

In article <1106840171.084819.266910@z14g2000cwz.googlegroups .com>,
alex4groups@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



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

louis (louisducnguyen@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, esquel@sommarskog.se

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

In article <Xns95EC3BF8DED7Yazorman@127.0.0.1>, esquel@sommarskog.se
says...
> louis (louisducnguyen@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.


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

Leythos (void@nowhere.lan) writes:
> In article <Xns95EC3BF8DED7Yazorman@127.0.0.1>, esquel@sommarskog.se
> says...
>> louis (louisducnguyen@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, esquel@sommarskog.se

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

In article <Xns95EC62C1AC31AYazorman@127.0.0.1>, esquel@sommarskog.se
says...
> Leythos (void@nowhere.lan) writes:
> > In article <Xns95EC3BF8DED7Yazorman@127.0.0.1>, esquel@sommarskog.se
> > says...
> >> louis (louisducnguyen@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.

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