Here are some notes on “SQL Server 2008 I/O Performance” I took while attending an advanced class on SQL Server taught by Greg Low (from http://sqlblog.com/blogs/greg_low/ and http://www.sqldownunder.com/).

Please note that, although these notes were taken during the class, I might have added some of my own (mis)interpretation :-). Always check your facts on Books Online (I try to provide links when applicable). As with anything else you get from a blog, never use any of this in production before you thoroughly validate it a test environment. Please keep in mind that some of those will be hard to follow without some pre-requisite knowledge and the right context. Reading the post from top to bottom will help.


Performance

  • No single right way to do it. All environments are different.
  • In general, for OLAP/DW, bandwidth is more important than spindle count
  • In general, for mixed workloads, IO becomes more random, SAN is more flexible
  • Ensure storage engineers have knowledge of SQL best practices
  • Validate your configuration before deployment

Storage

  • How many/what size LUNs? “It depends” :-)
  • Results vary, not all storage implementations perform the same. Test it
  • Volume alignment – Windows Server 2008 is good at 1MB alignment, older OSes need attention
  • Allocation Unit Size – Use 64KB to align with extent size
  • Careful – Array and driver firmware is important
  • Careful – Use drivers that have been qualified
  • Careful – Discuss ideal settings for HBA/controller with the storage vendor
  • Careful – Hosts usually have multiple PCI buses. Don’t overload just one of them
  • Consider using multiple paths for improved availability
  • iSCSI Support – See http://support.microsoft.com/kb/833770
  • For iSCSI - ensure appropriate bandwidth, latency, paths
  • HBA queue depth discussion

SQL Files

  • Log files: RAID 1+0, isolate log from data at the physical level
  • Tempdb: improvement if place on RAID 1+0 or some RAMSAN style storage
  • In general, do not mix SQL data files with other data
  • In general, multiple LUNs are usually better than a single large LUN
  • In general, for VLDB, multiple large LUNs are OK.
  • Use GPT for LUNs bigger than 2TB
  • See http://support.microsoft.com/kb/302873
  • More files per database does not necessarily equal better performance
  • For tempdb, multiple files could be a good idea to avoid contention on certain structures
  • Filegroups: backup per FG, partial availability, partitioned tables, tables and indexes
  • In general, if possible, keep the primary FG small, put data in other FG
  • Consider read-only filegroups for certain scenarios
  • Monitor growth

SQL Server urban legends

Monitoring SQL I/O Performance

Fragmentation

File Initialization

SQLIO

SQLIOSim

PerfMon + SQL Profiler

  • Demo: Start PerfMon, capture.
  • Demo: Capture a trace with SQL Profile, save. “Import Performance Data…” grayed out.
  • Demo: Load SQL Profile trace, then Use “Import Performance Data…”, compare.
  • Books Online: How to: Correlate a Trace with Windows Performance Log Data (SQL Server Profiler) 
  • See http://msdn.microsoft.com/en-us/library/ms191152.aspx

Related blog posts: