A very important step that a DBA should take whenever deploying a new server that will serve as a SQL Server database server is something that I don’t see enough DBAs take before actually installing SQL Server.
That is, test your IO subsystem with a tool that creates synthetic (aka not-real SQL Server transactions) transactions from the server to your SAN. It is called SQLIO and it is a free tool that really has nothing to do with SQL Server: download it here. Running SQLIO will tell you the kind of performance that you can expect natively from your IO subsystem by simulating read/write transactions similar to SQL Server patterns. This way, you will capture the throughput measurements with empirical data that can be shared with your SAN team before laying down SQL Server bits.
Why is it important to baseline the SAN IO before you install and configure SQL Server? Because you want to measure the raw IO throughput without taking into considering SQL Server configurations and because you want the SAN fully optimized for SQL Server. Once the LUNS have been finalized, then you can use those drive mappings in your SQL Server installation and you have the baseline numbers of what you will expect to receive in terms of SAN performance already in hand.
There are a lot of options available to the command-line for SQLIO so start with this TechNet article that steps you through running SQLIO. That article usually serves us pretty well with customers and there are a few generalizations to keep in mind for SQL Server DBAs when running SQLIO that I’ll list here for you as well:
- It helps to measure true SAN bandwidth by setting a file size on the command line that is larger than the storage cache. You want to measure against the disks without assuming that you’ll always read from the SAN’s cache.
- Start with a low value for outstanding I/Os (command line option: -o 20) and increase this value until no further gain is noted in aggregate throughput.
- When you run the tool, you will get output files that contain information similar to what I have pasted below. Notice that I have bolded the areas in the output to give you IOPS and throughput in MBs/sec. This is your disk performance output:
using system counter for latency timings, 14318180 counts per second
parameter file used: single_lun_10.txt
file c:\dev\data01\sqlio_1_1.dat with 4 threads (0-3) using mask 0x0 (0)
4 threads reading for 30 secs from file c:\FT\data01\sqlio_1_1.dat
using 512KB sequential IOs
enabling multiple I/Os per thread with 20 outstanding
using specified size: 10 MB for file: c:\dev\data01\sqlio_1_1.dat
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+
%: 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 100