SQLIO

SQLIO

You’ve spent thousands on a shiny new server. Before you go ahead and configure your database environment, now is a good time to tune the software side of your storage subsystem for optimal performance. SQLIO is a Command Line IO benchmarking utility for Windows which can help you do just that. For Linux, consider utilities such as fio or bonnie++.

Here are my notes from when I configured and benchmarked the storage subsystem of three Dell Poweredge R720 servers for a previous client.

The servers were configured with a Raid1 mirror for the OS, and three Raid10 arrays for SQL Server Data, Log and TempDB files. A Dell Fast Track system configuration paper I saw makes use of a single large Raid10 array for all of these file types. I didn’t have time to test this configuration so I stuck to the more traditional school of thought of splitting these file types up on to separate physical spindles/arrays. SSD storage was considered but dismissed due to a) budget constraints and b), at the time, there wasn’t much information on it’s long term use with SQL Server or in any Data Warehouse environment.

 

The Test

The server was to host a small data warehouse that had ETL operations running overnight and predominantly read intensive activity during the day. The main variables I set out to test were:

  1. Raid Array Stripe size
  2. File system Allocation Unit Size / Cluster Size
  3. Variety of data block sizes
  4. Random and Sequential access – sequential access being key for a read mostly Data Warehouse.

NTFS Allocation Unit Size

Note, when formatting a partition from Computer Management, the Wizard calls this the Allocation Unit Size. The fsutil command line utility refers to it as Cluster size:

The default is 4096 bytes but I found that a 64kb cluster size gave the best results during this series of SQLIO benchmarks. The default 4096 may be more suitable for partitions not used for SQL Server data.

SQLIO Configuration

Download and install SQLIO. Edit C:\Program Files\SQLIO\param.txt  (Run notepad as Administrator).

  1. C:\testfile.data – change to drive being tested
  2. MSDN blog says to use as many threads as you have CPU’s for the second parameter
  3. Leave affinity mask to its default 0x0
  4. Change test file size (in MB) – 102400 = 100gb

e.g, for an 8 core machine using a 100gb test file on drive F:

 

Initial run to generate a test file

 

 

Subsequent Runs

Brent Ozar and Robert Sheldon on Simple Talk have good articles that got me started. Here’s a sample of their tests I began experimenting with:

Brent Ozars:

 

Robert Sheldons tests modified:

 

 

Batch script

Proceed to create batch file comprised of comprehensive tests suitable for your server workload.

 

Execute the batch file writing output to text file. e.g. sqlioTests.bat > sqlio_output.txt

Test Steps

  1. Access the BIOS, configure the Raid Stripe Element size (64kb / 128kb / 256kb / 512kb)
  2. Boot to Windows and format the partition(s) to be tested with the first Allocation Unit Size (4kb)
  3. Run the batch file of tests outputting the results to a text file with a sensible naming convention. Format the partition again this time with a 64kb Allocation Unit size and repeat the tests saving the output to new text files.
  4. Repeat steps 1 – 3 for each permutation of the Stripe Element.
  5. Analyse and aggregate the results of each output file in a spreadsheet.

Example Batch script calls

 

 

 

 

 

References

Leave a Reply

Your email address will not be published. Required fields are marked *