I've gotten two requests from customers in the last month on this topic so I thought I would put something here on the Everything Microsoft Community blog.  This is a really interesting challenge since there is a lot written on this topic but not much recently.  There are two blog posts that I reference a lot when asked about stress testing disk subsystems.

 

Disk I/O: Microsoft SQL Server on SAN Best Practices from SQL CAT's Mike Ruthruff (& Prem Mehra) (2009)

http://blogs.msdn.com/b/jimmymay/archive/2009/03/01/disk-i-o-microsoft-sql-server-on-san-best-practices-from-sql-cat-s-mike-ruthruff-prem-mehra.aspx

 

Microsoft SQL Server on SAN Best Practices (2007)

https://technet.microsoft.com/library/Cc966412

 

Most of the work that we did at the Microsoft Technology Center in Mt View when I worked there used the SQLIO tool.  Mike Ruthruff is one of the only sources that I've been able to find that explicitly calls out what is noted in the SQLIO documentation - SQLIO is unstable when used with "too many" threads.  There is only anecdotal information about what "too many" means.  As a work around  for that issue many practitioners adopted a strategy of running multiple copies of SQLIO with only a couple of threads per instance. This also addressed the lack of support for doing simultaneous read and write tests with SQLIO.  However, then you get to spend a lot of time aggregating the results from multiple results files.  There are some good PowerShell based results aggregators that have been written and shared with the community but this is clearly a time consuming and error prone process at best.

 

See for example:

https://community.emc.com/people/swaddc/blog/2014/02/03/the-mtc-parse-sqlio-results-to-excel-graphs-with-powershell

 

The big recent development in I/O testing has been the release of DISKSPD to the public as an open source, unsupported tool:

https://gallery.technet.microsoft.com/DiskSpd-a-robust-storage-6cd2f223

 

So far I haven't seen anyone update any of the older "here is how I use SQLIO" information with DISKSPD specifics since it supports simultaneous read/write tests and has better multithreading behavior.  Jose Barreto has a good blog post on using DISKSPD but it is not SQL Server specific

http://blogs.technet.com/b/josebda/archive/2014/10/13/diskspd-powershell-and-storage-performance-measuring-iops-throughput-and-latency-for-both-local-disks-and-smb-file-shares.aspx

 

So for now my best recommendation is to follow the methodology described by Mike Ruthruff in the "Microsoft SQL Server on SAN Best Practices" white paper and follow Jose's documentation on how to use DISKSPD.  Someone in the SQL community will address this need sooner or later.  It is on my to-do list but that is a very long list right now with all the new announcements from EMC World around XtremIO, ScaleIO, and ViPR to name just a few.  I'm also looking to do a test implementation of the Avamar Virtual Edition for Azure https://community.emc.com/videos/19259 with some former colleagues from the MTC.  Fun stuff...

 

Happy testing

Phil Hummel, @GotDisk

San Jose, CA