Testing SQL Server storage read speeds using the NUL device

In this post I’m going over one method that I like to use for testing SQL Server storage read speeds.

While there are a few ways to do this, a couple that come to mind being:

• doing a SELECT * on a large table from either SSMS set to discard results or SqlQueryStress and then dividing the size of the table to the time taken
• Looking at the storage throughput during busy times either through monitoring software or even Task Manager

They might not be 100% accurate.

In the first example you still have network latency that can impact the test, especially since discarding results only stops SSMS from rendering the results set, but it still receives the data from SQL Server over the network.

For the second example, unless you’re monitoring exclusively for SQL Server’s disk throughput, you might get “interference” from other processes, including from your AV if you haven’t configured it to exclude SQL Server’s data, log and backup directories (which you should).

Here’s where backups to the NUL (yup, this time it’s NUL with one L, not two) device come in.

What’s a NUL device?

The NUL device is a special file in Windows that discards anything written to it while reporting that the write operation was successful.

Think of it as a virtual trashcan that never needs emptying regardless of how much trash you output to it.

If you come from a Linux background, NUL is the Windows equivalent of /dev/null.

Setting things up

For this I wanted a larger database than what I already had on my test instance. So, I’ve downloaded 430GB StackOverflow database made available by Brent Ozar here.

Once the download was done, I extract the files and place them in the fastest drive in my PC, a Samsung 980 Pro SSD on the PCIe 4.0 x4 M2 slot that’s connected directly to the CPU.

I then use icacls, from Command Prompt opened as admin, to grant full control permissions on those files to the SQL Server service account.

Otherwise, attaching the database would have failed with a permission denied (Operating System Error 5) message due to the service account not having permission to access to the files.

And, since I already have cmd opened as admin, I also use it to attach the database instead of having to reopen SSMS as admin – check out this post to find out why.

From SSMS, I make sure I have the right database by running the second script from this post in the context of the StackOverflowBig database.

The test

For the test I just do a simple, backup without compression and specify NUL as the backup file.

Again, NUL with one “L”, if I were to specify NULL I would just end up with backup file named NULL.

Note: If you’re doing this in a production environment or anywhere where backups are important, also include the COPY_ONLY option so that you don’t reset your database’s LSN chain and invalidate previous backups created through your standard backup process.

The result:

The important part is the last line in the message, the one that states the number of pages processed and the amount of time it took to process them, plus the nice human-friendly summary in MB/sec.

Seeing as the Samsung 980 Pro is rated for up to 7000MB/s read speeds and this is also my system drive, 6330.9 MB/s isn’t all that bad.

Conclusion

Backing up to the NUL device is a simple way, that uses readily available features of the OS, to measure the sustained read speeds of the storage on which your databases live.