OT: Question about Server Performance in SQL

[PieDerro]

Hey TeAm,

I have a quick question about how to best get SQL Server running nice and fast on my existing crapola "not-really-a-server" server! Here are my current specs:

P4 2.8GHz Prescott
2 x Seagate NCQ 120GB Drives in RAID 1
Tyan S5120 i915 mobo with SATA RAID 0,1,0+1
1GB RAM
A bunch of other not-so-important parts.

The server is running Win2K3 and running SQL Server 2000. Currently, setting up a database with about 20,000 records (they are product records for an online comp shop!!) and this will be serving a website.

Could you guys recommend what, if anything, i need to upgrade in the hardware? I pretty much know i should go for more memory, SCSI RAID for the SQL Database and logs, but what can I do with my limited, meagre system (and budget)?

Thanks!
Pie

 

[soni]

Make sure the database is indexed properly.

With such a small database, its almost like you could let SQL Server cache the entire database in memory.

 

[OhioDude]

With such a small database Andrew, the machine you have will perform quite adequately. As Soni said, make sure you index your tables properly.

SQL Server loves memory and will use whatever you throw at it. Keep in mind that the Standard Edition of SQL Server will only address 2GB. If you're running the Enterprise edition, it will address 64GB.

 

[Ross Whitehead]

What everybody else said.

20,000 is tiny as far as databases go. If you performed a default install of MS SQL Server you should be good. If it was not a default install ensure that memory usage is dynamic, and that autocreate and autoupdate stats is turned on on the DB.

A clustered index is recommended and depending upon the activity on the DB you should consider nightly or weekly index rebuilds.... and backups of course!

 

[Unforgiven]

the only thing i would recommend is more ram. as said before SQL Server will gobble up any ram it can get its hands on! there is a fix for the 2 GB limit in SQL Server 2000 that can allow you to use up to 4 GB: here

 

[PieDerro]

Thanks for the tips guys. I knew the database was small, but it just seemed like there was an aweful delay on the retrieval and reporting side of things.

Looks like I may have to re-index and re-tune. Thanks!!

No doubt, I'll probably upgrade the memory though - given that for now, that same box is running Exchange 2003, Windows 2003 and is acting as a Domain Controller (as well as DNS DHCP blah blah blah)!

PD

 

[Ross Whitehead]

Originally posted by: Unforgiven
the only thing i would recommend is more ram. as said before SQL Server will gobble up any ram it can get its hands on! there is a fix for the 2 GB limit in SQL Server 2000 that can allow you to use up to 4 GB: here

For SQL to use more than 2GB, you would have to upgrade from Standard edition to Enterprise edition.

 

[Ross Whitehead]

Originally posted by: PieDerro
Thanks for the tips guys. I knew the database was small, but it just seemed like there was an aweful delay on the retrieval and reporting side of things.

Looks like I may have to re-index and re-tune. Thanks!!

No doubt, I'll probably upgrade the memory though - given that for now, that same box is running Exchange 2003, Windows 2003 and is acting as a Domain Controller (as well as DNS DHCP blah blah blah)!

PD

Wow... that is a boat load of stuff on the db server. With that many apps and services you will need plenty more RAM. Your delays should be approaching zero on repeat queries. If not look at the cache, disk queue, and CPU metrics in Performance Analyzer. Is it also safe to assume that everything is installed on the same mirror, i.e.: OS, pagefile, Exchange data store, database files? This is not ideal for a production box.

 

[PieDerro]

Yep that's right Ross. Everything's stored on the same RAID 0 SATA mirror - definitely not ideal.

How would the following upgrades serve me:

1. Upgrade memory to 2GB (if not 3GB - I'm running SQL Server Standard, but even though SQL can only address 2GB, I should get benefit from going to 3GB? Because of everything else running on that machine?)
2. Add a RAID 5 SCSI Array for the Database, with, say 3 18GB Seagate Cheetah 15K drives.

PD

 

[CyGoR]

My guess is that the delay has more to do with the querys then with the server
Ofcourse, more RAM would make a difference, but as I found out lately an optimized query can make a huge difference..
I was working with one table with 800.000 record (23 columns) and all standard select querys worked very fast, on an old dual PIII 1,0Ghz system but as soon as the querys got bigger, it sometimes took hours!
Changing both the table layout and the query gave me a speed boost of over 60%...

Your will be capable of running a SQL server, but when it's also used intensively with other services, it might be a little tight.. Good luck!

 

[OhioDude]

Since that is a DC and an Exchange server, put as much RAM on that thing as you can. If at all possible, I would try to migrate Exchange and the authentication responsibilities to another system. As a general rule of thumb, when you're running a database server, you don't want that system doing anything else besides serving data.

A SCSI subsystem will definitely add performance across the board, too.

 

[Ross Whitehead]

Originally posted by: PieDerro
Yep that's right Ross. Everything's stored on the same RAID 0 SATA mirror - definitely not ideal.

How would the following upgrades serve me:

1. Upgrade memory to 2GB (if not 3GB - I'm running SQL Server Standard, but even though SQL can only address 2GB, I should get benefit from going to 3GB? Because of everything else running on that machine?)
2. Add a RAID 5 SCSI Array for the Database, with, say 3 18GB Seagate Cheetah 15K drives.

PD

1. Considering the load this system has on it, I would go w/ your reccomended 3GB RAM. This should leave pleanty for the OS, Exchange, and SQL Server. In this scenario SQL would not use more than the 2GB but that would still leave 1 GB min. for everything else. In my testing, SQL has proven good at giving back memory "when" it is required by other apps.
2. With enough RAM and your small DB, disks should not be as important. I would upgrade your memory first, and watch my disk queue length when the system is under load. If I was going to consider RAID, I would be careful of RAID 5 if your application is insert/update intensive and/or you do not have a good RAID controller.

Also, as CyGoR eluded to... the design of the tables and queries are often the source of poor performance. In your case, I am confident the server is lacking memory and I would upgrade that and see what happens.

Let us know how it goes!

 

[PieDerro]

OK, we've got an extra 2GB of memory coming along, and the queries and tables will be indexed and optimised some more soon!

This should scream in a few days!

Thanks for all the input guys.

 

[DaFinn]

Jumping in late, but I have a similar situation.

I have a db with about 35,000 records. Running on:

W2KServer +SQL2k
Asus P4G8X-Deluxe (Intel 7205 chipset)
P4 2,53Ghz
1Gb ECC DDR (2x512, dual channel)
40Gb 7200rpm ATA HDD for OS
2x 18Gb 10K U160 SCSI drives in RAID1 on ICP-Vortex Raid controller w. 64Mb cache. (=DB)

This is as cheap setup as they come, and it screams... So your setup should be very nice.
Just like others said, optimization is the key.