SQLIO Benchmark Testing SSDs

SQLIO is a tool available for download from Microsoft that you can use to help determine the I/O capacity of your disk subsystem. Brent Ozar gives a great little video showing you how to use and configure your test batch scripts, as well as how to analyse your results.

There is a  section on SQLServerPedia that takes you through “How To Import your SQLIO Results into SQL Server” , the code is available for you to download which creates a couple of tables and a stored procedure which works through your SQLIO results text file to allow you to more easily analyse the results.

When using these scripts I did come across a couple of small issues around decimal places and length calculations which I have amended in the SQLIO_TestPass table creation and made the corresponding changes in the stored procedure to collate the data to report on.

 

Now that we have briefly gone over what is SQLIO, how you can download it, having created our import tables we are ready to start our testing of SSD’s. As Stated on SQLServerPedia, you need to ensure that your tests are actually getting to the disk and not just staying in cache. This means you are wanting to flush the cache, so your test files need to be larger than your cache. While recently on a client site working on a hardware refresh project which entailed moving onto newly purchased SSD’s. The hardware configuration we are testing here is as follows:

SQLIO Test 1 Configuration

• 10 GB Test file
• 64 Kb Block size
• 8 Kb Block size
• 4 Threads with 1, 8 & 64 Outstanding IO requests
• 8 Threads with 1, 8 & 64 Outstanding IO requests
• 16 Threads with 1, 8 & 64 Outstanding IO requests
• 32 Threads with 1, 8 & 64 Outstanding IO requests
• Individual test duration – 180 seconds
• Random Reads
• Random Writes
• Sequential Writes

8 Kb block size test result

Random Reads

 

 

 

Random Writes

 

 

Sequential Writes

 

 

64 Kb block size test result

Random Reads

 

 

Random Writes

 

 

Sequential Writes

 

 

After completing the tests and getting the results as seen in the graphs above, as would normally be expected the Random Read results show the SSD’s outperforms the non SSD’s for both sets of test (8Kb & 64 Kb block sizes). But the Write results were less than impressive. From these first round results the client understandably was concerned. The table below gives the highest values. From these values we try and find the sweet spot for what can be expected.

SQLIO Test 2 Configuration

With the results received in Test 1, the Random Write was not acceptable. This second round of tests was put together and undertaken to determine the configuration that would provide the appropriate performance.

• 10 GB Test file
• 50 GB Test file
• 100 GB Test file
• 64 Kb Block size
• 32 Threads with 1, 4, 8, 16, 32 & 64 Outstanding IO requests
• 64Threads with 1, 4, 8, 16, 32 & 64 Outstanding IO requests
• Individual test duration – 180 seconds
• Random Writes

32 Kb block size test result

Random Writes

64 Kb block size test result

Random Writes

With this second test being solely about random writes the results with the different Threads, Outstanding IO requests against the different file sizes, the results are far better than first time round. With analysing the results it looks like the system should be able to maintain up to 3000 IOPS with around 170 MB/sec throughput.

Conclusion

With the testing that was undertaken to benchmark the new storage architecture, working closely with the storage administrator and in contact with the vendor, the client’s new system should be able to achieve far better performance than the previous kit. After the go live I will be running some benchmarking tests against the old system to give a comparison for the upgrade. If you are considering upgrading your storage, you need to know what you are needing to achieve from the performance so you can get all of the appropriate kit to meet those requirements. Do not be rash in your decisions, but work closely with your vendors to determine the appropriate kit for you.  The performance received from the SSD’s is very good but you do pay for that performance. As another option and I am not promoting a product or vendor, but have you heard of FusionIO? This could be a suitable option for you. Brent Ozar has undertaken some testing against this technology and his blog is a good read.

I hope that this has been interesting and an informative read around what you can achieve with your systems and a little bit of testing. All comments are welcome.