Today, we’ll discuss about an interesting tool we use regularly in our lab & production environment when we configure a new system. After a machine is built, with all the hardware including storage and before installing Sql Server software, we run this toll to measure the performance of the IO drives for the expected growth of the database system. Its called SQLIOSim – SQL IO Simulator

SQLIOSim is used for stress testing as it could generate IO load. It simulates a database MDF & LDF files with heavy activity causing a lot of reads, write, Checkpoint, Lazy Writer, etc. Since it has a graphical tool, it makes it easy to use. We’ll go through some scenarios here:

SQLIOSim is a native tool available as part of Sql Server installation (starting 2008). It is located in the Binn folder.

Step 1 :

First let’s open the SQLIOSim.exe application (Run as Administrator).

SQLIOSim

Step 2:

Depending on the number of drives, you have, SQLIOSim automatically lists them all in the UI. We could pick and choose which ones we want to stress test using the “Remove” button on the top right corner. All the sqliosim.mdx, sqliosim.ldx, etc are the dummy files created, on those drives, to simulate traffic in and out of those files to test the performance of the IO drives.

Our next step is to pick the drives we want to stress test, and assign “Initial Size“, “Max Size” & “Increment” values for each file. If this machine is going to be used for a small, medium or large size database, comparable values must be provided to support the stress test. For example, the following table shows the approximate values we use in our lab:

Select file in each drive and enter corresponding values for mdx & ldx files. See the image below, for a small size database test, with slightly different values entered.

SQLIOSim Configure Values for each data drive

Step 3 :

Hit the start green start button at the top left corner to begin the test. Depending on the configured values, the test could run from several minutes to an hour. It uses the configured values to simulate database IO traffic including occasional file increments. If you open Resource Monitor, and go to “Disk” tab, you’ll see a lot of activity. For more information, highlight the “SQLIOSim.exe” image from the list, to see more details.

Step 4:

Once the test is completed, sometimes you’ll see a bunch of warnings and a huge log file with test results. In particular we’ll cover one section that is most relevant to this topic. Scroll all the way to the bottom, and your see something like for each drive/file.

SQLIOSim Test Results

Step 5: Interpretation

The above summary test results is displayed for each data/log file for each drive selected for testing. For this, we’ll focus on Target & Average Duration and Throttle metric.

Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 67, Number of times IO throttled = 9656 (copied for a different drive)

Average Duration shows the average time it took to perform actions on this file. Average over the whole duration of the test, including all read/write/etc activities. When this average duration exceedes Target Duration, SQLIOSim tries to throttle the load to improve the throughput while keeping the latency to a minimum. So, in the above example, the Average Duration is 67 ms; But SQLIOSim had to throttle the load 9656 times to make sure maximum through put is derived out of the drive. Keep in mind 67 ms is average, so some tests might have gone above 100 ms (Target Duration) that resulted in Throttling.

The goal is to keep the

• Average Duration as small as possible.
  • For Data files: See this previous post for metrics
    • Less than 15ms is excellent performance
  • For Log files:
    • Less than 5ms would be ideal
• Throttling to as small as possible.

Please keep in mind that these are relative numbers. Depending on the underlying hardware, some numbers vary; And the overall goal is to keep the numbers as close to ideal as possible.

Side Note:

Sometimes, we run into a “Access Denied” error for SQLIOSim in creating these mdx/ldx files.

• First, make sure SQLIOSim is “Run as an Administrator”.
• Make sure it has permissions on those locations
• After trying the above, if you still get the same error. Try this.
  • One workaround that worked for me is to pre-create some dummy files on each drive. Then any subsequent runs will work automatically.

Hope this helps,
_Sqltimes