July 18, 2013 at 7:45 am
I am setting up a new server (Windows Server 2008 R2) to run a SQL Server (2012) and IIS (7.5) based web application which manages about 160GB of data (SQL and flat files). I've got 6 large SAS disks in three RAID-1 sets, and have divided the data load, I think, between them. My goal is to optimize performance by splitting up the IO activity across the disks during the work day.
How can I measure my strategy? I'd like to measure disk physical activity without damaging application performance too much.
I'm looking for pointer to a utility or PowerShell script that will measure total IO by disk during a period of a few hours.
Thank you for any assistance.
July 18, 2013 at 7:57 am
Quite difficult, if you don't have anything to compare your results with. I suppose if users are happy, performance is acceptable. I would recommend monitoring Disk Reads and Writes per sec and Disk secs per Read/Write for each disk. Save the results, and then if you have any problems in future, you can monitor the same thing again to determine whether IO is the problem.
John
July 18, 2013 at 9:24 am
You might also track some of the existing waits with regards to IO. http://www.sqlskills.com/blogs/paul/how-to-examine-io-subsystem-latencies-from-within-sql-server/
July 19, 2013 at 12:26 pm
Thank you for the Paul Randall blog - it will be helpful.
I tested a new SAN by running SQLIO every hour from a VM server for several months, and looked at the IOPS change over time as more systems were moved to it. I'm looking to do something similar, by disk drive, on the new server. I might want to move my TEMPDB, or split the SS12 database between drives if I can show improved performance.
On the other hand, I am reluctant to change a system to solve a problem I don't have. This new HP DL380 is giving me over 110,000 IOPS from SAS drives in RAID-1 configuration, and has plenty of memory and CPU.
July 19, 2013 at 2:04 pm
Alan Spillert (7/18/2013)
I've got 6 large SAS disks in three RAID-1 sets, and have divided the data load, I think, between them. My goal is to optimize performance by splitting up the IO activity across the disks during the work day.
I'm hardly a RAID specialist, but isn't it better to create just 1 big RAID-5(or 6)? If you do that then the data load will be automatically divided evenly.
July 19, 2013 at 3:56 pm
Yes, data will be divided, but IO activity will also hit all the drives. My attempt is to optimize application performance by spreading out the IO activity.
July 22, 2013 at 1:07 pm
Alan Spillert (7/19/2013)
Yes, data will be divided, but IO activity will also hit all the drives. My attempt is to optimize application performance by spreading out the IO activity.
If data is divided then IO activity is also divided, isn't it?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply