Script to measure write speeds in SQL Server

In this post I cover a script I’ve put together for measuring storage write speeds in SQL Server, namely against database data files.

This is meant to help get an idea of how the underlying storage performs when SQL Server is writing 1GB of data to a database.

At this point, you might be asking yourself: “Why not use CrystalDiskMark instead?”.
The answer is simple: you might not always be able to install/run additional software in an environment. Even more so if you work with external customers or you’re a consultant. It’s a lot simpler to ask a customer to run a script and send you the output, than it is to ask them to install and run some 3rd party software.

The script

Since the TestWriteSpeeds.sql script is too big to include here, I’ve added it in my SQL Server Scripts GitHub repository.

In short, the script does the following:

1. Loads a table variable with 1GB of data.
2. Creates a table named io_stats_writes to store IO stats related data.
3. Creates the speed_test table in which the previously generated data will be written.
4. Does 4 passes of inserting the 1GB worth of records using the TABLOCK hint.
5. Captures info from the sys.dm_io_virtual_file_stats DMV to the io_stats_writes table for every pass of the insert into the speed_test table.
6. Outputs information about the duration of the inserts, IO write stall duration, if and by how much the data and/or transaction log file grew during the test.
7. Drops the 2 tables created during its execution.

Result sets explained

The first result set shows the following:

• The name of the database.
• Total number of passes.
• The file’s physical name.
• The file type.
• The average amount of MBs written to each file (data and transaction log).
• The average duration in milliseconds of a pass.
• The average time in milliseconds that SQL Server had to wait for the IO request (the IO stall time aka latency).
• Average writes per file.
• Average amount of MB by which the data and/or transaction log file increased in size during the test

The second result set is fairly similar to the first one, but this one shows the totals for all 4 passes instead of averages.

The third result set shows the non-aggregated information for each of the 4 passes.

Limitations

While the script uses a table variable to store the initial data, it still gets written to tempdb, although, from my tests the table variable way was faster than using a temp table, the read speed of the storage on which tempdb resides can be a bottleneck if it’s slower than the write speed on any of the storage used by the data files.

Since the insert duration is calculated as pre-insert timestamp - post-insert timestamp both data and tlog files have the same duration per pass. The only way to tell if your transaction log file is the bottleneck, and not the data file, is by looking at the amount of data written to it, if and by how much the file has grown during a pass, and the IO stall time.

Note

While working on another post, I’ve ran into a situation where parallelism related waits add to the write times, but only on databases using the FULL recovery model.

If you’re seeing suspiciously long times on databases with FULL recovery model, uncomment line 136 (the OPTION(MAXDOP 1) hint) and re-run the script.

How to run it

Ideally, you run this when the database isn’t being used. Or you create a dedicated database on the same storage and config that you’d want to test.

Test runs

All my 3 test runs are done against databases created using variations of the following T-SQL.

So, the data file is 5000MB in size, with 1024MB growth increments. The transaction log file is 800MB with 300MB growth increments.
And the database is set to use the simple recovery model.

The only differences between them are the database and file names, and the locations of the files.

Slow storage

SlowDB, as the name implies, resides on the slowest drive in my machine, a SATA 3 Samsung 860 Evo SSD.

I’m reusing here the previous screenshot because it shows the results of the speed test on this specific database.

The main info to keep from this is that the average time it takes for 1GB to be written to the data file is 2 seconds, the average IO write stall time is 443 milliseconds.

With the total amount of time required to write 4GB being 8.21 seconds out of which 1.77 seconds of IO stall time.

Since the database is set to use the Simple recovery model, there’s not so much data written to the transaction log – an average of 12.57MB per pass.

And none of the files go through autogrowth events since they’re both properly sized to accommodate the test.

Fast storage

Up next is FastDB, which resides on a Samsung 980 Pro SSD connected to a PCIe 3.0 M.2 slot.

The write speed improvement brought on by the faster storage is noticeable, with an average write speed of 433 milliseconds per 1GB and 7 milliseconds of IO stall time on average.

Faster storage

Now for FasterDB, which also resides on a Samsung 980 Pro SSD, but this one is connected to a PCIe 4.0 M.2 slot.

On this drive the average write time went down to 390 milliseconds for 1GB, and the IO write stall for the data file is a consistent 1 millisecond on each pass.

Conclusion

This script should be a decent option when you’re limited on what you can use to benchmark storage write speeds.

For testing storage read speeds, check this blog post covering how to do that via backing up to NUL device.