SQL Server 2012 : Storage Systems (part 7) - Measuring Performance - Storage Performance Testing

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
9/24/2013 4:17:15 AM

4.9 Storage Performance Testing

You have likely noticed how we have stressed that each workload is different. No single storage system will solve every technical or business problem. Any system must be validated prior to its production deployment.

You should draw a distinction between validation and testing. If you are able to re-create your production system with an exact replica and run exactly the same workload, your results should mirror production. Unfortunately most administrators are not fortunate enough to field duplicate test and production systems. Validation allows these administrators to collect useful data and later apply the data to real problems.

It is important to understand how your system will perform given specific parameters. If you are able to predict and verify the maximum number of IOPS a given system will tolerate, you can then apply this knowledge to troubleshooting performance issues. If latency performance counters are high, and you notice that a 20-drive RAID 1+0 volume is trying to absorb 5,000 IOPS, it should be clear that you have exceeded the system I/O capacity.

Prior to beginning any storage testing it is important to alert relevant system owners. Running a peak load test on a shared storage system will impact all the storage-connected systems. If the testing is executed against an array with automatic storage tiering, it can cause the system to migrate test data into higher storage tiers and depreciate important production data. It is critical to understand the effects of testing, so be sure to exercise care!

Microsoft has created two tools that are useful for performance testing: SQLIOSim for system validation, and SQLIO for performance measurement.

SQLIOSim is designed to simulate SQL Server I/O, and you can think of it as a functional test tool. It will stress SQL Server, the Windows Server, and storage, but it won’t push them to maximum performance. SQLIOSim can be downloaded at

Before getting started, note that SQLIOSim will report an error if latency exceeds 15 seconds. It is perfectly normal for storage I/O to show occasional high latency. We recommend using the GUI to run functional system testing (Figure 13), although the tool can be also be executed from a command prompt.



Many advanced storage arrays offer automated tiering and de-duplicating features. Synthetic test tools like SQLIO and SQLIOSim generate test files by writing repeating data, usually zeros. Advanced storage arrays will de-duplicate or return data with artificial velocity. It is especially important to use a test tool that creates data on top of an NTFS data partition. If the storage array answers a read request from data pages that have not been written, it will return zero data directly from the storage processor. Any of these conditions will produce performance data that is not representative of actual production performance. Be sure you understand the capabilities of the storage platform you are testing. If you receive test performance data that seems too good to be true, it probably is.

SQLIO is a tool that simply generates I/O. This application is executed from the command line and does not need SQL Server to run. Download SQLIO from

We recommend testing the storage system to performance maximums and setting up a test that mimics your best guess at a production workload. SQLIO is executed from the Windows command line. After installing SQLIO, open the param.txt file located in the installation directory. The default path is C:\Program Files (x86)\SQLIO. The param.txt file contains the following code:

c:\testfile.dat 2 0x0 100
#d:\testfile.dat 2 0x0 100

