Choosing what SQLIO tests to Run and Automating the Tests


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

Comments (9)

  1. Ignacio Salom Rangel says:

    Hi, I would like to mention that in the usingSQLIO.rtf file that comes with SQLIO when it is downloaded, it is recommended to set the value for threads in the parameter file to be the same as the number of CPUs on the host. I will quote the document " <Number of threads (per test file)> Recommend setting this equal to the number of CPUs on the host.  Possible exception is when testing many paths at one time". So I wonder why you suggest using a value of 1 or 2?  

  2. Ignacio, good question. The reason for choosing 1 thread in the write scenarios is because there is only one Checkpoint thread running inside SQL Server. Since the large majority of writing in SQL Server is performed by the Checkpoint process, you want to simulate SQL Server as close as possible. Thus use 1 thread (possibly 2 to see how far you can push the system).

  3. nicofer says:

    Hi jo, great post!

    I have a question, can i use the folowing patterns to test a disk for sql transaction log write ?

    sqlio -kW -s300 -fsequential  -o8 -b2 -LS -FparamMT.txt

    sqlio -kW -s300 -fsequential  -o8 -b8 -LS -FparamMT.txt

    sqlio -kW -s300 -fsequential  -o8 -b64 -LS -FparamMT.txt

  4. fawkes says:

    Hi, nice post.

    I guess SQL Server doesn't use software caching for it IO operations. Isn't it better to use -BH?

  5. _Jo.Pi_ says:

    Nicofer, yes, you can use these. Remember your goal is to stress-test your disk and see where it "cracks". That's why I prefer the random I/O tests because it exercises the disks more.

    Fawkes, good observation. You don't need to use -BH because the default setting for SQLIO is -BN, which mimics SQL Server behavior. SQL Server uses both FILE_FLAG_NO_BUFFERING and FILE_FLAG_WRITE_THROUGH to create a file via CreateFile(). So if you would like to match SQL Server's behavior you are better off using -BN explicitly or let SQLIO pick it as its default option.  Using -BH will enable the disk drive's hardware cache but not the file cache (i.e., only FILE_FLAG_NO_BUFFERING will be set).

  6. Peter Van Wilrijk says:

    Hi Joseph,

    Great post.

    Our SQL expert propose to use 0 outstanding IO to mimic logfile write behaviour since SQL gives back control to an application (eg Save button) once transaction is complete in the logfile.  For testing writing in datafiles other values should be used.

    I wonder whether outstanding IO's have a meaning in Read tests?

    Kind regards,

    Peter.

  7. Egor says:

    Great post and very nice explanation of SQL I/O patterns.  But I have a question. why do you use only 500 Mb files for tests? Modern SCSI controllers has 1024-2048 Mb of cache memory on board . and SANs much much more. So such small files will be almost entirely loaded in cache. I've tested with small files about 1 GB and files of 20 Gb and observe difference in performance up to 100 times. I would say 20 Gb is closer to real db size but same time I know how dramatically SQL Server perfomance depends of SCSI battery health and state. Thereby SQL really widely use HW cache.

    So I wonder what is optimum size of file for testing with sqlio ?

  8. A.Y. says:

    Hello Jo, Typically Data warehousing environments write randomly and sequentially pending on the purpose of the query, they also read randomly sequentially and in cases like read ahead, they may read up to 512KB. isn't it then a good idea to test both random and sequential read/writes? and I agree with Egor, 500MB is typically not big enough. I tend to use 20GB and BH

  9. Lonny Niederstadt says:

    Valuable writeup!

    One note:

    Provided the physical SQL Server HBA has had its maximum transfer size increased from default 512kb and sufficient contiguous rowstore extents, SQL Server can issue reads as large as 4 mb (for Emulex, 2 mb for QLogic).

    Niko Neugebauer has blogged that Columnstore reads can be as large as 8 mb – although I don't know of an HBA for physical Windows + SQL Server that will allow an 8 mb read.

    Even if a system is primarily OLTP focused with 8kb and 64kb reads, large reads can be important for index maintenance, statistics updates, checkdb performance.