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.
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 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 a time to keep this manageable and simple.
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). Note: Lazy Writer can use as many threads as there are NUMA nodes (soft NUMA or hardware NUMA). Therefore you can consider testing with larger values (say 4) in some cases
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 read sizes that make sense to test with.
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).
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.
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
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
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
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