The first line in the param file defines where the test file is located. Use the pound sign (#) to comment out a line in the param.txt file. The first number following the test file defines the number of threads per test file. Microsoft recommends setting this to the number of CPU cores running on the host machine. Leave the mask set at 0x0. Finally, the size of the test file in MB is defined.

Ensure that the test file is large enough to exceed the amount of cache dedicated to your storage volumes. If the host file is too small it will eventually be stored within the array cache.

SQLIO is designed to run a specific workload per instance. Each time you execute SQLIO.exe with parameters, it will define a specific type of I/O. It is entirely permissible to run many instances of SQLIO in parallel against the same test file.

Be sure to coordinate testing with your storage administrator before you start these tests, as they are designed to heavily stress your I/O system. It is best to begin by confirming any calculated assumptions. If you have 100 10K SAS disks configured in a RAID 1+0 set, you can assume random I/O will perform 14,000 reads. To test these assumptions run a single SQLIO.exe instance:

sqlio –kR –s300 –frandom –o1 –b8 –LS –Fparam.txt

The –k option sets either R (read) or W (write). Set the test run length using the –s function. We recommend testing for at least a few minutes (this example uses 5 minutes). Running a test for too short a time may not accurately demonstrate how a large RAID controller cache will behave. It is also important to pause in between similar tests. Otherwise, the array cache will reuse existing data, possibly skewing your results.

The –f option sets the type of I/O to run, either random or sequential. The –o parameter defines the number of outstanding I/O requests. You use the –b setting to define the size of the I/O request, in KB. This example tests very small I/O. SQL Server normally reads at least a full 64KB extent.

You use the –LS setting to collect system latency information. On a 64-bit system you can add the –64 option to enable full use of the 64-bit memory system. Finally, you define the location for the param.txt file using the –F option. Following is the test output:

sqlio v1.5.SG
using system counter for latency timings, 14318180 counts per second
parameter file used: param.txt
file c:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads reading for 300 secs from file c:\testfile.dat
using 8KB random IOs
enabling multiple I/Os per thread with 8 outstanding
size of file c:\testfile.dat needs to be: 104857600 bytes
current file size: 0 bytes
need to expand by: 104857600 bytes
expanding c:\testfile.dat ... done.
using specified size: 100 MB for file: c:\testfile.dat
initialization done
throughput metrics:
IOs/sec: 13080.21
MBs/sec: 102.18
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 0
Max_Latency(ms): 114
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 37 59 3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

This single 8K random test started by creating the 100MB test file. In this case, the test was generated on a VM guest using a flash drive, so the results are not representative of what you might see in a real-world test. The test ran for five minutes with one outstanding I/O, and generated 13,080 IOPS and 102 MB/sec. It peaked at 114 milliseconds of latency but averaged under a millisecond for access.

This test is a perfect demonstration of how a system can fail to utilize its maximum potential performance. Only eight outstanding I/Os completed, which failed to push the I/O subsystem. To properly test a given system, it is a good idea to scale the workload dynamically to determine how it will perform under increasing loads. To accomplish this you can script SQLIO:

sqlio –kR –s300 –frandom –o1 –b8 –LS –Fparam.txt
sqlio –kR –s300 –frandom –o2 –b8 –LS –Fparam.txt
sqlio –kR –s300 –frandom –o4 –b8 –LS –Fparam.txt
sqlio –kR –s300 –frandom –o8 –b8 –LS –Fparam.txt
sqlio –kR –s300 –frandom –o1 –b16 –LS –Fparam.txt
sqlio –kR –s300 –frandom –o1 –b32 –LS –Fparam.txt
sqlio –kR –s300 –frandom –o1 –b64 –LS –Fparam.txt
sqlio –kR –s300 –frandom –o1 –b128 –LS –Fparam.txt
sqlio –kR –s300 –frandom –o1 –b256 –LS –Fparam.txt

Run this batch file from the command line and capture the results to an output file:

RunSQLIO.bat > Result.txt

Ideally, you will test several different scenarios. Create a batch file with each scenario that scales from 1 to 256 outstanding I/Os, such as the following:

  • Small-block random read performance:
    sqlio –kR –s300 –frandom –o1 –b8 –LS –Fparam.txt
  • Small-block random write performance:
    sqlio –kW –s300 –frandom –o1 –b8 –LS –Fparam.txt
  • Large-block sequential read performance:
    sqlio –kR –s300 – fsequential –o1 –b8 –LS –Fparam.txt
  • Large-block sequential write performance:
    sqlio –kR –s300 –fsequential –o1 –b8 –LS –Fparam.txt

Running these scenarios as a single batch file will automate the I/O system testing process. The most important data points are the latency and performance measurements. When the tests exceed your maximum latency tolerance, usually not more than 20 milliseconds, you have exceeded the capabilities of your I/O subsystem. If the system does not meet your overall performance requirements with adequate response times, then you need to investigate the system for errors or configuration issues. Ultimately, you may need to optimize the storage system’s performance to ensure that previously specified requirements are met.

Top 10
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
- Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
- Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
programming4us programming4us