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
- Eliminate bottlenecks:
- 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.