A client of ours recently bought a brand new set of disks and wanted to know best practices around tempdb optimizations during the discussion we were trying to identify the latency for read and write performance as well as other benchmarking and stress testing metrics and I figured it would be a good time to write about a tool that has been around for a while now. The tool in question is SQLIOSIM , to be clear this is a stress testing tool and not a perf tool. However there are some important bits of info that can be gathered from the tool anyway. Links for downloading and understanding the results are available at the bottom of the page.
The tool is meant mainly to simulate a SQL workload and identify any potential disk issue that may arise even before installing SQL; even though most times we prefer to identify these issues while performing a load test.
For the purpose of this post I have run the test once on my laptop and once on an AWS server with two SSD drives.
The last link below will explain how to setup and use the utility and it’s very simple and most of the parameters come preconfigured. Just make sure to account for CPU and RAM values in the config file
The below results are from my laptop. Gray background is my local harddisk and yellow is an External USB storage (notice the filepaths), as expected you will see that the Running Average Duration for the local disk is 94 ms which is nowhere near what a server should have but much better than the 265 coming off the USB drive. Also you will notice that the number of time IO throttled ( was reduced to prevent bottlenecks) is higher for the USB drive at 3321 for fewer number of reads and writes (9562 vs 11470 for the disk) which means the utility had to cut down on the number of request being made to the USB drive. Notice there are fewer concurrent IO blocks on the harddisk at2 as opposed to 7 for the USB.
PLEASE REMEMBER THE NUMBERS MENTIONED IN THE SQLIOSIM REPORTS ARE NOT ACTUAL DISK LATENCY, but it is still a good indicator for any obvious performance issue. I normally run perfmon in the background to validate these numbers and also get a more accurate disk latency number e.g. the actual disk latency for reads and writes is 45 /57 ms for harddisk and 79/224 for USB drive respectively
Below are the number from SQL IO Sim for a server provisioned on Amazon AWS with 2 CPUs and 4 GB RAM.
Notice the performance isn’t as great as having a dedicated disk since the lower latency is offset by the amount of Throttling and blocks that occur.
In the next post I am going to talk about an even better tool called SQLIO which can be used to benchmark Disk performance more accurately.
Reference
http://sqlblog.com/blogs/kevin_kline/archive/2007/06/28/understanding-sqliosim-output.aspx
https://www.simple-talk.com/sql/database-administration/the-sql-server-sqliosim-utility/
Please Consider Subscribing
