Testing Disk Speed

It has been a rather busy couple months. We’ve had lots of big projects at work and lots of departmental changes to go with them. Additionally, it’s finally summer and I’ve been fishing as much as possible. This being my second year with a boat, I’ve been making the most of my free time including lot’s of fishing with the kids at nearby Silver and Little Cedar lakes, and a fair amount of muskie fishing with friends and family down at Pewaukee Lake.
20150708 Northen Cropped
Actually, just last night I was fishing with a couple of friends on Pewaukee. We didn’t have much luck, but we did find a 34″ northern pike floating in the middle of the lake. It clearly hadn’t been dead more than an hour or two. It had some thin, deep cuts toward the back of it, probably the result of another fish biting it.
Anyway, my boat is fairly small for three guys, and even with the motor on full throttle the best speed we were able to make was about 18 mph. It felt pretty slow when I had recently been out with a couple different people in their boats, one that could get up to 40 mph and the other was at 45 mph (and may have even been able to go faster). There are things you can do to increase your speed while moving across the lake, such as making sure your boat is level, but the size of the engine really does make a big difference.
The week before the big database server upgrade that I was recently working on, I decided to test the disk speed on the new server. I was confident we’d be getting a big boost because we were going from spinning disks on the old server to SSDs, which are supposed to be up to 40X faster. I was pretty excited to run some tests and see what kind of improvement I got from it.
I decided to use a tool called diskspd to measure the speeds. This tool is available at Microsoft Technet here. Although it was not created specifically for SQL Server, it can be configured to run effective tests on SQL Server disks. I created the following batch file to run tests on the OS disks, the Data File disks, the Log File disks, and the TempDB disks.

@echo off
echo Automated I/O testing with Diskspd, Microsoft tool available at https://gallery.technet.microsoft.com/DiskSpd-a-robust-storage-6cd2f223
REM Author: 		Dave Gugg
REM Create Date: 	2015-05-06
REM Description: This script will automate I/O testing on a new database server.  It is a good idea to
REM           	 run this test on both the new and old server (make sure old server database is offline)
REM			 	 to get an idea of how I/O will change when moving a SQL Server instance to a new server.

REM Description of applicable diskspd arguments:
REM -b: Block size.  SQL Server uses 8K pages, but also sometimes reads full extents (64K) and read-aheads (512K).
REM -c: Size of the workload files to create.  Compare this to your mdf or ldf file sizes.
REM -d: Test duration, in seconds
REM -h: Disable hardware and software caching, from within the Windows OS layer.  This mimics SQL Server behavior.
REM -L: Capture latency info.
REM -o: Outstanding I/Os (queue depth) per worker thread.  Make this higher to increase intesity of test.
REM -r: Random reads/writes.  Use random for data file drives.
REM -s: Sequential reads/writes.  Use sequential for log file drives.
REM -t: Worker threads.  Set this to number of cores on server.
REM -w: Write percentage.  The remaining percentage will be reads, so setting this to 20 will test based on 80% reads and 20% writes.
REM -Z: Separate read and write buffers, and size the write source buffer.  A larger buffer will have more randomized data to write.

cd /d "C:\Diskspeed"

echo Starting OS drive tests
REM System databases are kept on the OS drive

diskspd -b8K -d60 -h -L -o8 -t32 -r -w5 -Z250M -c5G c:\DiskSpeed\io.dat>OS_IO_8k_result.txt
diskspd -b64K -d60 -h -L -o8 -t32 -r -w5 -Z250M -c5G c:\DiskSpeed\io.dat>OS_IO_64k_result.txt
diskspd -b512K -d60 -h -L -o8 -t32 -r -w5 -Z250M -c5G c:\DiskSpeed\io.dat>OS_IO_512k_result.txt

echo OS drive tests complete
echo Starting data drive tests
REM Data drives hold the mdf and ndf files

diskspd -b8K -d60 -h -L -o8 -t32 -r -w20 -Z1G -c50G D:\SQLData\io.dat>DF_IO_8k_result.txt
diskspd -b64K -d60 -h -L -o8 -t32 -r -w20 -Z1G -c50G D:\SQLData\io.dat>DF_IO_64k_result.txt
diskspd -b512K -d60 -h -L -o8 -t32 -r -w20 -Z1G -c50G D:\SQLData\io.dat>DF_IO_512k_result.txt

echo Data drive tests complete
echo Starting log drive tests
REM Log drives hold the ldf files

diskspd -b8K -d60 -h -L -o8 -t32 -si -w90 -Z5G -c10G E:\SQLLogs\io.dat>LF_IO_8k_result.txt
diskspd -b64K -d60 -h -L -o8 -t32 -si -w90 -Z5G -c10G E:\SQLLogs\io.dat>LF_IO_64k_result.txt
diskspd -b512K -d60 -h -L -o8 -t32 -si -w90 -Z5G -c10G E:\SQLLogs\io.dat>LF_IO_512k_result.txt

echo Log drive tests complete
echo Starting tempdb drive tests
REM Tempdb needs its own drive since it can get very intense usage

diskspd -b8K -d60 -h -L -o8 -t32 -r -w33 -Z5G -c20G G:\TempdbData\io.dat>TF_IO_8k_result.txt
diskspd -b64K -d60 -h -L -o8 -t32 -r -w33 -Z5G -c20G G:\TempdbData\io.dat>TF_IO_64k_result.txt
diskspd -b512K -d60 -h -L -o8 -t32 -r -w33 -Z5G -c20G G:\TempdbData\io.dat>TF_IO_512k_result.txt

echo Tempdb drive tests complete
echo All testing complete, press any key to exit...
pause > nul

Next I took the SQL Server Service offline on our current production server (during a maintenance window) so I could run the tests when both servers were mostly idle. The first thing I noticed is OS drive performance did not improve. This made sense since the OS drives on the new server were not SSDs, they were the built-in spinning disks. Here are the results from the rest of the tests:

20150723 Diskspd results

Overall, I saw about a 20X improvement in speed. It will be interesting to see how this improves the performance of the applications using this database server, as well as running ad-hoc queries.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s