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.

  1. SQL Server only exposes one worker thread per logical CPU at a time. This is one of the main functions of SOS Scheduler inside SQL Server. What this means that the SQLIO Param.txt file should be configured to no more than use as many threads as there are logical CPUs on the machine (or as many CPUs are affinitized for SQL Server). Since this value is Number of threads per test file, I would also suggest that you test against a single file at time to keep this manageable and simple.
  2. SQL Server performs data writes periodically, when Checkpoint runs (or under memory pressure when Lazy Writer kicks in). Data modifications are done in memory and those changes flushed to disk by a single system thread that executes Checkpoint (or Lazy Writer). Therefore it makes no sense to configure more than 1 thread (maximum 2 threads if you are really want to push the I/O limits beyond capacity) in Params.txt when testing writes. In addition, SQL Server writes up to 32 pages - 256 KB - at a time. Therefore, it makes sense to use 256 KB as write size. One exception is Eager Writes, which are performed by minimally logged operations. Those would be 8 KB (or perhaps 64 KB in size).
  3. SQL Server performs data reads continuously. Most of the reads are done in 8 KB pages (singe page reads). Reads-ahead reads are done in 64-page (512 KB) chunks. Reading full extents - 64 KB in size- are also somewhat common. Therefore these three sizes are the only read sizes that make sense.
  4. Transaction Log writes vary in size depending on workload (transaction performed) but typically do not exceed 60 KB. Therefore, choosing a size to test for log writes is difficult, but as a test, I would suggest choosing 8 KB (which will cover both eager writes and log writes).
  5. SQL Server is designed to maximize sequential I/O as much as possible (both reads or writes). Random and Sequential I/O play a smaller role in today's SAN systems because of large storage caches, optimized read/write mechanisms, multiple spindles, etc. But for the purposes of stressing the system, I recommend selecting Random as SQLIO parameter in all tests.

  

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