SQLIO.exe is a tool provided by Microsoft that can be used to determine the I/O capacity of a given configuration.
Installation:->
Step 1. Configure Param.txt
The first step is to modify the file param.txt to tell SQLIO
where to find its test file which is named testfile.dat.
| Parameter | Description | Values |
| file name | Name of the test file | R:\testfile.dat |
| number of threads | Size of the thread pool. | 8 |
| mask | Affinity mask to bind operations against this file to particular CPU’s. I don’t use this and just use 0x0 for all CPU’s | 0x0 |
| file size | The size in MB. This should be roughly the size of your database and always larger than your cache, unless you want to test just the cache. I usually use 20 GB. | 20480 |
Step 2. Create Testfile
The next step is to run SQLIO once, so that it can create the testfile.dat
sqlio -kW -s5 -fsequential -o4 -b64 -Fparam.txt
| Switch | Description | Example |
| -d | The drive or drives to work on. There should already be a testfile.dat on that drive as specified in the param.txt. There can be multiple drive letters, such as in “-dEFG”. I test only one drive at a time. | -DL |
| -B | Controls buffering. N for no buffering, H for only hardware controller buffering, S for Windows software buffering, or Y for both hardware and software buffering. To match what SQL Serve is doing, use -BH for just hardware buffering. | -BH |
| -k | Operation to perform. W for write, R for read | -kW |
| -f | Random or Sequential I/O. -frandom chooses the block randomly across the file. -fsequential reads or writes sequentially. There are additional choices for testing specific block sizes | -frandom |
| -t | Threads to run simultaneously. | 0 |
| -o | Outstanding requests on each thread. SQL Server threads issue read requests and then wait, so their typical depths is 1. However, the Lazy Writer issues many write requests. So a switch of “-o8” would always keep 8 I/O operations queued on each thread. You’ll have to work on balancing the number of threads and the number of outstanding requests on each thread in order to keep the disk sub system busy. I usually increase this number until the disks become saturated. | 0 |
| -s | Seconds to run. This should be long enough to fill any cache and then build the queue to it’s maximum before processing levels into a steady state. 90 seconds is usually sufficient to get a good picture of what the disks are capable of | 90 |
| -b | Block size of each I/O in kilobytes. SQL Server reads and writes to data files in 64K blocks. | 0 |
Now Testing Time. All the test I have done on Clota-Lab03
Write Test
Random Writes Test (Perform 4 times as below)
sqlio -dR -BH -kW -frandom -t1 -o1 -s90 -b64 testfile.dat
sqlio -dR -BH -kW -frandom -t2 -o1 -s90 -b64 testfile.dat
sqlio -dR -BH -kW -frandom -t4 -o1 -s90 -b64 testfile.dat
sqlio -dR -BH -kW -frandom -t8 -o1 -s90 -b64 testfile.dat
Note: I am getting different results based on t values.
==CPU Config
CPU Cores 2
Sockets 2
Core per Socket 1
sqlio -dR -BH -kW -frandom -t1 -o1 -s90 -b64 testfile.dat
IOs/sec: 2670.30
MBs/sec: 166.89
sqlio -dR -BH -kW -frandom -t2 -o1 -s90 -b64 testfile.dat
IOs/sec: 4154.49
MBs/sec: 259.65
sqlio -dR -BH -kW -frandom -t4 -o1 -s90 -b64 testfile.dat
Test Result 1
IOs/sec: 4125.65
MBs/sec: 257.85
Test Result 2
IOs/sec: 1553.31
MBs/sec: 97.08
Test Result 3
IOs/sec: 1830.55
MBs/sec: 114.40
sqlio -dR -BH -kW -frandom -t8 -o1 -s90 -b64 testfile.dat
Test Result 1
IOs/sec: 1567.55
MBs/sec: 97.97
Test Result 2
IOs/sec: 1185.25
MBs/sec: 74.07
==CPU Config
CPU Cores 2
Sockets 2
Core per Socket 1
sqlio -dR -BH -kW -frandom -t1 -o1 -s90 -b64 testfile.dat
IOs/sec: 2658.63
MBs/sec: 166.16
sqlio -dR -BH -kW -frandom -t2 -o1 -s90 -b64 testfile.dat
IOs/sec: 4373.91
MBs/sec: 273.36
sqlio -dR -BH -kW -frandom -t4 -o1 -s90 -b64 testfile.dat
Test Result 1
IOs/sec: 3711.18
MBs/sec: 231.94
Test Result 2
IOs/sec: 5792.05
MBs/sec: 362.00
Test Result 3
IOs/sec: 3534.93
MBs/sec: 220.93
sqlio -dR -BH -kW -frandom -t8 -o1 -s90 -b64 testfile.dat
Test Result 1
IOs/sec: 2967.46
MBs/sec: 185.46
Test Result 2
IOs/sec: 3558.57
MBs/sec: 222.41
@t1
@t2
@t4
@t8
@b=4 (Block size 4KB)
Note: Here IO pre second are more but throughput per second is less.
Read Test
Random Reads Test
Leave a Reply