Many companies rely on IO stress-testing tools like SQLIO Disk Subsystem Benchmark Tool (aka SQLIO) to perform an I/O subsystem validation before they deploy SQL Server in production. Over the years, I have seen a variety of SQLIO tests being used and many of them are not really necessary because they do not mimic SQL Server I/O patterns. In this blog I hope to provide guidance on what test parameters to choose for SQLIO and explain why. Also at the bottom, I provide a sample batch file, ready for use in your environment.
Summary of SQL Server I/O Patterns that Determine SQLIO parameters.
Based on these patterns, here is a proposed list of commands to use. Run the tests for at least 5 min or 300 seconds.
Similar to single-page reads (8 KB) in SQL. Use as many threads as logical CPUs in the Param.txt. Here is an example for a machine with 8 CPUs: G:\testfile.dat 8 0x0 500
sqlio -kR -s300-frandom -o8 -b8 -LS -Fparam.txt > Reads8KRandom8Oustanding.txt
Similar to extent reads I/O 64KB; use as many threads as CPUs in the Param.txt
sqlio -kR -s300 -frandom -o8 -b64 -LS -Fparam.txt > Reads64KRandom8Oustanding.txt
Similar to Read-Ahead in SQL; use as many threads as CPUs
sqlio -kR -s300 -frandom -o8 -b512 -LS -Fparam.txt > Reads512KRandom8Oustanding.txt
8 KB Writes – similar to single-page writes in SQL, which are rare, or potentially similar to Log Writes though log write sizes vary . Also Eager Writes may be similar; use as many threads as CPUs in the Param.txt
sqlio -kW -s300 -frandom -o8 -b8 -LS -Fparam.txt > Writes8KRandom8Outstanding.txt
256 KB Writes similar to Checkpoint in SQL with a realistic outstanding I/O count 100. This will really push the I/O subsystem, but is realistic and can happen. Use 1 (max 2 threads) in Param.txt – similar to checkpoint. Here is an example for a machine with 8 CPUs: G:\testfile.dat 1 0x0 500
sqlio -kW -s300 -frandom -o100 -b256 -LS -Fparam.txt > Writes256KRandom100Outstanding.txt
256 KB Writes similar to Checkpoint in SQL with a possible realistic outstanding I/O count 200. This will really, really push the I/O subsystem, but could happen. Use 1 thread in Param.txt – similar to checkpoint
sqlio -kW -s300 -frandom -o200 -b256 -LS -Fparam.txt > Writes256KRandom200Outstanding.txt
Automating the Test Described Above
You use a batch file to have these tests performed automatically
1. Create 2 Param.txt files in the SQLIO folder. One that uses a single thread and one that uses multiple threads. Call them ParamST.txt and ParamMT.txt
ParamST.txt contents might look like this: G:\testfile.dat 1 0x0 500
ParamMT.txt contents might look like this: G:\testfile.dat 8 0x0 500
2. Copy the text below and paste it into a text file.
echo ****** Read Tests *****
sqlio -kR -s300 -frandom -o8 -b8 -LS -FparamMT.txt > Reads8KRandom8Oustanding.txttimeout /T 10sqlio -kR -s300 -frandom -o8 -b64 -LS -FparamMT.txt > Reads64KRandom8Oustanding.txttimeout /T 10sqlio -kR -s300 -frandom -o8 -b512 -LS -FparamMT.txt > Reads512KRandom8Oustanding.txttimeout /T 10
echo ****** Write Tests *****
sqlio -kW -s300 -frandom -o8 -b8 -LS -FparamMT.txt > Writes8KRandom8Outstanding.txttimeout /T 10sqlio -kW -s300 -frandom -o100 -b256 -LS -FparamST.txt > Writes256KRandom100Outstanding.txttimeout /T 10sqlio -kW -s300 -frandom -o200 -b256 -LS -FparamST.txt > Writes256KRandom200Outstanding.txt
2. Then save the text file as SQLIOTest.bat in the folder that contains SQLIO.EXE and you are ready to go
3. Just run SQLIOTest.bat from Command Prompt