How To Test SQL Server Disk IO Throughput

When managing large databases or high-transactional databases, the performance of your underlying storage system is crucial, whether it is internal server storage, a local SAN, cloud storage, or a combination of those.

While high IOPs are nice, having enough throughput of your drives is what matters when it comes to moving data around in SQL Server.

It can also be challenging to justify the server's performance needs to management, and in the past, I have found these tools to be a valuable resource for accomplishing this.

Here are some of the tools I use to measure disk performance throughput.

These tools will work for any on-prem physical, on-prem virtual machine, and cloud virtual machine.

Crystal Disk Mark

For a quick and short test to get an idea of what throughput a drive has, I download the standard edition (ZIP) of Crystal Disk Mark.

Once I extract the files, I run the 64-bit executable.

Once it opens, there are several settings you need to select. The first drop down is how many 'tests' you want to do. For most instances, I just do 1 test.

The next option is the size of the test file. This is important to take note of because you want to be sure the size of your test is beyond any 'cache' size of your controllers, if you have cache on them. If you are not getting past the cache size, you won't get a true test of your actual read/writes to disk to understand your throughput.

Then select your drive/LUN/disk/clustered folder you wish to test.

Once your options are selected, click 'All' to perform the test.

Once the test is done, you will see the results of the throughput of the test. The top two results are the ones you want to pay attention to. This is the read and write throughput in MBps. As you can see here, this is pretty fast at over 2k MBps.

DiskSpd (formally SQLIO)

DiskSpd is another tool I like to use if I need to do a more 'real world' scenario testing or long testing of disks/SAN. This tool is a command-line tool that can be executed on the server. This is also beneficial because you can spin up multiple instances simultaneously, testing multiple drives to assess your bandwidth to the SAN and verify multi-pathing to ensure you are achieving your expected throughput for multiple drives at once.

There are so many options when it comes to using DiskSpd, so I'm not going to go into all of them.

My tests are read random, write random, read sequential, and write sequential. The block size for this test is 64k. You can also test in 8k if you'd like. We run each test for 5 minutes and output the results to a text file.

If you are interested in visually reviewing these results, see my blog post Compile and Present DiskSpd Results Using PowerShell and PowerBI.

Run the sample below after adjusting your paths and drives in a command (cmd) file to test. I would suggest running this in 'off-hours' if you are testing in production.

rem @echo off 

rem -kW and -kR: means we’re testing writes or reads

rem -t8 and -o8: means 8 threads with up to 8 outstanding requests at once.  SQLIO isn’t CPU-bound at all, and you can use more threads than you have processors.  The more load we throw at storage, the faster it goes - to a point.

rem -s120: means the test will last 120 seconds

rem -b8 and -b64: the size of our IO requests in kilobytes.  SQL Server does a lot of random stuff in 8KB chunks, and we’re also testing sequential stuff in 64KB chunks.

rem -frandom and -fsequential: random versus sequential access.  Many queries jump around randomly in the database, whereas things like backups, bulk loads, and table scans generally work sequentially.

cd "\Sql\DiskSpd\"

rem ************** CHANGE THESE SETTINGS ********************

SET DriveLetter=C:\

SET ReportPath=C:\Sql\DiskSpd\C\

rem ************** CHANGE THESE SETTINGS ********************

echo %DATE%_%TIME%

diskspd -b64K -d300 -o32 -t10 -h -r -w0 -L -Z1G -c20G %DriveLetter%test.dat > %ReportPath%64kreadoutputrandom.txt

diskspd -b64K -d300 -o32 -t10 -h -r -w100 -L -Z1G -c20G %DriveLetter%test.dat > %ReportPath%64kwriteoutputrandom.txt

diskspd -b64K -d300 -o32 -t10 -h -w0 -L -Z1G -c20G %DriveLetter%test.dat > %ReportPath%64kreadoutputsequential.txt

diskspd -b64K -d300 -o32 -t10 -h -w100 -L -Z1G -c20G %DriveLetter%test.dat > %ReportPath%64kwriteoutputsequential.txt

echo %DATE%_%TIME%

pause

Conclusion

I hope this helps you in your quest for performance and provides you with some resources to request additional hardware to support the needs of your database system.

If you have any questions, feel free to reach out or comment below!