SQL Server IO bottleneck

Last Post 05 Jan 2007 01:10 AM by SQLUSA. 8 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
jefflonn
New Member
New Member

--
09 Nov 2006 02:23 PM
We are currently upgrading our hardware for a mission critical 24/7 system. Since it is a 3rd party application and have no control of each client, we are concentrating on hardware. We are going to an HP G5 dual core, dual Xeon processors, with a 4GB Raid controller and we are configuring a RAID 10 drive, along with the 2 duplexed drives for the OS and SQL Server. The raid controller has 4GB memory. It will have 4GB System RAM, it is going to be 2003 Server with 2 licenses of SQL Server 2000 Enterprise Edition. Current version is 2000 Server with SQL 2000 Std.

We have upto 250 hospital monitors (census dependent) collecting data and sending it in every 1/2 second, with up to 20 statistics per monitor (up to 40tps/monitor), which is configurable by the nurse. One of them is a 20 to 60K-image file. The 250 montiors connect to 7 different monitor loaders that then sends the info into SQL Server. What is happening on our current hardware which is a 2-year-old G3 server is it is not keeping up. The application only needs 72 hours of info and all the rest is deleted off. There is also a couple reporting stations throughout the hospitals to give live graphical reports on a patient as requested. We are also pumping each patients info on the minute to our hospital system. What usually causes our problems is deadlocks along with physical disk I/O limitations.

We are currently throwing hardware at it, until the vendor optimizes the system, which does need optimizing, and are giving them suggestions, which all have to go through the FDA. We are looking to get the best bang for our buck in hardware and since this is a mission critical application we have some money to throw at it. We have the database set at Simple, otherwise the transaction file gets massive and we can't do backups because we have no extra capacity. What would be your recommendations to increase our bottlenecks and give us some future growth? 64 bit, more memory, change raid 10, can’t do SQL 2005 yet.

Thanks

Jeff
SQLUSA
New Member
New Member

--
09 Nov 2006 11:35 PM
More memory is also helpful, but your real problem is disk.

Try this:

1. Transaction log on dedicated RAID1 (or RAID10)
2. Data on RAID10
3. Tempdb on dedicated RAID1

Use perfmon to monitor for disk bottlenecks, make appropriate config changes.

Use SQL profiler to monitor reads/writes/duration into a table 24/7 . Analyze it. Since it is third party, you can only make improvent with index maintenance, possibly dropping/adding indexes.


Kalman Toth
http://www.sqlusa.com
prospec
New Member
New Member

--
02 Jan 2007 11:48 AM
Is moving to a SAN an option, if so you may get better performance with the multiply disk as well as the correct RAID setup.


SQLUSA
New Member
New Member

--
04 Jan 2007 12:29 AM
What kind of moneys are involved with SAN?

Kalman Toth DB, DW & BI Architect, SQL Server Training
URL: http://www.sqlusa.com/order2005grandprix
The Best SQL Server 2005 Training in the World
prospec
New Member
New Member

--
04 Jan 2007 01:00 PM
Well depends on what Vendor you go with and what you need but the SANS that I have worked with in the past have been easily over $100,000. Ziotech is a good one the 3D and the older model Magnitude.
Haywood
New Member
New Member

--
04 Jan 2007 01:28 PM
quote:

Originally posted by: prospec
Well depends on what Vendor you go with and what you need but the SANS that I have worked with in the past have been easily over $100,000. Ziotech is a good one the 3D and the older model Magnitude.


Can't disagree more on the Xiotech.... I currently have systems on an existing Xiotech and the disks on that SAN run slower than Ultra-2 speeds. We have two of them and I've never (personally) seen a worse performing piece of disk equipment.

If you're going to go with a SAN, stick with the bigger vendors (HP/EMC/NetAPP etc). You're going to see costs anywhere from 50K (HP MSA1000) to over a million for small to mid-size SAN solutions.

jefflonn
New Member
New Member

--
04 Jan 2007 02:24 PM
We have 2 different EMC SANS, quite expensive, the slower one was comparable to a fast SCSI RAID 5. I am currently using this for a couple of clustered sql server 2000 Ent boxes and no problems. I am also looking at using this for my original post. I am thinking of partitioning the database to use the existing RAID 10 and SAN, because they keep growing this application and the vendor has not made any improvements yet.

Jeff

Haywood
New Member
New Member

--
04 Jan 2007 03:36 PM
In my experiences, where SAN performance goes belly up is usually from lack of proper configuration and server(s) isolation on the SAN. A lot of times, the DBA does not have any say in the SAN architecture and this is a bad thing (TM), IMO. Most of the performance problems I see on SAN systems are related to the spindles being shared horizontally across multiple servers.

A lot of companies get into SANs for the relative ease of management and consolidation of disk across a lot of server sprawl. This is great from an Operations or consolidation perspective, but the exact opposite of what a production DBA desires for his or her databases.

I have a joke that a lot of people don't get, but the DBAs generally do....

I wish I could buy 15K 9GB SCSI drives still, I'd just buy TONs of them. Meaning that I would still rather use smaller DAS storage in some situations. It affords me significantly cheaper storage that performs as well as a properly configured and implimented SAN. I also have (some) more flexibility in my architecture, and the penalty is mostly management/operations of the DAS.

Too many people I run into today have very little concept of well performing disk subsystems for databases. Disk may be cheap for the storage you get, but well performing disk with good storage cpacity is not.

Edit:
SQLUSA
New Member
New Member

--
05 Jan 2007 01:10 AM
Thanks guys for giving ballpark pricing.

Amazing where the PC technology is leading.....


Kalman Toth DB, DW & BI Architect, SQL Server Training
URL: http://www.sqlusa.com/ordermcitpdba/
The Best SQL Server 2005 Training in the World
You are not authorized to post a reply.

Acceptable Use Policy