Performance | 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