First published on MSDN on Apr 04, 2013
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.
Update: SQLIO benchmark tool was replaced with DiskSpd
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.
Read Tests
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
Write tests
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.txt
timeout /T 10
sqlio -kR -s300 -frandom -o8 -b64 -LS -FparamMT.txt > Reads64KRandom8Oustanding.txt
timeout /T 10
sqlio -kR -s300 -frandom -o8 -b512 -LS -FparamMT.txt > Reads512KRandom8Oustanding.txt
timeout /T 10
echo ****** Write Tests *****
sqlio -kW -s300 -frandom -o8 -b8 -LS -FparamMT.txt > Writes8KRandom8Outstanding.txt
timeout /T 10
sqlio -kW -s300 -frandom -o100 -b256 -LS -FparamST.txt > Writes256KRandom100Outstanding.txt
timeout /T 10
sqlio -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
Namaste!
Joseph
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.