Hi,
I have a Windows 2003 Server with two local disk drives one (C of 136 G
the other (D 409 GB. The Server (Dell) runs with 8 Intel Xeon 2.8 G Htz and
has 8 G of RAM. I have SQL Server 2000 installed and two databases total
combined size of around 50 G the data files are located on the D drive. The
sqlservr.exe runs at about 1.7 G of RAM. SQL Server (and in fact any
application) that is installed on the server runs incredibly slow. My
question is for databases of 50 GB size would you expect reasonable
performance on a machine of the spec I outlined. Also are there any
configuration tips (Server or SQL Server) that you could pass on that my
improve performance. When I say the server is slow, it's the type of slow
where there generally is a 2 to 5 second delay on any mouse operation.

Some ideas:

1) Do those disks have RAID or are they single disks? If they are single
disks, you should expect poor performance.
2) If you have Enterprise Edition, then use AWE, since SQL is not
currently configure to use the extra RAM. If you don't have EE, turn on the
/3GB switch.
3) Use the Profiler to find out which queries are slow and tune them.
4) Use Perf Mon to look at disk queue lengths and page life expectancy.
You want low queue lengths and high page life expectancy.

Report back what you find.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Joe" <Joe@discussions.microsoft.com> wrote in message
news:CADBA8F3-6838-4912-8F66-76FB2120FC54@microsoft.com...
Hi,
I have a Windows 2003 Server with two local disk drives one (C of 136 G
the other (D 409 GB. The Server (Dell) runs with 8 Intel Xeon 2.8 G Htz
and
has 8 G of RAM. I have SQL Server 2000 installed and two databases total
combined size of around 50 G the data files are located on the D drive. The
sqlservr.exe runs at about 1.7 G of RAM. SQL Server (and in fact any
application) that is installed on the server runs incredibly slow. My
question is for databases of 50 GB size would you expect reasonable
performance on a machine of the spec I outlined. Also are there any
configuration tips (Server or SQL Server) that you could pass on that my

improve performance. When I say the server is slow, it's the type of slow
where there generally is a 2 to 5 second delay on any mouse operation.

Joe wrote:
> Hi,
> I have a Windows 2003 Server with two local disk drives one (C of 136 G
> the other (D 409 GB. The Server (Dell) runs with 8 Intel Xeon 2.8 G Htz and
> has 8 G of RAM. I have SQL Server 2000 installed and two databases total
> combined size of around 50 G the data files are located on the D drive. The
> sqlservr.exe runs at about 1.7 G of RAM. SQL Server (and in fact any
> application) that is installed on the server runs incredibly slow. My
> question is for databases of 50 GB size would you expect reasonable
> performance on a machine of the spec I outlined. Also are there any
> configuration tips (Server or SQL Server) that you could pass on that my
> improve performance. When I say the server is slow, it's the type of slow
> where there generally is a 2 to 5 second delay on any mouse operation.

You have quite a performance issue. First thing to do is to reboot,
apply most recent Windows and SQL patches and/or service packs, reboot
again. Then disable all services that are not critical to the server
operations. Also run a full scan with your antivirus, defragment your
drives and clear the event logs (keep a backup copy of old logs).

Quick question, is this server accessible directly from internet? In
which case you should activate the Windows firewall and enable TCP/IP
filtering, at the very least.

The CPU and RAM can take a big load, however your I/O is probably
killing your performance.

>From the size of your disks I suspect that they are SATA or PATA, not
SCSI. This can be a bottleneck as those disks are slower (7200 RPM vs
15kRPM), and more prone to burn. Also make sure to setup a mirror
(RAID-1) for your O/S and data disks, unless you really want to gamble
with your job security... SATA disks are quite cheap, and RAID-1 is
available with most Poweredge editions.

To improve SQL performance, move your tempdb and transaction logs on
the C: drive, and keep the data on the D: drive.

By the way, do you have a 32-bits or 64-bits processor? If this is a
32-bits you need to configure SQL Server to manage memory with AWE.

Regards,
lucm

Thanks for the information Tom, this is extremely ful.
We have Raid 5 on data disk and Raid 1 on the system disk. SQL Server is the
Standard edition. Windows is enterprise edition but the PAE switch was not
enabled. After reading the article
http://support.microsoft.com/default.aspx/kb/283037 we are now going to try
enabling PAE. Is there any configuration that will need to be done for SQL
Server (Standard) to use AWE?

Thanks again

"Tom Moreau" wrote:

> Some ideas:
>
> 1) Do those disks have RAID or are they single disks? If they are single
> disks, you should expect poor performance.
> 2) If you have Enterprise Edition, then use AWE, since SQL is not
> currently configure to use the extra RAM. If you don't have EE, turn on the
> /3GB switch.
> 3) Use the Profiler to find out which queries are slow and tune them.
> 4) Use Perf Mon to look at disk queue lengths and page life expectancy.
> You want low queue lengths and high page life expectancy.
>
> Report back what you find.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Joe" <Joe@discussions.microsoft.com> wrote in message
> news:CADBA8F3-6838-4912-8F66-76FB2120FC54@microsoft.com...
> Hi,
> I have a Windows 2003 Server with two local disk drives one (C of 136 G
> the other (D 409 GB. The Server (Dell) runs with 8 Intel Xeon 2.8 G Htz
> and
> has 8 G of RAM. I have SQL Server 2000 installed and two databases total
> combined size of around 50 G the data files are located on the D drive. The
> sqlservr.exe runs at about 1.7 G of RAM. SQL Server (and in fact any
> application) that is installed on the server runs incredibly slow. My
> question is for databases of 50 GB size would you expect reasonable
> performance on a machine of the spec I outlined. Also are there any
> configuration tips (Server or SQL Server) that you could pass on that my
>
> improve performance. When I say the server is slow, it's the type of slow
> where there generally is a 2 to 5 second delay on any mouse operation.
>
>

Hi thanks for the reply,
we reboot every weekend, the machine has all of the latest patches and is
not accessible to the internet, only intranet. I don't think we've defragged
for a while so that and clearing the event logs we can do this weekend. We
have Raid 5 on data disk (D and Raid 1 on the system disk (C. The tempdb
is on the C: drive and the data is on the D: drive however the transaction
logs are also on the D: drive so we can try moving them to the C: drive as
you suggest. We have 32-bits processor and SQL Server Standard Edition. We
didn't have the AWE set so we've set that now and will restart SQL Server
once the client has been contacted.
Thanks once again for your . I will keep you posted on progress.

SQL Server 2000 SE will use up to 4GB, so the 8GB you have cannot be used.
I'd either just go with the /3gb switch or upgrade the OS and SQL.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Joe" <Joe@discussions.microsoft.com> wrote in message
news:ABF24F94-10E8-4382-8559-781D60DD1D8E@microsoft.com...
Thanks for the information Tom, this is extremely ful.
We have Raid 5 on data disk and Raid 1 on the system disk. SQL Server is the
Standard edition. Windows is enterprise edition but the PAE switch was not
enabled. After reading the article
http://support.microsoft.com/default.aspx/kb/283037 we are now going to try
enabling PAE. Is there any configuration that will need to be done for SQL
Server (Standard) to use AWE?

Thanks again

"Tom Moreau" wrote:

> Some ideas:
>
> 1) Do those disks have RAID or are they single disks? If they are
> single
> disks, you should expect poor performance.
> 2) If you have Enterprise Edition, then use AWE, since SQL is not
> currently configure to use the extra RAM. If you don't have EE, turn on
> the
> /3GB switch.
> 3) Use the Profiler to find out which queries are slow and tune them.
> 4) Use Perf Mon to look at disk queue lengths and page life expectancy.
> You want low queue lengths and high page life expectancy.
>
> Report back what you find.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Joe" <Joe@discussions.microsoft.com> wrote in message
> news:CADBA8F3-6838-4912-8F66-76FB2120FC54@microsoft.com...
> Hi,
> I have a Windows 2003 Server with two local disk drives one (C of 136 G
> the other (D 409 GB. The Server (Dell) runs with 8 Intel Xeon 2.8 G Htz
> and
> has 8 G of RAM. I have SQL Server 2000 installed and two databases total
> combined size of around 50 G the data files are located on the D drive.
> The
> sqlservr.exe runs at about 1.7 G of RAM. SQL Server (and in fact any
> application) that is installed on the server runs incredibly slow. My
> question is for databases of 50 GB size would you expect reasonable
> performance on a machine of the spec I outlined. Also are there any
> configuration tips (Server or SQL Server) that you could pass on that my
>
> improve performance. When I say the server is slow, it's the type of slow
> where there generally is a 2 to 5 second delay on any mouse operation.
>
>

Joe wrote:
> Hi thanks for the reply,
> we reboot every weekend, the machine has all of the latest patches and is
> not accessible to the internet, only intranet. I don't think we've defragged
> for a while so that and clearing the event logs we can do this weekend. We
> have Raid 5 on data disk (D and Raid 1 on the system disk (C.

Raid-5 is not an optimal scenario for a database, especially for write
operations. If you have an even number of disks for this array, and if
you controller does support it, you should go for Raid-10. If you have
only 3 disks, maybe you could pull one out and setup a Raid-1; you
would actually get a better performance and a much faster rebuild time
in case of a disk failure. Of course it depends on how much space you
need.

Since you have a Poweredge, you should use OpenManage to check on the
disks health. A failing or rebuilding disk can kill the performance on
Windows.


>The tempdb
> is on the C: drive and the data is on the D: drive however the transaction
> logs are also on the D: drive so we can try moving them to the C: drive as
> you suggest.

This suggestion applies only if you can't setup a RAID-10 with 4 disks
or more.

> We have 32-bits processor and SQL Server Standard Edition. We
> didn't have the AWE set so we've set that now and will restart SQL Server
> once the client has been contacted.
> Thanks once again for your . I will keep you posted on progress.

Maybe you could monitor the temperature of the CPU and disks. I don't
recall if there is a sensor monitor in OpenManage, however there is
plenty of third party tools to do it. If your CPU is overheating, but
not to the point of shutting down, then you might notice some
performance issues.

Another point to investigate is the power alimentation. Hard drives are
very sensitive to power-related issues. Make sure your power supply is
strong enough, and if the server is not connected to an inline UPS
(with voltage regulator), you should most definitely get one. Or at
least take some metrics from the power source to make sure there is no
hiccups weak enough not to trigger the breakers but strong enough to
make your drives dizzy.

A few other things. You should disable the real-time antivirus features
(auto-protect), and setup instead a thorough scan at night. If you can
install Diskeeper it would to improve the performance, but make
sure to add SQL Server data folders to the exclude list.

Regards,
lucm

That seems to be unreasonably slow for an Enterprise Production server.

SQL Server 2000 Standard will only use 2 GB of memory, and only 4 of the
CPUs.

(http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp)

Setting /3GB and especially /PAE will NOT have any desired effect. Setting
max server memory to larger values will not have any effect. Standard
Edition will indicate about 1.7 GB memory allocation.

One thing you might consider (other than upgrading to Enterprise Edition so
that you can use some of that extra -unused- memory and CPUs) would be to
install another instance of SQL Server Standard Edition (license required)
and move one of the databases to that second instance. Obviously upgrading
to Enterprise Edition would be the preferred choice -but more expensive for
licensing.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf


"Joe" <Joe@discussions.microsoft.com> wrote in message
news:CADBA8F3-6838-4912-8F66-76FB2120FC54@microsoft.com...
> Hi,
> I have a Windows 2003 Server with two local disk drives one (C of 136 G
> the other (D 409 GB. The Server (Dell) runs with 8 Intel Xeon 2.8 G Htz
> and
> has 8 G of RAM. I have SQL Server 2000 installed and two databases total
> combined size of around 50 G the data files are located on the D drive.
> The
> sqlservr.exe runs at about 1.7 G of RAM. SQL Server (and in fact any
> application) that is installed on the server runs incredibly slow. My
> question is for databases of 50 GB size would you expect reasonable
> performance on a machine of the spec I outlined. Also are there any
> configuration tips (Server or SQL Server) that you could pass on that my
>
> improve performance. When I say the server is slow, it's the type of slow
> where there generally is a 2 to 5 second delay on any mouse operation.