Microsoft SQLIO: Disk performance test and benchmark tool
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.
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.
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.