SQLWebpedia

I am currently working on building out a new SAN (older HP EVA 8000) for new Windows 2008 and SQL 2008 migration from SQL 2005. There is so much you can tweak that I wanted to put all my thoughts in one location, so here is my brain dump which will be updated as time goes on. Listed below in order of importance.

1. Stay away from Shared SAN's unless dedicated to your DB servers only! We have used two of the latest shared SAN companies products, Isilon and 3PAR, and without a doubt you should not use them for high-end database storage. They should work fine for medium to low usage as long as there are no big data loads or other high consumers using it at same time.
2. Disk partition alignment - On pre-windows 2008 servers this is needed Win 2008 does this for you. Expect 20-30% performance gains if you re-align disks (does require reformat of disk). Denny Cherry has a great writeup of this, basically due to disk alignment from formating disk you could require 2 physical operations per one logical operation if aligned improperly. More info on checking alignment. A SAN introduction with thoughts on mixing/matching drive types (fast/slow) on your systems to minimize cost which I very much agree with.
3. Format disk cluster (allocation unit) size on physical disk to 64K (default is 4K for all server OS versions). The physical disks like to do all their operations in 64-block chunks called clusters. Conveniently, SQL Server likes to do all its operations in 64 K operations, there are eight 8K blocks in each extent, and SQL does its reads one extent at a time. Note: If you like using the disk compression option for read-only filegroups, you have to stick with 4K clusters.
   
4. Use RAID 10 for Data, Log, Tempdb. If you can't afford all that and you have good caching performace on your SAN Raid 5 for data files is fine as the cache can alleviate the performance loss of write activities on Raid 5. More info on SQLCAT website.
5. Change Read/Write caching on SAN LUN to be 0/100, 10/90, or 20/80 (read/write %) as SQL server does its own read ahead caching already and the SAN has no clue about SQL storage.
6. Tune HBA Queue Depth, typically 8-32 by default. Talk with storage vendor and your SAN engineer to see what you can increase it to.
7. If you are putting multiple log files on a single LUN, tune it to be a random IO pattern, not seqential, as sequential would only work for one log file on the LUN (and who has enough money for this?).

Good articles to read:

• Predeployment I/O Best Practices - SQLCAT/Technet Article.
• Disk Partition Alignment Best Practices for SQL Server - SQLCAT Whitepaper.
• http://kendalvandyke.blogspot.com/2009/02/disk-performance-hands-on-series.html
• SQLCAT team blog series on SQL 2005 and SANs: http://blogs.msdn.com/sqlcat/archive/2005/10/11/479887.aspx http://blogs.msdn.com/sqlcat/archive/2005/11/21/495440.aspx http://blogs.msdn.com/sqlcat/archive/2005/11/17/493944.aspx
• http://sqlcat.com/whitepapers/archive/2009/05/11/disk-partition-alignment-best-practices-for-sql-server.aspx
• http://blogs.msdn.com/jimmymay/archive/2008/10/14/disk-partition-alignment-for-sql-server-slide-deck.aspx
  http://blogs.msdn.com/jimmymay/archive/2009/05/08/disk-partition-alignment-sector-alignment-make-the-case-with-this-template.aspx
• SQL Partition Checking KB929491
• SQLIO Basics (hasn't changed since SQL 2000 so still applicable to SQL 2005/2008.
  
• http://en.wikipedia.org/wiki/Standard_RAID_levels