July 1, 2011 at 3:48 pm
Hi Everyone,
From BOL, the recommended settings for SQL to perform at its best are:
File Allocation Unit (Cluster) Size: 64K
RAID Stripe Size: 64K
Offset: 64K/1024KB(compatible)
I have a 6 disk RAID 10 array.
If I had a stripe size of 128K or 1024k, will I just be wasting storage space? EX: 128 KB stripe unite, 1 write = 2x 64k blocks?
Any performance benefit from using higher numbers for either stripe size or file allocation unit?
Thanks
July 3, 2011 at 5:03 pm
Provided you are formatting the disk with Windows2008 (just guessing, you're using SQL2008 so I hope you're also using Win2008), you'll get an automatic disk partition offset of 1MB. Leave that alone.
If you're using Win2003, then you will need to bust out diskpart and get that offset to 64KB. Terrific that you're paying attention to that. 🙂 For the partition offset, 1024k is fine too, just get it to something at least 64k and divisible by 64k and you're set.
Stripe size: space will not be wasted by using a stripe large than 64k, you're just telling the SAN how to carve up the chunks. In the SQL world, we like to think 64KB all the time (one full extent), but Windows does not see how things are spread across the SAN, it only knows its own formatting (offset + block size). Going with a stripe size wil allow sequential reads/writes of multiple 64KB windows disk blocks, so there is some benefit (demising returns kicks in quick) to going with a lrger strip size, but with a stripe of 64KB, issuing a sequential read of three 64KB blocks will force three parallel reads across three separate disks. with you setup, issuing 20 sequential reads is really a series of parallel but random reads. so, the answer to your question is, "it depends" 🙂
The direct answer is no space will be wasted.
with a 1024KB stripe, there is a potential (big caveat on 'potential') for the writes to perform a single SAN write on multiple blocks, but I wouldn't bet on it. Given then SQL Server makes no attempt to put extents for a single table next to each other, you could just be looking at write contention on a stripe segment.
Now for the decision on stripe size. If you're not going to use a 64KB stripe, any specific choice is dependent on your particular SAN vendor (3PAR vs. NetAPP vs. xxx). I would just default to 'um, cover your tail and stick to 64KB'. Your SAN vendor may have specific guidance for SQL Server, but any vendor advice on SQL Server is "trust, but verify". I've had enough arguments with vendor SEs to take whatever they say with a grain of salt.
I may get taken apart by other folks (who may be far more correct), but once you get the alignment sorted, it's 64k, 64k, 64k. I'm a DBA, so my surly built-in feeling is: stick with published best practices, because your biggest issue is going to be bad code that folks would love to blame on the DBA setting things up "wrong". If you pick a scheme that has loads of documentation behind it, it's far easier to tell them to fix their code, because you have the disks set up "right".
-Eddie
Eddie Wuerch
MCM: SQL
July 4, 2011 at 12:11 am
64kb and 256kb are both optimal recommended stripe sizes for SQL server storage drives. Personally, if using Windows 2008 you do not want to accept the default of 1MB but create and format the partitions manually to implement your required stripe size. Common SQL server reads and writes are in 8kb, 64kb and 256kb sizes. Depends on the activity of the server is it transactional, is it a warehouse, etc, etc.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 4, 2011 at 12:06 pm
Thank you for your responses. Really detailed info you gave me Eddie, just what I was looking for 😉
Came across these benchmarks, seems like the 64KB will produce the best results.
I have heard that a bigger stripe help aid in SQL read-aheads. Any thoughts on this guys? Needed or not?
July 4, 2011 at 2:53 pm
This series of tests by Kendal Van Dyke give a better benchmark. It's worth noting that in most cases there isn't really much of a margin. View them and make your own mind up
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 4, 2011 at 3:22 pm
Yeah thats a good blog indeed. Lots of visuals.
Well guys, I think that does it, thank you both for your help 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply