MS Windows – Storage – Reading IOPS Using DiskSpd

Background

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
http://sqlperformance.com/2015/08/io-subsystem/diskspd-test-storage

Referenced Blogs

Here are the blogs that pre-meditated this post.

  1. SQL Server – Storage – IO Pattern – Write%
    https://danieladeniji.wordpress.com/2016/05/03/sql-server-storage-io-pattern-write/
  2. SQL Server – Storage – IO Pattern – Seeks versus Scans
    https://danieladeniji.wordpress.com/2016/05/03/sql-server-storage-io-pattern-seeks-versus-scans/

 

Tool

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
-h
 -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

 

 

 

Code

runtestOnDrive_Base.cmd

@ECHO ON

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 "_APPFULL=%_APPFLD%\%_APPPRG%"

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%.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%

runtestagaintDriveF.cmd


@ECHO ON

set "driveLetter=F"

call runtestOnDrive_Base.cmd

Output

Images

Drive C:

DriveC

 

Drive D:

DriveD

 

Drive E:

DriveE

 

Drive F:

DriveF

 

Drive G:

DriveG

 

Tabulated

 

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

 

 

Explanation

  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

 

Follow-up

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

 

Functionality

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

Stable

  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

 

Stress

  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

Validity

  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

ResourceMonitor-20160504-0148PM

Concurrent Reads and Writes

Writes is 25% of Reads

ResourceMonitor-20160504-0158PM

 

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

 

System Configuration

Anti-Virus

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

AntiVirus-20160504-0234PM

 

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

Perfmon

20160504-0617PM

DiskSPD

20160504-0617PM [Diskspd]

References

Tool

  1. Diskspd Utility: A Robust Storage Testing Tool (superseding SQLIO)
    https://gallery.technet.microsoft.com/DiskSpd-a-robust-storage-6cd2f223

 

  1. Paul Culmsee – More on SQL and SQLIO – Part 8 of the Demystifying SharePoint Performance Management series
    https://www.itunity.com/article/sql-sqlio-1144

 

Blogs about DiskSpd

  1. Glenn Berry – Using Microsoft DiskSpd to Test Your Storage Subsystem
    http://sqlperformance.com/2015/08/io-subsystem/diskspd-test-storage
  2. Jose Barreto’s Blog – DiskSpd, PowerShell and storage performance: measuring IOPs, throughput and latency for both local disks and SMB file shares
    https://blogs.technet.microsoft.com/josebda/2014/10/13/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
    http://blog.purestorage.com/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
    http://www.pearsonitcertification.com/articles/article.aspx?p=2240989&seqNum=4
  2. Suman Pinnamaneni – How to collect storage performance statistics using Windows Perfmon
    https://community.emc.com/docs/DOC-28792

 

Microsoft Sharepoint White Papers 

  1. Storage and SQL Server capacity planning and configuration (SharePoint Server 2013)
    https://technet.microsoft.com/en-us/library/cc298801.aspx

 

Centrino

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
    http://www.centinosystems.com/blog/sql/load-testing-your-storage-subsystem-with-diskspd/
  2. Load Testing Your Storage Subsystem with Diskspd – Part II
    http://www.centinosystems.com/blog/sql/load-testing-your-storage-subsystem-with-diskspd-part-ii/
  3. Load Testing Your Storage Subsystem with Diskspd – Part III
    http://www.centinosystems.com/blog/sql/load-testing-your-storage-subsystem-with-diskspd-part-iii/

 

Disk Controllers

  1. Description of caching disk controllers in SQL Server
    https://support.microsoft.com/en-us/kb/86903

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