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

SQL Server – Storage – IO Pattern – Write%

Background

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
    https://danieladeniji.wordpress.com/2016/05/03/sql-server-storage-io-pattern-seeks-versus-scans/

 

Database \ File Aggregates

Guide

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.

Code



set nocount on;

declare @tblDatabaseSkip TABLE
(
	[name] sysname
)

insert into @tblDatabaseSkip
([name])
select 'DBBackup'
union
select 'DBUtility'
union
select 'csSchoolContent'
union
select 'csLogins'
union
select 'nettraffic_db'
union
select 'repository'
union
select 'TestLogShipping'
union
select 'TSRDestribution'

;with cteBase
(
	  [DB]
	, [type_desc]
	, [Drive]

	, [num_of_reads] 
	, [num_of_bytes_read]

	, [num_of_writes]
	, [num_of_bytes_written]

	, [%writes]
	, [%byteswritten]

)
as
(

	SELECT

		  [DB]
			= 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]
			= 
				cast
					(
						sum([num_of_writes]) * 100.00
							/ NULLIF(
								sum([num_of_reads] + [num_of_writes])
								, 0
							)
						as decimal(10, 2)
					)

		, [%byteswritten]
			= 
				cast
					(
						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)


)

select 
	  [DB]
	, [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 )

Output

FileIOStats-20160503-1029AM

 

Explanation

  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 1.xxx])

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