Sql-server – SQL Server 2K/2K5/2K8 and Solid State Disks: Specific Optimizations

sql serverssd

Is anybody here running SQL Server on solid-state drives? Have you found any specific optimization tips? I'm specifically interested in ways to reduce the frequency with which SQL Server performs small random write operations since they're the nemesis of SSD performance, particularly MLC SSD drives.

There are some obvious optimizations one can do, of course: read-heavy data should be served from the SSD, and write-heavy stuff should be left to traditional spinning disks. That includes transaction logs, naturally!

Given enough budget, of course, one would want to use SLC SSD disks like the X25-E or the Vertex Ex series or various enterprise-level offerings. But I'm also interested in tips that might benefit MLC SSD setups. I think that's an interesting area. One of my clients' clients has a small budget and a dataset that's grown immensely and they're facing a complete rewrite of close to a hundred queries in order to maintain a decent level of performance. However, I have a sneaking suspicion that less than $500 of RAM and SSD space might net them a bigger performance gain than thousands (possibly tens of thousands) of dollars worth of developer time.

Best Answer

Not sure what you mean by reducing the amount of small, random writes that SQL Server does. SQL Server writes out data pages only during checkpoints - so the only way to limit the number of writes is to change the checkpoint interval or don't so many IUD operations. Did you mean something else?

In all the implementations of SSDs that I've seen (a handful), it's kind of the opposite of what you're suggesting - the best use of SSDs seems to be for write-heavy transaction logs and tempdb - basically where's the biggest I/O subsystem bottleneck and stick the SSDs in there - as seek time and latency are reduced to a low constant.

Checkout this research paper that MS produced (unfortunately not hugely detailed on SQL Server specifics): Migrating Server Storage to SSDs: Analysis of Tradeoffs.

Hope this helps!

Related Topic