Measure Server IO Baseline Before Deploying SQL Server

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:

  1. 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.
  2. 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.
  3. 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:

Sun 01/29/2012

12:03:44.09

sqlio v1.5.SG

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

initialization done

CUMULATIVE DATA:

throughput metrics:

IOs/sec:   1371.11

MBs/sec:   685.55

latency metrics:

Min_Latency(ms): 24

Avg_Latency(ms): 101

Max_Latency(ms): 118

histogram:

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

 

 

Advertisements

One response to “Measure Server IO Baseline Before Deploying SQL Server

  1. Pingback: Link Resource # 42 : Feb 01 – Feb 24 « Dactylonomy of Web Resource

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s