SQL Server Utilities - Using diskspd to test disk performance with SQL Server

This is the link to my GitHub diskspd project

diskspd was designed as a storage testing tool and that basically mean test your SAN or NAS but not only SAN/NAS based disks, you can test any other disk, be it a DAS, a local drive on your server or your new laptop’s SSD (I always test mine ).

diskspd is the replacement tool for the well known and beloved SQLIO Disk Subsystem Benchmark Tool that was used by the community to test disk performance for sql servers.

I can say I am a fan of diskspd (much better than SQLIO or Iometer ) and happen to use it quiet often.

When I do a performance analysis session for a client, besides using the well known Wait and Queues methodology, Performance Monitor Data Collection as well as the DMVs and other tools, part of my report would include a drives performance analysis based on diskspd stress tests. Nothing beats visualizing your drives performance in a beautiful spread sheet graph.

The benefits I see with using diskspd are:

SQL Server as an IO demanding application very much dependent on the IO capacity of the underlying disk subsystem it is relying on so knowing what we have in hands is useful and essential

In general, most database systems now a days are more likely to be stressed on IO rather than CPU or Memory

Documenting the current IO performance would serve as a baseline for the future
Comparing the performance tests of all drives lets us know that all drives deliver the same performance instead of making that assumption
At times we can spot an IO performance that is bellow the excepted

For example your vendors has informed you that you are all set and configured with an IO path of FC (fiber channel) 16 gb end to end but your tests results prove different
You can spot a significant difference between the performance of Read and Write operations and in some cases a low Write performance can be an indication to a caching problem

In some cases you may reach a solid conclusion that the given IO capacity cannot deliver the workload generated by the application(s)

This of course would be a last option after we have assured that no further tuning can be done to decrease IO
In such a case you would surly need to prove your point

When purchasing a new SAN put is to test in order to verify that you get those figures that the beautiful presentations had been promising you over the course of the selling process.

Note that for this use case you will need to hit the storage system using diskspd from multiple hosts concurrently

More that I cant think of at this time…

Being a diskspd user I used to manually get the results from the generated output in a text file, copy past to excel and manually generate graphs to visualize the stress tests results as it makes much more sense to visualize the data rather than looking at dry numbers.

Since I have a passion for automation which I find challenging and fan apart of being a time saver I was thinking to wrap my diskspd stress tests commands in a Powershell script or function and just before sitting at the coding table I did what I typically do and that is take a few short moments to stress out my googling skills and see if someone has already invented that wheel I am searching for. And in did I found this brilliant Powershell script by David Klee Founder of Heraflux Technologies that did just what I was planning to do and saved me lots of time.

When using diskspd you have 2 options to output the results, to a text file or an xml file. David’s script uses the xml option but captures the output at run time in order to construct a csv file making further analysis much easier.

The script I use is based on David’s script with minor modifications customized to meet my needs.

Note that for SQL Server we typically test with 8k and 64k block size and that is because the SQL Server’s Storage Engine is built on top of data and index pages sized 8 k each and in most cases read operation will be of 8 k (single page) or 64 k (extents which are 8 contiguous pages).

See this table taken from Pure Storage’s blog

Here is how to run the diskspd.ps1 script file for different block size tests

By the way, in my experience the underlying block size in which the NTFS partition is formatted with does not make that much of a performance difference. Check it out for your self, format an NTFS volume using any 2 different block size of your choice and then issue the same set of tests and note the results.

powershell.exe -file C:\temp\diskspd\diskspd.ps1 -time 30 -dataFile E:\temp\diskspd\diskspdtest.dat -dataFileSize 1024M -outPath C:\temp\diskspd -BlockSize 4k     -diskdpdExe C:\temp\diskspd -SplitIO N -AllowIdle Y -EntropySize 1G
powershell.exe -file C:\temp\diskspd\diskspd.ps1 -time 30 -dataFile E:\temp\diskspd\diskspdtest.dat -dataFileSize 1024M -outPath C:\temp\diskspd -BlockSize 8k     -diskdpdExe C:\temp\diskspd -SplitIO N -AllowIdle Y -EntropySize 1G
powershell.exe -file C:\temp\diskspd\diskspd.ps1 -time 30 -dataFile E:\temp\diskspd\diskspdtest.dat -dataFileSize 1024M -outPath C:\temp\diskspd -BlockSize 64k    -diskdpdExe C:\temp\diskspd -SplitIO N -AllowIdle Y -EntropySize 1G
powershell.exe -file C:\temp\diskspd\diskspd.ps1 -time 30 -dataFile E:\temp\diskspd\diskspdtest.dat -dataFileSize 1024M -outPath C:\temp\diskspd -BlockSize 512k   -diskdpdExe C:\temp\diskspd -SplitIO N -AllowIdle Y -EntropySize 1G
powershell.exe -file C:\temp\diskspd\diskspd.ps1 -time 30 -dataFile E:\temp\diskspd\diskspdtest.dat -dataFileSize 1024M -outPath C:\temp\diskspd -BlockSize 1024k -diskdpdExe C:\temp\diskspd -SplitIO N -AllowIdle Y -EntropySize 1G
powershell.exe -file C:\temp\diskspd\diskspd.ps1 -time 30 -dataFile E:\temp\diskspd\diskspdtest.dat -dataFileSize 1024M -outPath C:\temp\diskspd -BlockSize 2048k -diskdpdExe C:\temp\diskspd -SplitIO N -AllowIdle Y -EntropySize 1G
powershell.exe -file C:\temp\diskspd\diskspd.ps1 -time 30 -dataFile E:\temp\diskspd\diskspdtest.dat -dataFileSize 1024M -outPath C:\temp\diskspd -BlockSize 4096k -diskdpdExe C:\temp\diskspd -SplitIO N -AllowIdle Y -EntropySize 1G
powershell.exe -file C:\temp\diskspd\diskspd.ps1 -time 30 -dataFile E:\temp\diskspd\diskspdtest.dat -dataFileSize 1024M -outPath C:\temp\diskspd -BlockSize 8192k -diskdpdExe C:\temp\diskspd -SplitIO N -AllowIdle Y -EntropySize 1G

In an upcoming post I will share my Python code that accepts diskspd output files as an input and generates spreadsheet graphs as it’s output. Here is example to how it looks like