June 20, 2008 at 9:02 am
We have some tests we're doing to stress a system and we can't figure out why the disk isn't giving us a little more performance. It's an older system with 5 U160 drives and an adaptec raid controller. The first drive is just for OS (C:), the next 3 are in a RAID 0 volume (D:), and the final disk is standalone (E:). We're using RAID 0 b/c we're just testing - I know it's not redundant. Windows 2003, SQL 2000, Allocation unit size in NTFS is 64KB on D & E. SQL data is on D: and Logs are on E:.
The throughput when doing just straight file copies etc. is up around 70-80MBps. But when stressing the server via a multithreaded client that goes through stored procedures (lots of reads, most dealing with full text, few inserts), the throughput only gets up to about 15MBps. But % idle time on the disk is 0% in perfmon. So can anyone explain to me why the disk is slammed according to idle time but the throughput on bytes/sec is only around 15MBps?
June 20, 2008 at 9:10 am
Forgot to mention - generally we have 3 or 4 filegroups, all with 2 files each - total size of files is about 90GB, with about 40% of that space being unused.
June 20, 2008 at 11:14 am
It could be several things contributing to the bottleneck, but it is usually a safe bet to start from the beginning.
Go through the Predeployment IO Best Practices document from Microsoft: http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx#EPAAC
I'd pay special attention to the section on Volume Alignment. I've personally seen dramatic performance increases just by configuring the offset properly. Since you're dealing with 3 striped disks, this could be your problem. If you're worried about what to make it, you're generally safe using the Longhorn/Vista default of 1024.
Use the SQLIO tool as discussed in the document before and after you make any changes. This will be a better benchmark as it will remove any application layer stuff from obscuring the results.
Kyle
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply