Storage Systems - SQL Server 2012 Internals and Troubleshooting - page 106

Storage Performance Testing
You have likely noticed how we have stressed that each workload is different. No single storage sys-
tem will solve every technical or business problem. Any system must be validated prior to its produc-
tion deployment.
You should draw a distinction between validation and testing. If you are able to re-create your pro-
duction system with an exact replica and run exactly the same workload, your results should mirror
production. Unfortunately most administrators are not fortunate enough to i eld 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 specii c 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.
WARNING 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 under-
stand the effects of testing, so be sure to exercise care!
Microsoft has created two tools that are useful for performance testing: SQLIOSim for system vali-
dation, 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 http://support.microsoft.com/kb/231619 .
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 4-13), although the tool can be also be executed from a com-
mand prompt.
WARNING Many advanced storage arrays offer automated tiering and de-
duplicating features. Synthetic test tools like SQLIO and SQLIOSim generate
test i les by writing repeating data, usually zeros. Advanced storage arrays will
de-duplicate or return data with artii cial 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.