Benchmark SQL Server IO after partition offset and allocation unit changes

By: Andy Novick   |   Comments (2)   |   Related: > Hardware

Problem

In previous tips we discussed how to improve disk I/O performance. In the last tip, we looked at changing the partition offset and allocation unit size (a.k.a cluster size) of a drive. Once that has been changed, how can you tell if performance is improved?

Solution

In a previous article, How to benchmark a drive with SQLIO, I benchmarked my L: drive, a RAID 5 set of 4 SATA disks, with SQLIO. In another article, How to create and format a drive for SQL Server, I re-partitioned and reformatted the drive in the hope of improving its performance. In this article, I'll re-run the benchmarks to see if performance improved. I don't expect spectacular changes, but even a 10 percent improvement would be helpful.

SQLIO is a free disk benchmarking tool from Microsoft, which can be downloaded from here. I've used it for benchmarking for several years, because it provides consistent results. Use it without SQL Server running, possibly before SQL Server is even installed. It produces a variety of results, the most important of which are the I/O operations per second and the Megabytes of data per second. Because the buffer size that's read or written is set as a parameter these numbers are directly related.

To test the L: drive, I use a series of tests in command files that were described in the previous article. The number of I/O requests that are outstanding at any one time is the product of two switches: -t for threads and -o for outstanding requests. The tests start with one outstanding request per thread and increase the number of threads used until there is one per core in the server and then increase the number of outstanding requests per thread after that. Each line doubles the number of outstanding requests from the previous line. What I'm looking for is the maximum number of any particular operation that the drive can provide.

Random Writes

There are only 4 tests for random writes, because they quickly saturate the drives. Random writes are used by the Lazy Writer and by Checkpoints to save pages to disk. Here they are:

sqlio -dL -BH -kW -frandom -t1 -o1 -s90 -b64 testfile.dat  sqlio -dL -BH -kW -frandom -t2 -o1 -s90 -b64 testfile.dat  sqlio -dL -BH -kW -frandom -t4 -o1 -s90 -b64 testfile.dat  sqlio -dL -BH -kW -frandom -t8 -o1 -s90 -b64 testfile.dat

I've extracted the IOs per second and megabytes per second numbers into the table that follows and it shows the results for both the Before the reconfiguration and After the reconfiguration.

Wow! that's a nice increase from 110 random writes per second to around 170 random writes per second. An increase of 50%. Cost: nothing but time and attention.

Random Reads

The next set of tests is for random reads. SQL Server does random reads when doing bookmark lookups or when reading from fragmented tables. Here are the tests that I ran:

sqlio -dL -BH -kR -frandom -t1 -o1 -s90 -b64 testfile.dat  sqlio -dL -BH -kR -frandom -t2 -o1 -s90 -b64 testfile.dat  sqlio -dL -BH -kR -frandom -t4 -o1 -s90 -b64 testfile.dat  sqlio -dL -BH -kR -frandom -t8 -o1 -s90 -b64 testfile.dat  sqlio -dL -BH -kR -frandom -t8 -o2 -s90 -b64 testfile.dat  sqlio -dL -BH -kR -frandom -t8 -o4 -s90 -b64 testfile.dat  sqlio -dL -BH -kR -frandom -t8 -o8 -s90 -b64 testfile.dat  sqlio -dL -BH -kR -frandom -t8 -o16 -s90 -b64 testfile.dat  sqlio -dL -BH -kR -frandom -t8 -o32 -s90 -b64 testfile.dat  sqlio -dL -BH -kR -frandom -t8 -o64 -s90 -b64 testfile.dat  sqlio -dL -BH -kR -frandom -t8 -o128 -s90 -b64 testfile.dat

Here are the results extracted to a table:

Before the reconfiguration the L: drive plateaued at around 488 random reads per second. After the change the plateau jumps to around 860. An increase of 75%. Another Wow!

Sequential Reads

The tests for sequential reads consists of these tests:

sqlio -dL -BH -kR -fsequential -t1 -o1 -s90 -b64 testfile.dat  sqlio -dL -BH -kR -fsequential -t2 -o1 -s90 -b64 testfile.dat  sqlio -dL -BH -kR -fsequential -t4 -o1 -s90 -b64 testfile.dat  sqlio -dL -BH -kR -fsequential -t8 -o1 -s90 -b64 testfile.dat  sqlio -dL -BH -kR -fsequential -t8 -o2 -s90 -b64 testfile.dat  sqlio -dL -BH -kR -fsequential -t8 -o4 -s90 -b64 testfile.dat  sqlio -dL -BH -kR -fsequential -t8 -o8 -s90 -b64 testfile.dat  sqlio -dL -BH -kR -fsequential -t8 -o16 -s90 -b64 testfile.dat  sqlio -dL -BH -kR -fsequential -t8 -o32 -s90 -b64 testfile.dat  sqlio -dL -BH -kR -fsequential -t8 -o64 -s90 -b64 testfile.dat  sqlio -dL -BH -kR -fsequential -t8 -o128 -s90 -b64 testfile.dat

Here are the results.

It looks like the plateau is the same both before and after the reconfiguration. I'm a little puzzled by the number for 1 thread being lower than it had been before the reconfiguration. I ran the test again a few times and got substantially the same results.

There are a variety of times when SQL Server is able to write sequentially. One of them is when it writes to the transaction log file. If there is only one log file on the drive, all writes are to the end of the file and the disk heads don't have to move. I've changed the parameters to use 8K blocks to be closer to how log files are used.

sqlio -dL -BH -kW -fsequential -t1 -o1 -s90 -b8 testfile.dat  sqlio -dL -BH -kW -fsequential -t2 -o1 -s90 -b8 testfile.dat  sqlio -dL -BH -kW -fsequential -t4 -o1 -s90 -b8 testfile.dat  sqlio -dL -BH -kW -fsequential -t8 -o1 -s90 -b8 testfile.dat  sqlio -dL -BH -kW -fsequential -t8 -o2 -s90 -b8 testfile.dat  sqlio -dL -BH -kW -fsequential -t8 -o4 -s90 -b8 testfile.dat  sqlio -dL -BH -kW -fsequential -t8 -o8 -s90 -b8 testfile.dat  sqlio -dL -BH -kW -fsequential -t8 -o16 -s90 -b8 testfile.dat  sqlio -dL -BH -kW -fsequential -t8 -o32 -s90 -b8 testfile.dat  sqlio -dL -BH -kW -fsequential -t8 -o64 -s90 -b8 testfile.dat  sqlio -dL -BH -kW -fsequential -t8 -o128 -s90 -b8 testfile.dat

Here are the results:

The numbers are up just slightly from the results before the reconfiguration the plateau happens soon, but at 3-7 percent higher number of operations.. These are 8K writes, so they're smaller and might benefit from a smaller allocation unit size.

Summary

The disk reconfiguration was worth while after all. The benefits seem to come in random reads and writes and they're substantial, with increases of 50 and 75 percent, just for getting the partition offset and allocation unit size correct.

Next Steps

• Run SQLIO tests on your drives as a benchmark to have on hand
• Consider smaller allocation unit sizes
• Read all of the tips in this series:
  • Benchmarking SQL Server IO with SQLIO
  • Format drives with correct allocation and offset for maximum SQL Server performance
  • Partition offset and allocation unit size of a disk for SQL Server

About the author

Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips