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
Here are the blogs that pre-meditated this post.
- SQL Server – Storage – IO Pattern – Write%
- SQL Server – Storage – IO Pattern – Seeks versus Scans
The tool he talks up is DiskSpd.
Let us see if it is easy to use and addresses our need.
@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%
@ECHO ON set "driveLetter=F" call runtestOnDrive_Base.cmd
- Depending on the drive, we get different numbers
- Read and Writes
- We asked for mixed bag of reads and writes, specifically 25% writes
- We attained about 30% writes
- Review the drives your OS Swap file sits on
- Review your disk configuration
There is a wealth of functionalities that comes with this lithe product.
- Allows one to mix and match reads and writes during the same run
- Represented in percentile of writes
- Some of the competing products such as SQLIO does not offer this functionality
- Able to test both random and sequential workloads; albeit in different runs
- More Sequential pattern
- Outstanding I/Os
- The more this value is, the more I/Os you will be driving
- Thread Count
- Concurrent Operations
- Disable both software caching and hardware write caching
- 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
- The longer you run the test for the more reliable your numbers will be
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.
Concurrent Reads and Writes
Writes is 25% of Reads
Again, your system may get trashed and monitoring alarms might go off!
Make sure that the File’s Folder or Extension is excluded from your AV View.
Here we are bumping against McAfee’s mcshield.exe
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 …
- Disk Reads/sec
- Disk Writes/sec
- Disk Writes/sec
- Diskspd Utility: A Robust Storage Testing Tool (superseding SQLIO)
- Paul Culmsee – More on SQL and SQLIO – Part 8 of the Demystifying SharePoint Performance Management series
Blogs about DiskSpd
- Glenn Berry – Using Microsoft DiskSpd to Test Your Storage Subsystem
- Jose Barreto’s Blog – DiskSpd, PowerShell and storage performance: measuring IOPs, throughput and latency for both local disks and SMB file shares
- Argenis Fernandez – So Long, SQLIO. Hello, DiskSpd
Storage White Papers
- Virtualizing SQL Server with VMware: Architecting for Performance: Storage
By Michael Corey, Jeff Szastak, Michael Webster
- Suman Pinnamaneni – How to collect storage performance statistics using Windows Perfmon
Microsoft Sharepoint White Papers
- 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.
- Load Testing Your Storage Subsystem with Diskspd
- Load Testing Your Storage Subsystem with Diskspd – Part II
- Load Testing Your Storage Subsystem with Diskspd – Part III
- Description of caching disk controllers in SQL Server