April 4, 2002 at 7:09 am
I am setting up a raid 1+0 array for a SQL server machine, the database is going to be doing some write intensive overnight batch processing. I was wondering what the best stripe size to use would be.
The default size on my card is 64k but my hunch is that a smaller size would offer better performance.
Any help much appreciated
Regards
P
April 4, 2002 at 7:56 am
I think 64k is a pretty good place to start since extents are 64k.
Andy
April 4, 2002 at 1:31 pm
If you decide to test keep in mind SQL stores data in 8k pages so it is best if you keep the value a multiple of 8.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 4, 2002 at 6:14 pm
There is a white paper on performance tuning here:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
I think there will be a variety of factors. It seems that the write size of Windows will have something to do here as well.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 4, 2002 at 11:56 pm
If you are using a separate RAID Array then the strip interleave is an impotant factor in determining the I/O performance. In general, most sites I have worked on have seen good performance with a 32kb or 64kb interleave. However it is important to note that the interleave can drive performance based on the following factors: the specific database read/write block size, the number of devices (partitions) in the stripe, and the DBMS and server workloads. For example, a heavily hit OLTP system should use a different interleave size than a pure DSS (read only) environment.
For example...let's suppose you have a DB blocking factor of say 4KB and that a striped device is made up of three disks. That means, in order to have full I/O parallelism (in this case three-way parallelism), a read or write request would have to be batched at a size of (stripe size) x (number of members in the stripe). Or in this case, (32KB) x (3) = 96KB. This means that SQL Server would have to read and write 24 data blocks (conceptually) in order to achieve full parallelism. If the workload were such that small number of rows were being requested, the read requests would consist of small batch sizes. This would not, in such a case, make full use of the stripe itself.
I hope this helps!
"Doing easily what others find difficult is talent; doing what is impossible for talent is genius." -
Henri-Frederic Amiel
"Real knowledge is to know the extent of one's ignorance." - Confucius
April 5, 2002 at 2:49 am
Thanks for the info.
I am going to start with a 64k stripe size do some benchmarking and then reduce the stripe size to see if there is any performance benefit.
Cheers
P
July 8, 2010 at 9:05 am
How'd this go? What did you end up using and are you happy with it?
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply