Microsoft SQLIO: Disk performance test and benchmark tool

Sarath Pillai's picture
SQLIO disk performance benchmark

Database is a critical and on demand resource in any IT infrastructure. Database administrators and developers deploy mission critical databases for different applications. However the underlying disk drives where the database is kept, also plays a major role in the overall performance of a database system.

Some databases are heavy in its read operations than write, and some are heavy in write operation than read. Although a well configured RAID disk array fits well for these operations, its advisable to test the performance of a partition/disk drive after its made available to the operating system.

Performance benchmark test results can be very much helpful in taking decisions, regarding the deployment of a database. We have already IO performance benchmarking in Linux.

Read: File System read/write performance test in Linux

In this tutorial we will be discussing one of the best tools available in the market(which is provided by Microsoft itself), to test read/write performance. Its none other than SQLIO. Don't get distracted by its name, it has nothing to do with SQL, Microsoft probably named it that way to indicate that it can be used to test disk performance for using MSSQL.

 

How to download SQLIO?

The official Microsoft website claims that this tool is provided without any kind of support from them.

Don't worry about the support, the tool does pretty well when used properly.

You can download SQLIO from the below link of their official website.

Download SQLIO From Microsoft

 

How to install SQLIO?

Installing SQLIO is simple, as you just need to follow the regular steps, while installing any Microsoft provided .msi file.

Just follow the steps on the screen (all the steps are self explanatory).

The installer install's the tool, in the below location by default. However its alterable during the install. I have shown the Windows CMD directory listing of the installed directory below(C:\Program Files\SQLIO)

Directory of C:\Program Files\SQLIO
03/04/2013  05:26 PM    <DIR>          .
03/04/2013  05:26 PM    <DIR>          ..
01/21/2004  05:12 PM            35,287 EULA.rtf
11/10/2003  10:17 AM                53 param.txt
11/10/2003  10:17 AM            22,614 readme.txt
10/19/2001  04:03 PM           179,252 sqlio.exe
03/04/2013  05:26 PM         8,388,608 testfile.dat
01/21/2004  06:05 PM            90,357 Using SQLIO.rtf
               6 File(s)      8,716,171 bytes
               2 Dir(s)  12,715,286,528 bytes free
C:\Program Files\SQLIO>

 

Some things to keep in mind before going ahead with SQLIO test

Sequential Read/write: As far as reading and writing performance in terms of disk's is concerned, you need to understand what is sequential read & write.

In sequential read, data is read from ordered or adjacent blocks sequentially, in other words blocks are read one after the other. This kind of read or write is very fast, because the controller head does not require fast movement form one block to another(as all are in order or say one after the other). Same applies for Write.

Random Read/Write: The name suggests that blocks from the disk are read which are random. Which means blocks are read from here and there.

This of course will be slow, compared to sequential.

Caching in SAN/iSCSI controllers: Almost all SAN and iSCSI controller's employ caching as a mechanism for faster access of highly used data.

In fact it caches most recently used blocks so that no time is required accessing it again. This caching can prevent us from getting accurate results while doing a performance test. The best method to overcome this problem is to use a very large file for read and write, which is larger than the cache.

 

What is param.txt in SQLIO?

This file is the one which serves as an option to specify where to conduct the test. It also specifies the size of the file etc. Let's see what's inside the default param.txt you got.

The file location is C:\Program Files\SQLIO\param.txt (because in our case its installed in the default path)

c:\testfile.dat 2 0x0 100
#d:\testfile.dat 2 0x0 100

Let's understand the content of the file. The first entry is the location of the test file(SQLIO creates a test file of a specified size to perform test upon.)

C:\testfile.dat : means that the sample file will be named testfile.dat and will be in C drive. Which means we will be testing Performance of C drive.

"2" : indicates the number of threads to perform read/write test.

The third option "0x0" Specifies affinity mask

The fourth option "100" specifies size of the test file in MB. Keep this file

