[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Large SQL data read and writes required for our Application - What Hard drives would help most?

Posted on 2009-12-20
6
Medium Priority
?
386 Views
Last Modified: 2012-05-08
We have very intensive application that requires large read and writes of data on Microsoft SQL Server 2000.  Our application currently stalls without any use of memory or CPU and we think it is because of large read and writes of SQL data.

We are need to find a way to speed up DISK IO and it appears like switching from SCSI with speeds of 33.8 - 67 MB per sec to something very fast like Fusion IO Drive DUO that has read of 1,500 MB per sec and write speed of 1,400 MB per sec would make a real speed difference.

We are running on a Dell 2950 Server and are looking for solutions to dramatically speed up the application.

We are use ESXi on the Dell Server currently with SCSI drives, and still have no performance gain switching to Enterprise 2003 Server and Enterprise SQL 2000.

We desperatly need to speed up our customer experience while we take 9-12 months to re-write the application to not be so read/write intensive.

Any help greatly appreciated.
0
Comment
Question by:tmorita
6 Comments
 
LVL 47

Accepted Solution

by:
David earned 400 total points
ID: 26093021
Make sure NTFS chunk/allocation size is 64KB.  This is how SQL server reads/writes, so unless it matches, you are throwing away a lot of I/O bandwidth.

Since you are also using ESXi, and probably RAID, I have no idea how you have them tuned ... but everything needs to be the same, and I doubt very much they are.
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 400 total points
ID: 26093386
Hi,

I had started to write some stuff about setting disks up, and had second thoughts.

I suggest
http://www.sql-server-performance.com/articles/audit/perform_performance_audit_p1.aspx

and see which disk(s) that are being hammered, and if this in fact is the bottle-neck.

Otherwise see
http://sqlblog.com/blogs/linchi_shea/archive/2007/02/01/performance-impact-of-disk-misalignment.aspx

for some stuff on setting disks up.

HTH
  David
0
 
LVL 11

Assisted Solution

by:b_levitt
b_levitt earned 1200 total points
ID: 26097058
How are your disks configured (physical, logical raid).  How are the data files/log files for this apps db distributed on these disks?  How are the data files/log files for the tempdb configured?  How much memory is in the box?  How much is sql server using?  How many processors/cores?  Have you used profiler to determine the biggest offenders in terms of DML operations?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 11

Assisted Solution

by:b_levitt
b_levitt earned 1200 total points
ID: 26097419
I missed the ESXi comment.  In addition to the above questions:
Is this sql server virtualized or did you do that just to test other OSs?
How large is this DB?
0
 

Author Comment

by:tmorita
ID: 26121573
How are your disks configured (physical, logical raid).
Answer: Right now I have individual 140K 15,000RPM drives.  I realize now I should RAID 5 or even RAID 10 to increase speed.

 How are the data files/log files for this apps db distributed on these disks?
Answer: Log file and db on same drive. I have been told now to move log file to different drive?
Any other input here?

 How are the data files/log files for the tempdb configured?
Answer: I have never adjusted the files for tempdb - what is this all about ?
What should we change here?

 How much memory is in the box?  
Answer:
Virtual, so we can go up to 8 -16 gigs

How much is sql server using?  
Answer:
We have tried setting it up to use a lot of memory but often it just hovers at low amount.
We are on SQL 2000, so tried the awe settings and did not see it using a lot of memory.

How many processors/cores?  
Answer:
We have 4 on the machine but this is on ESXi machine and I am not sure how we are configured on multi-processors

Have you used profiler to determine the biggest offenders in terms of DML operations?
Answer: What are DML operations ? Do you mean SQL Profiler ?
I am not the actual developer but work with him,  I know we have used SQL profiler before.
Any recomendations on this side of it as well?
0
 
LVL 11

Assisted Solution

by:b_levitt
b_levitt earned 1200 total points
ID: 26125085
How are your disks configured (physical, logical raid).
Answer: Right now I have individual 140K 15,000RPM drives. I realize now I should RAID 5 or even RAID 10 to increase speed.

How many drives?
Multiple arrays of raid 10 or raid 1. Raid 5 is loosing favor these days.

How are the data files/log files for this apps db distributed on these disks?
Answer: Log file and db on same drive. I have been told now to move log file to different drive?
Any other input here?

Log is entirely sequential writes so it gets a speed boost when its on a disk that's busy servicing other read/writes.   Obviously the disk that looses the log has less to work on.

How are the data files/log files for the tempdb configured?
Answer: I have never adjusted the files for tempdb - what is this all about ?
What should we change here?

Microsoft recomends a separate data file for each processor core. However, I'm not sure what a virtual environment does to that rule.

How much memory is in the box?
Answer:
Virtual, so we can go up to 8 -16 gigs

How much is sql server using?
Answer:
We have tried setting it up to use a lot of memory but often it just hovers at low amount.
We are on SQL 2000, so tried the awe settings and did not see it using a lot of memory.

How much specifically is the sql server process using in megabytes?

How many processors/cores?
Answer:
We have 4 on the machine but this is on ESXi machine and I am not sure how we are configured on multi-processors

Have you used profiler to determine the biggest offenders in terms of DML operations?
Answer: What are DML operations ? Do you mean SQL Profiler ?
I am not the actual developer but work with him, I know we have used SQL profiler before.
Any recomendations on this side of it as well?

DML - Data Manipulation Language (inserts,updates, deletes, selects). Yes, sql profiler. If you have long running operations, often all that is needed a new/changed index. A 10 or 100x increase isn't unrealistic with a new index or simply moving the clustered index.

Again, how large is this database (in MB)?
I should also start with a more basic question.  What do you mean by "large read/writes."?
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question