Sql-server – Logical Drives – I/O aside, does SQL Server care

Conventional wisdom about RAID doesn't apply well to SSDs. They don't really need striping (RAID0). They are prone to failures by-design, but RAID-1 is usually not the right answer for SSD for two reasons: a) is wasteful, halves the capacity of the SSD array (and they are pricey) and 2) SSDs failure characteristics leads towards both drives in the mirror to fail at very close intervals (ie. correlated failures) and thus render the entire array useless. See Differential RAID: Rethinking RAID for SSD Reliability for a lengthier discussion. Some have recommended using Raid-6 for SSDs.

Additionally, the conventional wisdom of SQL Server file layout doesn't apply to SSDs. I would recommend you watch SQL on SSDs: Hot and Crazy Love and go over the benchmark links in this 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