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

SQL Server – Storage – IO Pattern – Write%


As we prepare to dig a bit deeper in Storage IOPs requirements, I found that I need an insight into Read and Write%.


Referenced Blogs

Here are the blogs that pre-meditated this post.

  1. SQL Server – Storage – IO Pattern – Seeks versus Scans


Database \ File Aggregates


We query  the sys.dm_io_virtual_file_stats dmv for I/O statistics.

We grouped by database, logical disk, and file type ( rows and log ).

And, we skipped system databases and some of the less interesting databases.


set nocount on;

declare @tblDatabaseSkip TABLE
	[name] sysname

insert into @tblDatabaseSkip
select 'DBBackup'
select 'DBUtility'
select 'csSchoolContent'
select 'csLogins'
select 'nettraffic_db'
select 'repository'
select 'TestLogShipping'
select 'TSRDestribution'

;with cteBase
	, [type_desc]
	, [Drive]

	, [num_of_reads] 
	, [num_of_bytes_read]

	, [num_of_writes]
	, [num_of_bytes_written]

	, [%writes]
	, [%byteswritten]



			= DB_NAME ([vfs].[database_id]) 

		, [mf].type_desc

		, [Drive]
			= LEFT ([mf].[physical_name], 2)

		, [num_of_reads] 
			= sum([num_of_reads])

		, [num_of_bytes_read]
			= sum([num_of_bytes_read])

		, [num_of_writes]
			= sum([num_of_writes])

		, [num_of_bytes_written]
			= sum( [num_of_bytes_written] )

		, [%writes]
						sum([num_of_writes]) * 100.00
							/ NULLIF(
								sum([num_of_reads] + [num_of_writes])
								, 0
						as decimal(10, 2)

		, [%byteswritten]
						sum( [num_of_bytes_written] ) * 100.00
							/ NULLIF(
								sum( [num_of_bytes_read] + [num_of_bytes_written] )
								, 0
						as decimal(10, 2)

	FROM sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]

	JOIN sys.master_files AS [mf]
		ON [vfs].[database_id] = [mf].[database_id]
		AND [vfs].[file_id] = [mf].[file_id]

	group by
			  DB_NAME ([vfs].[database_id]) 
			, [mf].type_desc
			, LEFT ([mf].[physical_name], 2)


	, [type_desc]
	, [Drive]

	, [num_of_reads] 
	, [num_of_writes]
	, [%writes]

	, [num_of_bytes_read]
		= cast
				( [num_of_bytes_read] / 1E9 )
					as decimal(30, 2)

	, [num_of_bytes_written]
		= cast
				( [num_of_bytes_written] / 1E9 )
					as decimal(30, 2)

	, [%byteswritten]

from   cteBase

-- skip system databases
where  DB not in ( 'master', 'model', 'ReportServer', 'ReportServerTempDB' )

-- ignore some user databases
and    DB not in ( select [name] from @tblDatabaseSkip )





  1. File Type
    • Log
      • A lot more write transactions, 98%+, than reads
      • One to one ratio of Bytes written to Bytes read
    • Data
      • Much more read transactions compared to write transactions
      • Less than 5% bytes written compared to bytes read


QLogic – Determine IOPs (Using QConvergeConsole CLI [version])

QLogic – Determine IOPs (Using QConvergeConsole CLI)

1) Install QConvergeConsoleCLI

  • Make sure to note the Install Folder

2) Launch QConvergeConsole CLI

  • Available via Menu (QLogicManagementSuite \ QConvergeConsole CLI)
  • Command Line (Install Folder \ qaucli.exe)

3) The Console Application is displayed

  • The Application scans for the QLogic Adapters….
  • And, displays the Main Menu

4) Access “Adapter Statistics”

  • From the main menu, enter 5 to Access “5: Adapter Statistics”
  • From the “FC Statistics”, enter 1 to Access to “1: Display FC Port Statistics”
  • The HBA usually comes with dual ports, choose each port 1 or 2
  • From the “HBA Statistics Menu”, enter 4 to “Display HBA Statistics”

5) The Stats are displayed under the following headings

  • HBA
  • Port Errors
  • Device Errors
  • Reset
  • I/O Count
  • IOPS
  • BPS
  • Time