Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Benchmark SQL Server IO after partition offset and allocation unit changes


By:   |   Read Comments (2)   |   Related Tips: More > Hardware

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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.

Outstanding I/O s

Before After
IOs per Second MB per second IOs per Second MB per second
1 110.06 6.87 174.61 10.91
2 107.24 6.70 171.35 10.70
4 112.67 7.04 159.78 9.98
8 107.36 6.71 175.51 10.96

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:

Outstanding I/O s

Before After
IOs per Second MB per second IOs per Second MB per second
1 100.83 6.30 106.46 6.65
2 149.90 9.36 196.32 12.27
4 204.07 12.75 304.95 19.05
8 261.24 16.32 427.47 26.71
16 315.76 19.73 541.08 33.81
32 366.78 22.92 639.01 39.93
64 420.82 26.30 734.55 45.90
128 453.46 28.34 806.84 50.42
256 486.76 30.42 867.51 54.21
512 488.14 30.50 863.95 53.99
1024 488.68 20.54 857.57 53.59

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.

Outstanding I/O s

Before After
IOs per Second MB per second IOs per Second MB per second
1 3646 227 2702 168
2 5497 343 5006 312
4 5469 341 5492 343
8 5550 346 5502 343
16 5503 343 5554 347
32 5577 348 5566 347
64 5574 348 5620 351
128 5637 352 5641 352
256 5611 350 5669 354
512 5673 354 5670 354
1024 5629 351 5710 356

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:

Outstanding I/O s

Before After
IOs per Second MB per second IOs per Second MB per second
1 11,510 90 11,676 91
2 14,888 116 15,488 121
4 15,030 117 15,441 120
8 14,911 116 15,398 120
16 15,006 117 15,275 120
32 14,931 116 15,459 119
64 15,062 117 15,550 121

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


Last Update:


signup button

next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, November 02, 2010 - 12:03:51 PM - Andy Novick Back To Top

Although SQLIOSim has it's uses, benchmarking disks isn't one of them.  It's a tool to test the robustness of SQL server I/O.

The Microsoft CSS (formersly PSS) team blogs about it here:

http://blogs.msdn.com/b/psssql/archive/2008/04/05/sqliosim-is-not-an-i-o-performance-tuning-tool.aspx

There are other tools, such as IOMeter that can benchmark performance if you like.

 

Andy

 


Tuesday, November 02, 2010 - 10:54:55 AM - Rudy Komacsar Back To Top

 

 

 

SQLIO has been superseded by SQLIOSIM -

 

 

http://support.microsoft.com/kb/231619/en-us

This is a much more comprehensive tool although documentation is lacking … below is what doc I could find:

 

 

 

http://sqlblog.com/blogs/kevin_kline/archive/2007/06/28/understanding-sqliosim-output.aspx

 

 

 

http://www.sqlcrunch.com/Storage/tabid/85/Default.aspx


Learn more about SQL Server tools