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 = 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.
- 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.
- 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.
- 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.)
10 Comments. Leave new
Hey Brent
I share you like of the CrystalDiskMark/Info tools, but I want to point out that the .zip versions are still available – just downloaded them again from the site, just to make sure 🙂
PS
Thanks for a(nother) interesting post.
Hi Brent
I have just installed the tool but I do not see Mix(mb/s) column , I mean I cannot choose R70%W30%, any Idea?
Sorry , please ignore ,everything is ok.
Hi Brent! Why no mention of DiskSpd (former sqlio.exe)? – https://github.com/Microsoft/diskspd
Because diskspd is a pain in the ass to use compared to the above post.
Zip file available here: https://osdn.net/projects/crystaldiskmark/releases/71859
Great, thanks!
The link provided by Terry is for a specific version and will eventually become stale.
Visit https://crystalmark.info/en/download/ for an up-to-date list of downloads. This includes the zip file linked by Terry, the installer linked by Brent, the Microsoft Store link, and the source code. It also includes the Shizuku edition (Shizuku edition is just a skin).
Hi Brent, what is the most important indicator in CDM for testing SSD or NVMe disk performance for SQL server please? Is it RND 4K Q32T16 closest to random tiny operations – many done at a time?
Many thanks!
Paul – make sure to read the post carefully. The answer’s in there. Thanks!