IO Subsystem Analysis

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