MS SQL hardware gurus - question

I

Ivork

Expert Member
Joined
Nov 9, 2005
Messages
1,235
Reaction score
110
Location
Kimberley
I need some advice from the SQL peeps here.

Current situation is a G5 ML350 with 2 SAS drives.
One holds the OS (2003 Small Buss DC with officescan, ISA & SQL)
The other drive has the database file.

It’s grinding away at a snails pace and time to upgrade.

Database file is +/- 40GB and there are about 40 users accessing it.

I’m pretty sure the need for speed arises from the fact it’s on a single drive.

RAID is in order imho so:

1 drive for OS and programs & 2 striped drives for the DB (RAID 0) + a good backup system of course?

Or perhaps I should go for a 5 drive array (say,4+1)? Or would that be an overkill?
Or better, RAID 0 with all 5 drives and make sure my backup works flawlessly?

Suggestions? It will also be a HP G6 server of some kind.

Thanks
(Sorry - I know stuff-all about database systems hardware requirements.)
:o
 
OK, for a DB system.. RAID is a damn good start... though 40 users shouldn't be putting too much of a load on the system at all.

I would be suggesting hiving the DB stuff off to its own server so you can isolate its performance and tune from there. Running DB and ISA and DC on the same box is always going to give you performance problems.

After making NO sense above I'll put a few points down.

I would go RAID 5 at the very least for some type of hotswappable redundancy plus a decent backup solution on top of that.
I would get a fairly decent chunk of memory and try and get your SQL sorted to get as much of the DB into memory as you can.. You'll find your users are only using a small portion of the DB itself so it can cache a HELL of alot of it.
 
Well 40GB is small for a DB. Also 40 users is not a big one.

I would also seriously look at the schema and focus on correct indexes and even views to speed up queries. Throwing better hardware is not your only solution here if you have performance issues.
 
Oi... keep quiet...

He's a sysadmin who CLEARLY wants a new toy :p
 
Biggest problem here is lack of support from the software vendors that i shall keep anonymous.

The support they offer is more desktop, how to install and run (at a lucrative price for every callout), not technical database administration.

I would like to not have the blame for poor performance laid at my door for a kuk server, (the default chirp when it goes slow) hence my Q.
 
Last edited:
Ahh, I think we can guess which software vendor this is..... and then yeah, you can dream on about getting any kind of decent DB tuning out of them...
 
Ivork said:
Biggest problem here is lack of support from the software vendors that i shall keep anonymous.

The support they offer is more desktop, how to install and run (at a lucrative price for every callout), not technical database administration.

I would like to not have the blame for poor performance laid at my door for a kuk server, (the default chirp when it goes slow) hence my Q.
Click to expand...

Hire a decent independent DBA to do a health check and performance tuning on your database before spending money on hardware which might not even help. There are also some software products which might do the trick.

You don't mention how much memory the box has and that makes a HUGE difference to SQL Server.
 
Suggestion, if I may?

Get a good server with RAID5/RAID6 onboard support. Use 6 (or more) drives.

Use the onboard RAID controller's firmware to create more than one virtual drive on the RAID itself. This way you can have a partition for your primary OS, and another partition for your database.

I have done this with a new file server to keep data totally separate as the previous server kept filling up with users temporary files and other nonsense. That way it's the user's problem if their user space or temp partition gets full - all other services will still be able to run normally without interruption.

HTH

Libs
 
6 may be a little overkill. Is it possible to get performance stats? If you can, post average:

Physical Disk queue
Physical Disk Writes /s
Physical Disk reads /s
Processor queue
Memory

Bottom line is that if your disks are not slowing the system down, and you add 10, then your performance will not increase. Rather isolate the problem, and the above counters will assist.

Edit - never use RAID0, unless you have absolutely non-critical services running on a machine....
 
Currently x86 server so only 4GB RAM. SQL uses a good 3GB of that.
I'm still under the impression that drive & access speed is slowing things down (a lot) - but I could be wrong.
 
Conradl said:
6 may be a little overkill. Is it possible to get performance stats? If you can, post average:

Physical Disk queue
Physical Disk Writes /s
Physical Disk reads /s
Processor queue
Memory

Bottom line is that if your disks are not slowing the system down, and you add 10, then your performance will not increase. Rather isolate the problem, and the above counters will assist.

Edit - never use RAID0, unless you have absolutely non-critical services running on a machine....
Click to expand...

6 might be overkill, sure.

At least a minimum of 3 HDD's are needed for a RAID5 configuration. I strongly suggest getting 4 drives, and use the 4th as hot spare.

Remember, if you don't take care of your data, it will be a LOT more expensive trying to recover your data than getting the right platform from the beginning.
 
