SQL Server Performance Forum – Threads Archive
RAID Drive Capacities for I/O optimization
I recently upgraded our company’s erp system from the old foxpro version, to thelatest sql version. I’m seeing a couple of performance issues. It doesn’t
matter which workstation config. I’ve got some with 2.8GB processor and 2GB of
RAM, still have the same problem. I can run the client on the server, and
still have to same issue. It’s like the reads are OK, but the writes seem slower than on the foxpro
version. Not a lot slower, but still slower — and you know how users can
be!! My server config is just as the software company recommends. Dell PowerEdge
2850, Dual 3GB processors, 6GB RAM with sql tuned to use 5gb. . RAID controller
is PERC4e/DI, with 2X4 backplane. SQL Server 2000 Enterprise with AWE enabled.
Log files and data files on separate raid arrays on different channels. OS and
Log array is RAID 1 on channel 0, Data array is RAID10 on channel 1. RAID Policy
is set to write-back. My main database is about 10GB. I said all that to ask this question, all of my drives are 140GB 10K rpm, would
the performance increase be significant enough to spend the money if I upgraded
the 4 hard drives on the RAID-10 Data drive to 140GB at 15K rpm?? I’ve got the
budget to do it, but if it’s not a big difference, I won’t bother spending the
money. Also, I’ve been reading a lot and have noticed that most everyone says that sql
tries to read your entire database into memory. Well, I’ve got 3 different
databases all running about 10GB each. Two of these are archives of old data,
that could be moved to another server. I’ve also seen a lot of info about
using smaller capacity drives for raid. The 4 current RAID-10 drives are
140GB, I could downsize to 4 drives at 73GB for this. Do you guys think this
could help with my I/O responses any?? thanks
Randy
Links :
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx
http://www.sql-server-performance.com/hardware_tuning.asp
http://www.sqljunkies.com/Article/7F8518F9-FDAA-4FF3-8FC5-25E8946C8D0C.scuk
for a good start and resolve the performance issues. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Smaller drives with higher RPM are usually going to give better performance. You need to identify the bottlenecks though. What do your disk write and read queue lengths look like for each drive? How are your memory and cache hit ratio counters looking? Have you ran Profiler to find out what is long running, has heavy reads or writes, and utilizes a lot of CPU? Have you ran the Index Tuning Wizard to see how you could improve indexing? You need to identify what is causing your slowtime. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Derrick, after watching the monitor, the thing that jumps out at me is that the disk queue length on the drive holding the database does spike on certain operations that I’m testing. What do you think?
on certain operations or at regular intervals, on intervals, thats the checkpoint process
Not at specific intervals. I can start the operation, and watch the disk queue spike while it’s running.
Can you post the disk read and write queue lengths? I would like to know for each drive letter. Tell me how many physical drives on each drive letter and what’s on each (data, log, tempdb, etc).
MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
]]>