Possible IO Problems

Hi guys

We have a 23GB SQL database and we are experiencing some pretty dire performance problems.

I think this may be related to the disk setup.

We have 3 physical SCSI disks on 1 array which make the C and D drive. (RAID 5)

We also have 3 SCSI disks (also RAID 5) which makes the E drive, now this E drive contains all the database files and all the transaction log files.

On a daily basis we are adding approximately 83,000 records.

Database activity is nearly always high as queries are running to generate results and the same server is also serving browser requests via IIS to display the data for users.

Now I have suggested that we split the database files and the transaction log files onto 2 seperate physical disks.

In this server we will also have 4 70GB SCSI disks, these will be split into 2 arrays using RAID 1. Giving me 2 logical drives of 70GB each. I proposed putting the database files on one and the transaction log files on the other.

We maintain our redundancy as both disks are mirrored and we also have offsite backups.

Will we notice improvement by removing the database files and the transaction log files off the same RAID 5 array and onto seperate RAID 1 arrays (what are peoples opinions regarding SQL database files on RAID 5?)

Many thanks for you help guys

HanleyOriginally posted by Hanley
Hi guys

We have a 23GB SQL database and we are experiencing some pretty dire performance problems.

I think this may be related to the disk setup.

We have 3 physical SCSI disks on 1 array which make the C and D drive. (RAID 5)

We also have 3 SCSI disks (also RAID 5) which makes the E drive, now this E drive contains all the database files and all the transaction log files.

On a daily basis we are adding approximately 83,000 records.

Database activity is nearly always high as queries are running to generate results and the same server is also serving browser requests via IIS to display the data for users.

Now I have suggested that we split the database files and the transaction log files onto 2 seperate physical disks.

In this server we will also have 4 70GB SCSI disks, these will be split into 2 arrays using RAID 1. Giving me 2 logical drives of 70GB each. I proposed putting the database files on one and the transaction log files on the other.

We maintain our redundancy as both disks are mirrored and we also have offsite backups.

Will we notice improvement by removing the database files and the transaction log files off the same RAID 5 array and onto seperate RAID 1 arrays (what are peoples opinions regarding SQL database files on RAID 5?)

Many thanks for you help guys

Hanley

Its been a while since I've had to beat on this one. So bear with me while I throw some questions at you.

What is the general setup of the server? CPU(s)? RAM?
How many SCSI controllers do you have?
How much memory do they have?
How fast are the SCSI drives?
Can you do replication of the database to another server for read only IIS?

RAID 5 is typically faster than RAID 1 in the real world. And if your choke point is the controller, throwing all the drives in the world at it isn't going to help.

In addition even splitting the disks for transaction logs and the datafiles is not going to give you much more speed if they are going against the same controller. As a matter of fact it may even slow you down.

If your C/D are on a separate controller from the E, then run your db files to c/d and your logs to e.

Just how I see it....|||on a 3-disk raid-5 there are at least 3 physical io's, vs. raid-1 yields only 2. raid-1 is actually faster, and for a 3-disk raid-5 it provides a better level of reliability than raid-5.|||It also sounds like your OS is on a RAID 5. If the OS is a raid 5, then the swap file is probably there as well, so the system has to calcualte parity for the swap file space every time it writes to the virtual memory. This can hammer you performace really, really badly.
I would recomment installing a separate partition or disk, separate from the RAID arrays, at least any with parity, and point the vitual memory swap file to live there.|||Originally posted by ms_sql_dba
on a 3-disk raid-5 there are at least 3 physical io's, vs. raid-1 yields only 2. raid-1 is actually faster, and for a 3-disk raid-5 it provides a better level of reliability than raid-5.

Yeah, RAID 1 is definately faster, and more space efficient. But it doesn't provide you with any failsafe. If you lose one of the drives all your data is gone. The point of RAID5 is to give you redundancy without too much extra space overhead and access times. If you lose a RAID 5 drive, your data is still in tact.|||Originally posted by ms_sql_dba
on a 3-disk raid-5 there are at least 3 physical io's, vs. raid-1 yields only 2. raid-1 is actually faster, and for a 3-disk raid-5 it provides a better level of reliability than raid-5.

It depends on the SCSI controller and drives being optimized. If so the the three physical I/O's should be running concurrently (write/3 plus parity) vs. RAID 1 being paralell(sp?).

I grant speed diff of RAID 5 on 3 disk is questionable depending on HW, Firmware (FW), and SW vs RAID 1. Running RAID 5 on 5+ disks will generally show the difference.

Another point in RAID 5 favor though depending on OS/FW though is continued expandability. I think the high end SCSI size is about 320GB and you are talking big $ per. In RAID 1 you are limited to that, unless you do RAID 10. but then you might as well look at SAN/NAS. With a good RAID 5 you can add drives to the limit of the controllers.

But if he is trying to run those databases and transactions on a 2x500Mz with 2G RAM a FastSCSI 1 controller and a 10/100NIC, he might as well be trying to pump out of the Pacific using a hand bailing pump and a straw.|||Originally posted by craigmc
Yeah, RAID 1 is definately faster, and more space efficient. But it doesn't provide you with any failsafe. If you lose one of the drives all your data is gone. The point of RAID5 is to give you redundancy without too much extra space overhead and access times. If you lose a RAID 5 drive, your data is still in tact.

