How to Use CrystalDiskMark 7 to Test Your SQL Server’s Storage


I bet you wanna know whether your storage is hot or not, and there’s no quicker, easier way to get a rough idea of your storage’s capabilities than to fire up CrystalDiskMark. This month, CrystalDiskMark released an all-new version 7, and it makes for even better testing.

Start by downloading it and installing it – sadly, there’s no zip file version anymore that you can stick on a network share and run remotely.

After it launches, click the Profile dropdown at the top:

The Peak Performance + Mix setting runs a pretty cool mix of tests that will push your storage hard. Note that I don’t try to get CDM to replicate exactly how SQL Server does IO: I’m just trying to get a quick 5-minute idea of whether my storage is hot or not.

Then, across the top there will be 4 dropdowns:

  1. 1 = the number of tests you want to run. If you only run one test, it’ll finish quickly, but it’ll be vulnerable to other activity running on the server at that moment. If you’re testing an already-in-production server, just do 1 test, but if you’re testing the bejeezus out of one before going live, use more tests.
  2. 1GiB = the test file size. A small 1GiB file will finish the test quickly, but if your storage has a caching component, you might get deceivingly fast results. I like using a 64GiB file if I’ve got the luxury of more time for testing, but it really will write out 64GiB of data, so make yourself some coffee.
  3. S: = the drive letter or folder you want to test. You wanna test where the data files, log files, and TempDB live in order to get a good idea of how everything performs.
  4. R70%/W30% = for the mixed test, the amount of reads vs writes happening simultaneously.

If you want to more closely replicate how your own server does IO, use the mix dropdown to pick a ratio of reads vs writes that mirrors your own server’s workload, and use the Queues and Threads window to pick the amount and sizes of IO. I cover those in my Mastering Server Tuning class, but if you haven’t been through that, you can just stick with the defaults for now.

Click the All button, and all of the tests will run.

When it finishes, click on the text entry box at the bottom of CrystalDiskMark, and you can type in some descriptive info about your storage, then hit enter. This is useful for when you take screenshots like these.

Here’s local ephemeral NVMe storage in an AWS instance:

And here’s general purpose (GP2) SSD – note that throughput on this goes up as drive size goes up, and this drive is only 500GB, so it’s pretty cripplingly slow:

When you’re trying to figure out whether your SQL Server’s storage is any good, just compare it to your laptop:

This is especially important with cloud servers: so often, you can sink a ton of monthly fees into storage that performs far, far worse than a $50 USB3 thumb drive. (I’m certainly not saying you shouldn’t go to the cloud – I’m just saying you want to know the strengths and challenges of the infrastructure life dealt ya so you can understand why query tuning and index tuning pay off so well in the cloud.)

Previous Post
New Multi-Server Emails for SQL ConstantCare®
Next Post
SQL Server Doesn’t Encrypt Data By Default.

10 Comments. Leave new

Leave a Reply

Your email address will not be published.

Fill out this field
Fill out this field
Please enter a valid email address.