Using SSD Drives for SQL Server

I’ve  just watched a great video explaining How to Optimally Use SQL Server with SSD’s Without Burning Them Out.

Here’s some notes:

  • 1 x SSD can give 4-5x the IOPS a 15k HDD gives. (I thought it would be more than this. Maybe because the video is from SQL Server Days 2011, and is a little out of date.)
  • The file allocation unit size (cluster size) recommended for SQL Server is 64 KB; this changes things when comparing IOPS stats from SAN. Ask for “Steady State IOPS”: http://calypsotesters.com/up-to-sustained-steady-state-ssd-performance
  • 4KB pages are standard on SSD
    • Smallest read/write structure.
  • Pages are grouped into blocks (128).
    • 512KB per block.
    • Smallest erasable structure
  • SSD write performance will degrade over time as it gets full.
  • SSD read performance stays roughly the same.
  • TRIM will help slow this down:
    • Check your OS supports TRIM by running this command
    • fsutil behavior query DisableDeleteNotify (should return 0)
    • If not 0, try to set it with this command:
    • fsutil behavior set DisableDeleteNotify 0
  • DO NOT defrag a SSD.
  • SSD typically don’t handle random writes very well.
  • Always use Enterprise SSDs, as they can last 10x longer than Prosumer SSDs.
  • Useful SMART tool is SSD Toolbox: www.intel.com/support/go/ssdtoolbox/index.htm
  • DO NOT use SSDs for TempDB, and maybe not even Log.
  • Base Solution – Tiered Infrastructure:
    • Eliminate bottlenecks:
      • Think 6Gbps and higher
      • Multilink
    • Use good controllers with Wear Levelling
    • Look at IOPS and response rates in Steady State
  • Problems with TempDB:
    • TempDB involves heavy Random writes.
    • Most used database
    • Enormous amounts of reads and erases
    • Kill (standard) SSDs extremely fast!
  • Log Files
    • Consider using Enterprise SSDs only, with RAID1 (mirrored).
    • Be careful with autogrowth.
    • Log files have more sequential reads and writes; more suited to HDDs.
  • Indexes
    • This is the best candidate for using SSD.
    • Place index filegroup on SSD.
    • Rebuild options that matter for SSDs:
      • SORT_IN_TEMPDB
      • MAXDOP=1 (this keep writes sequential)
      • COMPRESSION
  • Problem drive order of failure:
    • TempDB
    • Log
    • Data
  • Data Writes:
    • Logically view this as log entries being written and then used as instructions to write data pages out to the disk by an asynchronous process.
    • Heavily random access pattern; great for SSD.
  • Conclusion:
    • SSDs are not so good for sequential writes
    • SSDs are great for Random reads; indexes and Data files.
    • IO Volume matters, not just IOPS.