Sql-server – Cluster Size Configurations for SQL Server at RAID Level

sql servervirtualisation

I was reading the below two articles (one of which I posted) and one recommends to use a RAID cluster stripe size of 256k while the other recommends to use 64k. There is a big difference between the two and was wondering if anyone could shed some light on this.

RAID 10 Cluster Size for SQL Dynamics AX Database

SAN block size for SQL Server on 4KB NTFS

Best Answer

Think of the right answer in terms of the COALESCE command, which returns the first non-null value from a list. The prioritized list of recommendations are:

  1. Your tested and proven settings. Some folks have enough time to do repeated benchmarks of their own applications on their own hardware. That's fairly unusual, though.
  2. Microsoft's Fast Track Reference Architectures. These are very specific documented setups done in partnership with hardware vendors. They include everything from firmware versions to SQL config settings. Unfortunately, they're not available for all combinations of hardware. (Don't worry too much about the SQL Server version - SQL IO hasn't changed much since SQL 2000, with the exception of SQL 2014's new in-memory OLTP.)
  3. Your storage vendor's SQL-specific guidelines. Major storage vendors like Dell, EMC, and HP provide detailed documentation for SQL Server as well as other database platforms. Make sure to get the docs for your exact make/model of storage - different models can have totally different recommendations.
  4. General advice from some bozo on the web. Myself included - my work was quoted in one of the other answers. General advice just doesn't hold true for all combinations of storage hardware. This is a last resort.
Related Question