July | 2014 | The Cloud Technologist

I came across an excellent blog article on disk performance testing:

http://www.brentozar.com/archive/2008/09/finding-your-san-bottlenecks-with-sqlio/

It walked through using SQLIO to test disk performance.

Basically, you just modify the param.txt file like this:

D:\testfile.dat 2 0x0 20480

(Where 20480 is a 20gigabyte file that will get created on the D:\ drive)

Then you can do a quick 10 second run with this syntax:

C:\Program Files (x86)\SQLIO>sqlio.exe -kW -s10 -fsequential -t8 -o8 -b8 -LS -Fparam.txt timeout /T 10

For comparison my C drive is a Corsair Force GS and scored 210mb/s with 27k IOPS and my D drive is a OCZ-Vertex and scored 135mb/s and 17k IOPS

Then you can run a longer 120 second test by creating a batch file containing these entries:

sqlio -kW -t8 -s120 -o8 -frandom -b8 -BH -LS D:\TestFile.dat
sqlio -kR -t8 -s120 -o8 -frandom -b8 -BH -LS D:\TestFile.dat
sqlio -kW -t8 -s120 -o8 -fsequential -b64 -BH -LS D:\TestFile.dat
sqlio -kR -t8 -s120 -o8 -fsequential -b64 -BH -LS D:\TestFile.dat

-kW and -kR: means we’re testing writes or reads
-t8 and -o8: means 8 threads with up to 8 outstanding requests at once. SQLIO isn’t CPU-bound at all, and you can use more threads than you have processors. The more load we throw at storage, the faster it goes – to a point.
-s120: means the test will last 120 seconds
-b8 and -b64: the size of our IO requests in kilobytes. SQL Server does a lot of random stuff in 8KB chunks, and we’re also testing sequential stuff in 64KB chunks.
-frandom and -fsequential: random versus sequential access. Many queries jump around randomly in the database, whereas things like backups, bulk loads, and table scans generally work sequentially.

Downloads

SQLIO from Microsoft http://www.majorgeeks.com/mg/getmirror/sqlio_disk_subsystem_benchmark_tool,1.html

Note: the blog post also recommends an easy GUI tool that is available here:

CrystalDiskMark Portable Edition http://crystalmark.info/download/index-e.html#CrystalDiskMark

CrystalDiskMark generates sequential & random, read & write loads at your storage system in 512KB and 4KB chunks

More advanced tools:

Iometer http://www.iometer.org/

SQLIOSim http://support.microsoft.com/kb/231619

Microsoft just released a tool that can connect to your on-premises environment and estimate the cost of running it in Azure Infrastructure as a Service (IaaS).

You can target an individual physical machine, a Hyper-V or vCenter/ESX host, or a System Center Virtual Machine Manager (VMM) environment.

The tool takes seconds to install and is extremely easy to use. I installed it on my Windows 8.1 domain-joined laptop and pointed it at my lab environment running Hyper-V.

1. Download the beta (here). Note: The beta will expire on 9-1-2014.

2. Follow the installation (next –> next –> done!)

3. Launch the tool and click on Hyper-V

4. Enter the details of your Hyper-V server

Note: I selected ‘Run Once’ to get an immediate cost but I like how the tool allows you to run it over a configurable duration of time so that it can include other factors such as disk and network I/O.

Click Begin Profiling to run the scan.