So if i want to test  the performance on "D" drive, with a large test file(as mentioned before using large file's, which are larger than the cache size of the controller will produce more accurate results.), lets use a 5gb testfile. So our param.txt file will look somethig like the below.

d:\testfile.dat 2 0x0 5000
#d:\testfile.dat 2 0x0 100

 

A sample sequential test with SQLIO

Let's first conduct a sequential write test with sqlio first, and see the output.

C:\Program Files\SQLIO>sqlio.exe -kW -s5 -fsequential -o4 -b64 
-Fparam.txt
sqlio v1.5.SG
parameter file used: param.txt
        file c:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads writing for 5 secs to file c:\testfile.dat
        using 64KB sequential IOs
        enabling multiple I/Os per thread with 4 outstanding
size of file c:\testfile.dat needs to be: 104857600 bytes
current file size:      0 bytes
need to expand by:      104857600 bytes
expanding c:\testfile.dat ... done.
using specified size: 100 MB for file: c:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec:  1509.48
MBs/sec:    94.34

Lets understand the options i have used in the above SQLIO command.

 

-k specifies the type of operation to be performed, W Stands for write, and R stands for read.

-S specifies the numer of seconds for which the test will run.

-f specifies the type of read or write operation to be performed on the test file

-o specifies the I/O requests per thread.

-b specifies the block size using which the test will be performed.

-F specifies the parameter file(its param.txt in our case)

From the above output the two most important output to consider is I/O per second and MB/sec.

If you increase the time duration for the test with -s argument, the output will be different.

Let's see some advanced method to detect disk IO performance based on threads and CPU cores.

C:\Program Files\SQLIO>sqlio.exe -dD -BH -kW -fsequential -t2 
-o1 -s40 -b64 testfile.dat
sqlio v1.5.SG
2 threads writing for 40 secs to file D:testfile.dat
        using 64KB sequential IOs
        enabling multiple I/Os per thread with 1 outstanding
        buffering set to use hardware disk cache (but not file cache)
using current size: 12905 MB for file: D:testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec:  1161.05
MBs/sec:    72.56

During the previous test we saw that we created a testfile.dat of 5gb size. In the above shown test we will be using this file for conducting one more sequentioal write test with some more threaded options.

-d specifies the drive to test

-B specifies the buffering to use. "H" denotes hardware buffer to use.

-t specifies the number of threads to use during the test. Here in our case we have used two threads, each doing 1 I/O request at a time(denoted by -o )

To do a complete stress test i will recommend to increase the number of threads till it reaches the number of cpu cores you have. So if you have a 8 core CPU, then test your drive by doubling the number of threads till it reaches 8 threads, and analyse the output.

Put the below commands in a text file for the test, and run it as a batch script.

sqlio.exe -dD -BH -kW -fsequential -t2 -o1 -s40 -b64 testfile.dat
sqlio.exe -dD -BH -kW -fsequential -t4 -o1 -s40 -b64 testfile.dat
sqlio.exe -dD -BH -kW -fsequential -t6 -o1 -s40 -b64 testfile.dat
sqlio.exe -dD -BH -kW -fsequential -t8 -o1 -s40 -b64 testfile.dat

 

Similarly for read tests you can put the below commands in a text file to test the random/sequential read performance.

sqlio.exe -dD -BH -kR -fsequential -t2 -o1 -s40 -b64 testfile.dat
sqlio.exe -dD -BH -kR -fsequential -t4 -o1 -s40 -b64 testfile.dat
sqlio.exe -dD -BH -kR -fsequential -t6 -o1 -s40 -b64 testfile.dat
sqlio.exe -dD -BH -kR -fsequential -t8 -o1 -s40 -b64 testfile.dat

 

Hope this tutorial was helpul in performing a benchmark performance test of your disk subsystem for taking a better decision, about deploying your database.

Rate this article: 
Average: 3.5 (775 votes)

Comments

F** this is F**** Brilliant.
F*** awesome.

Thanks slashroot.

slashmaster's picture

Hi,

Thanks for the comment..In order to keep the site tidy i have modified your comment with *'s in place of letters..laugh

Thanks

Thank you for your article!
It is very helful for me.

Thanks for the article.. Good stuff..!

Add new comment

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.