MS Windows – Storage – Reading IOPS Using DiskSpd


Wanted to capture the IOPs that our current Hardware is capable of offering us.

Googled for tools and found this nice post by Glen Berry.

Using Microsoft DiskSpd to Test Your Storage Subsystem
Posted by Glenn Berry on August 4, 2015

Referenced Blogs

Here are the blogs that pre-meditated this post.

  1. SQL Server – Storage – IO Pattern – Write%
  2. SQL Server – Storage – IO Pattern – Seeks versus Scans



Microsoft DiskSpd

The tool he talks up is DiskSpd.

Let us see if it is easy to use and addresses our need.

Tool Options

Option Meaning SQL Server Used
-b Block size of the I/O, specified as (K/M/G) 8 KB is relevant for SQL Server Data file -b 8k
-d Test duration in seconds N/A -d 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
-h Disable software caching at the operating system level and hardware write caching
  • SQL Server uses its reads caching and so disabling is good
  • On capable hardware it is OK to enable caching
  • Remember for hardware this is only a request and Hardware will respond as it sees fit
 -r  Options are -r for random, s for Sequential, and si -r for random  -r
 -w  % of Writes  25% writes is viable for DB  -w25
 -L  captures latency information  Latency is important for DB and so please capture & review -L
 -c  Size of workload file  Size of the workload file  -c 50 GB







set "_dataFileFolder=%driveLetter%:\Microsoft\SQLServer\Diskspd\datafile"

set _logApp="D:\Program Files (x86)\GnuWin32\bin\tee.exe"

set "_APPFLD=D:\Downloads\Microsoft\Diskspd\v2.0.15\Extract\Diskspd-v2.0.15\amd64fre\"
set "_APPPRG=diskspd.exe"

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%.log
set _currentFolder=%cd%
set _logFolder=%_currentFolder%\log
set _logFileFull="%_logFolder%\%_LOGFILE%"

if not exist %_logFolder%   (
    mkdir %_logFolder%

if not exist %_dataFileFolder%   (
    mkdir %_dataFileFolder%

set "dataFile=emptyfile.txt"
set _datafilename=%_dataFileFolder%\%dataFile%

set "option=-b8K -d300 -o4 -t8 -h -r -w25 -L -c50G"

if exist %_datafilename% del %_datafilename%

%_APPFULL% %option% %_datafilename% | %_logApp% %_logFileFull%

if exist %_datafilename% del %_datafilename%



set "driveLetter=F"

call runtestOnDrive_Base.cmd



Drive C:



Drive D:



Drive E:



Drive F:



Drive G:





Product Version Total Reads Writes
 Drive C: 1217.39  914.86  302.53
 Drive D:  828.49  621.53  206.96
 Drive E:  1363.00  1022.90  340.10
 Drive F:  1278.83  959.72  319.10
 Drive G:  871.09  652.34  218.75




  1. Depending on the drive, we get different numbers
  2. Read and Writes
    • We asked for mixed bag of reads and writes, specifically 25% writes
    • We attained about 30% writes



  1. Review the drives your OS Swap file sits on
  2. Review your disk configuration



There is a wealth of functionalities that comes with this lithe product.


  1. Allows one to mix and match reads and writes during the same run
    • Option
      • -w
      • Represented in percentile of writes
    • Some of the competing products such as SQLIO does not offer this functionality
  2. Able to test both random and sequential workloads; albeit in different runs
    • Option
      • -s
        • Sequential
      • -si
        • More Sequential pattern
      • -r
        • Random



  1. Outstanding I/Os
    • Option
      • -o
        • The more this value is, the more I/Os you will be driving
  2. Thread Count
    • Option
      • -t
        • Concurrent Operations
  3. Disable both software caching and hardware write caching
    • Option
      • -h
        • Database engines perform their own caching and do not rely on the OS caching and so to properly replay SQL Server usage one should disable caching
        • Hardware


  1. Duration
    • Option
      • -d
        • The longer you run the test for the more reliable your numbers will be


Real Stress

Please be  careful before running this tool against your Production DB System.

We ran against our DR DB Server.

And, here are the metrics collected when we used Microsoft’s Resource Monitor to gauge overall system performance.

You want to zero in on System and diskspd Images.

High Reads


Concurrent Reads and Writes

Writes is 25% of Reads



Again, your system may get trashed and monitoring alarms might go off!


System Configuration


Make sure that the File’s Folder or Extension is excluded from your AV View.

McAfee McShield.exe

Here we are bumping against McAfee’s mcshield.exe



Other Tools

There are other tools that one can use to get IOPs readings.

This is obviously useful to validate what one is seeing.

MS Windows Performance


In Perfmon, read …

  1. PhysicalDisk
    • Disk Reads/sec
    • Disk Writes/sec
    • Disk Writes/sec




20160504-0617PM [Diskspd]



  1. Diskspd Utility: A Robust Storage Testing Tool (superseding SQLIO)


  1. Paul Culmsee – More on SQL and SQLIO – Part 8 of the Demystifying SharePoint Performance Management series


Blogs about DiskSpd

  1. Glenn Berry – Using Microsoft DiskSpd to Test Your Storage Subsystem
  2. Jose Barreto’s Blog – DiskSpd, PowerShell and storage performance: measuring IOPs, throughput and latency for both local disks and SMB file shares
  3. Argenis Fernandez – So Long, SQLIO. Hello, DiskSpd

Storage White Papers

  1. Virtualizing SQL Server with VMware: Architecting for Performance: Storage
    By Michael Corey, Jeff Szastak, Michael Webster
  2. Suman Pinnamaneni – How to collect storage performance statistics using Windows Perfmon


Microsoft Sharepoint White Papers 

  1. Storage and SQL Server capacity planning and configuration (SharePoint Server 2013)



Here are a couple of very informative postings by Anthony Nocentino,  an Enterprise Architect with Centino Systems.

  1. Load Testing Your Storage Subsystem with Diskspd
  2. Load Testing Your Storage Subsystem with Diskspd – Part II
  3. Load Testing Your Storage Subsystem with Diskspd – Part III


Disk Controllers

  1. Description of caching disk controllers in SQL 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