SQLIO write buffer type

When doing a search for SQLIO you will find many blog posts showing example executions with the -BH parameter. According to the help for SQLIO the possible values for the -B parameter are H for hardware buffering, S for software buffering, Y for hardware and software buffering and N for no buffering. The use of -BH for the SQLIO read examples may be fine but I would suggest the use of -BH for the write examples is incorrect if you are trying to match SQL Server’s write access method. Using Process Monitor we can identity the write buffering method that SQL Server uses when, for example, we do simple inserts into a table:

sqlio1

You can see the sequential writes to the transaction log made by the log writer process after each commit and the random writes to the data file made by the checkpoint process. In both cases the write type is a Non-cached, Write Through write.

Now checking Process Monitor when the following SQLIO command is run using the -BH parameter

sqlio -kW -s5 -dD -o8 -fsequential -b8 -BH -LS -Fparam.txt

sqlio v1.5.SG
using system counter for latency timings, 2597685 counts per second
parameter file used: param.txt
file d:\testfile.dat with 4 threads (0-3) using mask 0x0 (0)
4 threads writing for 5 secs to file d:\testfile.dat
using 8KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
buffering set to use hardware disk cache (but not file cache)
using specified size: 100 MB for file: d:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 28807.29
MBs/sec:   225.05
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 1
Max_Latency(ms): 3
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 99  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0

we see this:

sqlio2

Note the missing Write-Through.

Specifying -BN in place of -BH we match the write type that SQL Server is actually using:

sqlio -kW -s5 -dD -o8 -fsequential -b8 -BN -LS -Fparam.txt

sqlio v1.5.SG
using system counter for latency timings, 2597685 counts per second
parameter file used: param.txt
file d:\testfile.dat with 4 threads (0-3) using mask 0x0 (0)
4 threads writing for 5 secs to file d:\testfile.dat
using 8KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
buffering set to not use file nor disk caches (as is SQL Server)
using specified size: 100 MB for file: d:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 28794.08
MBs/sec:   224.95
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 0
Max_Latency(ms): 3
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+
%:  1 99  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0

sqlio3

The difference in results for the two parameter types may be quite large if you are doing benchmarks against different types of storage infrastructure. For example, replicated SAN storage.

Advertisements
  1. #1 by Brian Rohan on June 24, 2013 - 2:43 pm

    thanks for very good information, I guess the author of sqlio agrees with you since when -BN is used above, this line is in the output “buffering set to not use file nor disk caches (as is SQL Server)”

  1. Microsoft – SQLIO | Learning in the Open

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

%d bloggers like this: