Microsoft – SQLIO


Unfortunately, I never got around to using Microsoft’s SQLIO.

Yes, I have been familiar with the tool and its intention for a while.  But, somehow it has a reputation for being difficult to use.

As someone who is easily discouraged, I have just turned the other way and side stepped even when it could otherwise have been useful.



As I recently played around with Diskspd, I was tempted to go back and take a look at SQLIO.  And, so here we go


SQL IO – Tool Options

Option Meaning SQL Server Used
-b Block size of the I/O, specified as (K/M/G) 8 which translates to ( 8 KB)  is relevant for SQL Server Data file -b 8
-S Test duration in seconds N/A -S 300
-o Outstanding I/Os (meaning queue depth) per target, per worker thread N/A -o 4
-t Number of threads Number of threads -t 8
 -f Sequential
Sequential or Random
 -k r for Read
w for Write
 -L captures latency information  Latency is important for DB and so please capture & review -L
 -B Buffering
BN .. No Buffering
BY .. Buffering All or Yes .. Both Hardware & Software
BH … Hardware
BS  … Software
SQL Server provides its own buffering and so disable buffering -BN
 -d Drive





set "_LOGFILE_OPTION=Buffer~%_buffering%#WOrR~%_WriteOrRead%^#SOrR~%_SequentialOrRandom%^#NT~%_NumberofThreads%#OIO~%_outstandingIOs%#"

set _LOGFILE_DATE=%DATE:~10,4%_%DATE:~4,2%_%DATE:~7,2%
set _LOGFILE_TIME=%TIME:~0,2%_%TIME:~3,2%_%TIME:~6,2%
set _LOGFILE=log-%_LOGFILE_DATE%-%_LOGFILE_TIME%_OnDrive%_DriveLetter%Option%_LOGFILE_OPTION%.log

set _logFileFull="%_logFolder%\%_LOGFILE%"

if not exist %_logFolder%   (
    mkdir %_logFolder%

"%_APP%" -B%_buffering% -LS -d%_DriveLetter% -k%_WriteOrRead% -f%_SequentialOrRandom%^
  -t%_NumberofThreads% -o%_outstandingIOs% -s%_duration% -b%_blockSize% %_dataFile% ^
  | %_logApp% %_logFileFull%


set "_APP=D:\Program Files (x86)\SQLIO\sqlio.exe"
set _logApp="D:\Program Files (x86)\GnuWin32\bin\tee.exe"
set "_logFolder=D:\temp\sqlio\log"

set "_SequentialOrRandom=sequential"

set "_DriveLetter=E"

set "_WriteOrRead=W"

set "_NumberofThreads=64"
set "_outstandingIOs=8"

set "_duration=60"
set "_blockSize=8"

set "_dataFile=\temp\testfile20GB.mdf"



Sequential – Reads versus Writes




Performance Monitor





Performance Monitor


Random – Reads versus Writes




Performance Monitor





Performance Monitor


What we learnt ….

  1. Parameter values are case sensitive
  2. Sequential (-f sequential)  is faster that random (-f random )
  3. For Sequential I/Os, writes is actually a bit faster
    • IOPs
      • Reads is at 31,389, and Writes is at 35,785
    • Average Latency
      • Reads is 15, Writes is at 13
  4. For Random I/Os, Reads is appreciably faster than Writes
    • IOPs
      • Reads is at 2094, and for Writes 900
    • Average Latency
      • Reads is @ 242 and @ Writes is 565




There is really no reason to post another blog on something that has been around and covered like SQLIO, unless there is a bit of confession.

Here is some of mine…


Block Size (-b)


The script that I was using for our benchmark originated from DiskSpd.

In Diskspd, we were using “-b8K” to say we want 8K bytes.  This is what we need for MS SQL Server data writes.

Unfortunately, we tried same on SQLIO and we were getting really, really awful IOPs numbers.

Stayed stuck on tarmac for over a day, until changed from “-b8192” to “-b8“.

The change is due to the fact that in SQLIO, the numbers are accepted in KB.


Buffering (-B)


Option Meaning
-BN No Buffering
-BY Buffering All ( Both Hardware & Software)
-BH Buffering Hardware
-BS Buffering Software



For Microsoft SQL Server, you really want to use -BH,  No Buffering, as the database relies on its in-built Storage Engine to provide caching.


Error Messages

Error – “init_thread: VirtualAlloc (0x04000000 bytes for I/O Buffer): Not enough storage is available to process this command.”

If you enter a block size that is too big, you will get the error pasted above.


And, so if you pass along a block size of 8192, to indicate 8 KB.

You actually only need 8, as the number is expected to be in KB.

Error :- “too many threads (64) for too few stripes (0)”

too many threads (64) for too few stripes (0)

In the sample above, we passed in Sequential for -f, but should have passed in sequential.

Please keep in mind parameter and parameter values are case-sensitive.


too many threads (64) for too few stripes (0) - parameters

Download Site

Btw, SQLIO is no longer available for download from the official Microsoft site.





  1. Microsoft SQLIO: Disk performance test and benchmark tool
  2. SQLScope – SQL I/O Write Buffer Cache
  3. Grant Fritchey – SQLIO Writes
  4. Using SQLIO to determine I/O capacity
  5. SQLIO Tutorial: How to Test Disk Performance
  6. Microsoft SQLIO: Disk performance test and benchmark tool
  7. Paul Culmsee – Part 8 of the Demystifying SharePoint Performance Management series
  8. Benchmarking SQL Server IO with SQLIO


Benchmarking Tools

  1. Drew Robb – Data Storage Benchmarking Guide
  2. Server and Storage I/O Benchmarking and Performance Resources


  1. How to monitor IOPS for local disks?


Perfmon – Disk Counters

  1. Flavio Muratore – Windows Performance Monitor Disk Counters Explained


Other Applications

Storage Spaces

  1. Test Storage Spaces Performance Using Synthetic Workloads in Windows Server


Leave a Reply

Please log in using one of these methods to post your comment: Logo

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

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s