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.
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( echo( 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( echo( 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( echo( 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( echo All testing complete, press any key to exit... pause > nul cls exit
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:
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.