It was brought to my attention that our (Hitachi) SAN handles data in 42MB blocks. It is tiered storage so each block is evaluated when the director makes decisions as to when it should promote/demote storage to faster/slower storage. In the absence of having the political weight to "pin" particular LUNs to particular tiers I am trying to do all I can to set up my SQL Server (2008 R2 Enterprise) instance for success.
As such, I was wondering if there would be any benefit/detriment to changing my file sizes and file growth sizes to multiples of 42? An example database would be SIZE=57886MB with a FILEGROWTH=5124MB.
I see the potential for extra reads (since (42*1024)/8 = 8601.6, but (42*1024)/64 = 672) but I'm not sure I'm fully grasping all of the concepts here.
Best Answer
The 42MB unit (Hitachi calls this a page too, how convinient). is the basic allocation unit at which space is allocated to a LUN. So with Dynamic provisioning, a LUN will always grow in 42MB steps. These, Hitachi 42MB pages are taken from different Array groups with in a pool.
So whenever more space is needed for the LUN a new 42MB Hitachi page is allocated. When you write your first 8KB SQL Data page to a file, a 42MB Hitachi page will be allocated. Subsequent writes of SQL data pages go to this 42MB area, until it's full.
Now, what is important to understand is that a 42MB page goes to one parity raid group. If you have small RAID groups, for example : RAID-1+0: 2D+2P (2+2) (4 disks) and your data access is mostly sequential, this could be a problem when you have one file.
Lets say you have 4 RAID10 groups each having 4 disks. You just happen to have only 1 important table with 1 clustered index that is 168MB in size.
When you start inserting into this table, the first 42MB alloc page will be placed on one of the RAID groups, once this 42MB is full, the second alloc page will be allocated on another RAID group, most likely all 4 42MB alloc page will be devided across the 4 RAID GROUPS. (we are excluding tiering for simplicity at this moment).
What's important to understand is that anything you do sequentially in the order of the clustered key, will only have 4 disks at any given moment to handle the IO. (2 disks if you are writing..) even though the whole table is spread across 16 disks..
If you would want to be using all 16 disks. you would have to create 4 files (on 4 seperate LUNS) in a filegroup and create the table on that. At least you now know that SQL is "striping the data across 4 42 MB alloc pages"
This can be most problematic with the transaction log file. It's only 1 file and sequential. The only "tuning" you can do here is make the RAID groups used in that pool as big as possible. Contradictionary a concatinated array group RAID5 14+2 OR RAID5 28+4 might be the fastest even though it's RAID5 as opposed to RAID 10, since as far as I know the biggest RAID group in RAID 10 that can be created is 4+4 is 8 disks.
Please be aware this becomes far less important if you are running a server with multiple databases with multiple LUNS all sharing the same pool, or even more generic multiple servers all having LUNS in the same pool. SInce obviously the general load would then be evenly distributed across all the raid groups.
Regarding tiering, I wouldn't be to worried about it from a performance objective. (if you are not allowed to pin..) Heavy 42MB alloc pages are moved to a high performance tier, so you could have the situation where one table is spread across 3 different tiers. Apperently you have parts of your table that are IO intensive and parts that are not...
worry about the combination small raid groups AND single files (actually single LUNs) AND (high incremental inserts OR heavy table localized updates/deletes OR heavy seqeuential reads)
hope this helps a bit..
further reading:
Hitachi Tiering design guide
Hitachi SQL Server best practise
Blog post about 42 alloc unit