RAID 0 = Striping
RAID 1 = Mirroring
RAID 5 = Striping w/ partity
RAID 10 = Striping w or w/o parity and mirroring (generally with parity)|||craig, in raid-1 if I lose one of the drives, the other will have an exact copy of the data up to the point of failure of the first drive.

also, it becomes a defacto standard for small to medium size business that do not utilize san technology, to put the data on raid-5 with 5+ drives spread through 2+ scsi controllers, logs on raid-1, also multiple controllers to allow for semi-parallel io. I also recommend to put tempdb on a separate controller, maybe even 1 or 2 non-raided physical disks. AND i appload your suggestion on the location of the swap file.|||and watch paging (total pages), cpu (total pct) and logical disk (avg disk queue length).

This will give you a good first approximation at whether you problem is not enough memory (too much paging), not enough processor (cpu utilization) or insufficient disk performance.

Microsoft recommends that paging be kept below 20 pages/sec.
I suggest if the CPU(s) are spending a lot of time above 80%, you might need faster or more processors.
But I suspect you're problem is disk performance. The thing that REALLY leaps out at me is you have both the transaction logs and data on the same drive. That's nearly always a bad idea, unless you are just too constrained by money to have it any other way. Probably nothing will speed up your response time more - particularly if you're doing a lot of data changes as opposed to mostly SELECTS - than separating the log from the data files.

No doubt, RAID 1 is faster than RAID 5. RAID 10, if you can afford it, is even better. Sometimes it's hard to convince people of this, but RAID 5 is SLOW to write data. So if you're doing a lot of INSERTS, UPDATES or DELETES, get off of a RAID 5 array, it's probably killing you.

ALL THAT SAID, keep in mind that throwing hardware at a performance problem is the last avenue you should persue. If you have performance issues, first TUNE YOUR SQL. One frequent but inefficient query can cost a lot of machine performance. Next, TUNE YOUR DATABASE. Do you have the proper indexes to make your queries execute quickly? Finally, TUNE THE ENGINE. SQL 2000 does a good job of this by itself, but there may be parms you can change to improve it even further. These three steps, particularly the first two, at least have the potential to give you far greater performance gains than any amount of hardware. Only after you've convinced yourself that the common queries are sensibly formuated and have the proper indexes should you even consider spending money on faster hardware. Better hardware probably won't gain you more than 2x to 10x your current speed, but tuning can literally get you gains of 100x - 10000x.

If necessary, use the Profiler to analyzer incoming queries. Take the frequent ones, paste them into Query Analyzer and take a look at the Query plans and I/O costs. Build (or add columns to) indexes to eliminate as many of the table and index scans as you can and get the engine doing index seeks. Time and time again I've seen a little work here yield eye-poping gains.|||Originally posted by ms_sql_dba
craig, in raid-1 if I lose one of the drives, the other will have an exact copy of the data up to the point of failure of the first drive.


Ahh yes, I got my 0 and 1 confused. Sorry for adding my confusion to the discussion.|||Originally posted by craigmc
Ahh yes, I got my 0 and 1 confused. Sorry for adding my confusion to the discussion.

No problem. You are right about the swap file.

Until we know a little more about the server he's running on we're just shooting in the dark as to disk access being the problem.

And tuning can help, but some of us are really maintaining databases that are for applications delivered by vendors. We had one application delivered by a vendor that the EOD process had climbed north of 6 hours over time as the data increased. After complaining enough they finally delivered the next version which dropped it to 30 minutes with the same amount of data. They didn't change the database at all, they tuned their queries, which we couldn't touch. Have no doubt, some vendors can't program to find the hole in their backside with a data map, specific queries, and the light of open source code.

Paraphrasing Hanley "A 23GB SQL database and adding approximately 83,000 records daily." With probably mass selects for the IIS.

My company had less transactions and sub 10GB DB's which forced us off a dual 550 1G Ram onto a onto a hyperthreaded dual 2.2Xeon with 2GB ram and a connection to a SAN. Giant leaps.|||Guys

Sorry its taken so long to reply, I've been on a course the last 2 days and haven't been in the office all week.

I will check the spec of the servere tomorrow but off the top of my head it is as follows:

4 x 1.5Ghz CPU
4GB physical memory (SQL limited to 2GB as only running Std edition)

Will have to check the details of the RAID controller

Can I just say that CPU resource on the server is never really above 20-30% so I didn't think this is the bottleneck

Will check the specs of hard disk etc tomorrow

Cheers

Hanley|||Originally posted by Hanley
I will check the spec of the servere tomorrow but off the top of my head it is as follows:

4 x 1.5Ghz CPU
4GB physical memory (SQL limited to 2GB as only running Std edition)

Will have to check the details of the RAID controller


That isn't too bad - details on the controller would be good. What's your backbone like. Hubs? Switched? Fiber, 10, 10/100?

Just pointing out other things to look at.