How fast can your database go – I don’t know – Use SQLIO ?


SQLIO is a tool provided by Microsoft which can also be used to determine the I/O capacity of a given configuration.  You can find it here or simply google it. The purpose of the tool is to help you compare various disk or other IO device configurations for use as an MS  SQL database location.  While you might normally use sqlio to test SAN performance for database use – its equally valid for traditional direct attach storage environments.  Basically SQLIO is a tool which can be used to determine the I/O capacity of a given hardware configuration. Its purpose is NOT to simulate I/O patterns of SQL server but rather to testaq variety of I/O types and sizes.

 

When using SQLIO to test bear in mind

1) test a variety of I/O sizes and types. Use combinations of I/O sizes for read/write and sequential/ random access.

2) make sure the zise of the test files exceeds the amount of cache in the SAN or other devices

3) Know your hardware and the theoretical limits it has. Especially with SAN considered static mapping of LUNS versus multipathing

4) Test each I/O path individually before testing combinations to ensure the performance increases linearly (if thats whats expected)

5) Duration of the test runs may need to be varied. Too short a test period and you may not see potential issues. Also some larger sans may not be fully “optimised” until run for a period of time

6) allow time between tests for the system to return to “idle”

 

Commonly used SQLIO.exe options

-o

Specify the number of outstanding I/O requests. Increasing the queue depth may result in a higher total throughput. However, increasing this number too high may result in problems (described in more detail below). Common values for this are 8, 32, and 64.
-LS Capture disk latency information. Capturing latency data is recommended when testing a system.
-k Specify either R or W (read or write).
-s Duration of test (seconds). For initial tests, running for 5-10 minutes per I/O size is recommended to get a good idea of I/O performance.

-b Size of the IO request in bytes.
-f Type of IO to issue. Either ‘random’ or ‘sequential’.
-F Name of the file which will contain the test files to run SQLIO against.

** Thanks to Steve Wharton for introducing me to this tool