Microsoft – SQLIO

Background

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.

 

Diskspd

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
Random
Sequential or Random
 -k r for Read
w for Write
 -kw
 -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
datafile

 

 

Code

runbase.cmd




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

REM http://snipplr.com/view/21573/print-datetime-in-dos-batch-file/
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%

runtest_W_Sequential_T_64_OIO_8.bat



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"

runbase.cmd

 

Sequential – Reads versus Writes

Reads

SQL IO

Sequential_WriteOrRead_T_64_IO_8_WOrR_W_0243PM_v01

Performance Monitor

Sequential_WriteOrRead_T_64_IO_8_WOrR_W_0243PM_v02

Writes

SQL IO

Sequential_WriteOrRead_W_64_IO_8_WOrR_W_0250PM_v01

Performance Monitor

Sequential_WriteOrRead_W_64_IO_8_WOrR_W_0250PM_v02

Random – Reads versus Writes

Reads

SQL IO

WriteOrRead_T_64_IO_8_WOrR_R_0223PM_v2

Performance Monitor

WriteOrRead_T_64_IO_8_WOrR_R_0223PM

Writes

SQL IO

WriteOrRead_T_64_IO_8_WOrR_W_0233PM_v1

Performance Monitor

WriteOrRead_T_64_IO_8_WOrR_W_0233PM_v2

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

 

 

Stumbles

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…

Options

Block Size (-b)

BlockSizeInKB

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)

Buffering

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.

BlockSizeIsTooBig

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.

SQLIODeprecated

 

References

SQLIO

  1. Microsoft SQLIO: Disk performance test and benchmark tool
    http://www.slashroot.in/microsoft-sqlio-disk-performance-test-and-benchmark-tool
  2. SQLScope – SQL I/O Write Buffer Cache
    https://sqlscope.wordpress.com/2013/04/23/sqlio-write-buffer-type/
  3. Grant Fritchey – SQLIO Writes
    https://www.simple-talk.com/blogs/2011/06/28/sqlio-writes/
  4. Using SQLIO to determine I/O capacity
    https://kkryczka.wordpress.com/2011/12/02/using-sqlio-to-determine-io-capacity/
  5. SQLIO Tutorial: How to Test Disk Performance
    https://www.brentozar.com/archive/2008/09/finding-your-san-bottlenecks-with-sqlio/
  6. Microsoft SQLIO: Disk performance test and benchmark tool
    http://www.slashroot.in/microsoft-sqlio-disk-performance-test-and-benchmark-tool
  7. Paul Culmsee – Part 8 of the Demystifying SharePoint Performance Management series
    https://www.itunity.com/article/sql-sqlio-1144#sthash.aw7yvMnc.dpuf
  8. Benchmarking SQL Server IO with SQLIO
    https://www.mssqltips.com/sqlservertip/2127/benchmarking-sql-server-io-with-sqlio/

 

Benchmarking Tools

  1. Drew Robb – Data Storage Benchmarking Guide
    http://www.enterprisestorageforum.com/index.php/storage-management/data-storage-benchmarking-guide.html
  2. Server and Storage I/O Benchmarking and Performance Resources
    http://storageioblog.com/server-and-storage-io-benchmarking-resources/


Q/A

  1. How to monitor IOPS for local disks?
    https://community.spiceworks.com/topic/196856-how-to-monitor-iops-for-local-disks

 

Perfmon – Disk Counters

  1. Flavio Muratore – Windows Performance Monitor Disk Counters Explained
    https://blogs.technet.microsoft.com/askcore/2012/03/16/windows-performance-monitor-disk-counters-explained/

 

Other Applications

Storage Spaces

  1. Test Storage Spaces Performance Using Synthetic Workloads in Windows Server
    https://technet.microsoft.com/en-us/library/dn894707(v=ws.11).aspx

 

Leave a Reply

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

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