SQL will use all the RAM allocated to it, even if it does not require it; what does your paging look like? Your system may be running out of RAM, paging, and slowing down your disks. Try limiting SQL to 2GB and see if the paging goes down - assuming that there is excessive paging. What is your Peak commit charge?
 
Last edited:
Typically:

RAID5: Data files
RAID1: Log Files
RAID1: OS

Get more RAM. Switch OS to /3GB /PAE in boot file. Enable AWE in SQL. (if x86). Switch to Windows x64 asap.

Get a DBA to do performance analysis on your SQL box as there are too many counters to watch and cross correlate to mention here, don't just check how much RAM SQL uses, the company I work for specializes in this, PM me if you need help.
 
Conradl said:
6 may be a little overkill. Is it possible to get performance stats? If you can, post average:

Physical Disk queue
Physical Disk Writes /s
Physical Disk reads /s
Processor queue
Memory
Click to expand...

@Conradl I'll see what I can do next time I'm there with a few sysinternals tools if you think it's going to help, but they pretty much sold on the idea of a new server anyway......
 
Okay, I know nothing about SQL hardware requirements, but find this quite interesting. These days, when I think about good hard drive performance, I think SSD. Is there a reason one wouldn't use one or two SSDs to take care of this relatively small (apparently?) database, instead of going for multiple spinners in a RAID5 setup?
 
Ivork said:
@Conradl I'll see what I can do next time I'm there with a few sysinternals tools if you think it's going to help, but they pretty much sold on the idea of a new server anyway......
Click to expand...

I am almost 100% sure that you could fix the problem without resorting to buying new hardware, and with spending very little time troubleshooting. Just check those counters in windows perfmon and check the peak commit charge. If you do need to buy a new server, then either go for one RAID1 volume with everything on it, or two RAID1 volumes, one with data and one with OS.

The performance counters of your old server can be used to determine the number of disks required, and RAM. At R4k for a SAS disk, you can save a around R8k by only using two.... I always encourage people to rather buy what they need based on figures; rather than buy what they need based on assumptions (of course most people just go with assumptions and over-provision to CYA :D)
 
Messugga said:
Okay, I know nothing about SQL hardware requirements, but find this quite interesting. These days, when I think about good hard drive performance, I think SSD. Is there a reason one wouldn't use one or two SSDs to take care of this relatively small (apparently?) database, instead of going for multiple spinners in a RAID5 setup?
Click to expand...

For a small database there would be no reason, in principle, not to go for SSD. The problem is that HP does not ship an ML/DL with SSD, they do have 32/64GB options on the BL series that ship for around R7500/R15000 respectively. Also the performance increase may not be that dramatic.

A SAS disk can do 200 IOPS (IOs per second), and a SSD between 2000 - 9000 IOPS (roughly); increasing the number of disks in RAID configurations increases IOPS. The demand placed on the disks by a properly configured and indexed DB can be around 400-600 IOPS (dependant on size of course, some transact dbs require 100000+ IOPS). So if your IO demand was 400 IOPS (which would be a lot for a 40GB DB with 40 users), and the SAS drives in RAID1 could do 400 IOPS, then implementing SSD could offer massive % improvements in terms of theoretical speed increase; but only fractions of a second increase in real terms.
 
Conradl said:
For a small database there would be no reason, in principle, not to go for SSD. The problem is that HP does not ship an ML/DL with SSD, they do have 32/64GB options on the BL series that ship for around R7500/R15000 respectively. Also the performance increase may not be that dramatic.

A SAS disk can do 200 IOPS (IOs per second), and a SSD between 2000 - 9000 IOPS (roughly); increasing the number of disks in RAID configurations increases IOPS. The demand placed on the disks by a properly configured and indexed DB can be around 400-600 IOPS (dependant on size of course, some transact dbs require 100000+ IOPS). So if your IO demand was 400 IOPS (which would be a lot for a 40GB DB with 40 users), and the SAS drives in RAID1 could do 400 IOPS, then implementing SSD could offer massive % improvements in terms of theoretical speed increase; but only fractions of a second increase in real terms.
Click to expand...

Wow, thanks for the non-condescending reply! So search times aren't that big a deal on a well configured DB? The reason I thought SSD may be perfect, is because if you copy a bunch of files concurrently from the same disk, you lose a fair amount of actual throughput, so logically, if a DB works the same, then 40 people asking for data from a bunch of places on the same disk, would kill performance a bit.
Good to know though!
 
The_Librarian said:
6 might be overkill, sure.

At least a minimum of 3 HDD's are needed for a RAID5 configuration. I strongly suggest getting 4 drives, and use the 4th as hot spare.

Remember, if you don't take care of your data, it will be a LOT more expensive trying to recover your data than getting the right platform from the beginning.
Click to expand...

And hard drives are cheap. Worth the investment should something go wrong.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X