I caught this piece on the impact of flash storage on database engine design this week. It's interesting as there's been debate for years as to whether SQL Server should alter its behavior if it detects SSDs being used for storage instead of physical disks. It doesn't, and perhaps that's fine, though the article makes me think there are performance gains to be had if behavior changes.

The article really looks at a few of the NoSQL products, though the design changes aren't necessarily specifically limited to those products. I particularly thought that two ideas in the piece were interesting: indexes in memory with data on disk and the realization that threading can be he bottleneck with SSDs. I'm not sure if Windows and/or SQL Server could use these ideas, but they are interesting.

I do wonder sometimes if a little more control of indexes would be helpful in SQL Server. Imagine if I could limit a large slice of memory to strictly non-clustered indexes and then have other data on SSDs. Would there be a way to tune SQL Server to run better for some workloads? Perhaps the algorithsm that choose query plans would change if they knew a scan of an NCI could be completed in a fraction of the time that a seek took place on a CI? Maybe we'd be willing to perform more seeks on in-memory indexes before performing lookups on disk.

The idea of more concurrent operations, requiring more threads, also seems to be an area where I could suspect that both Windows Server and SQL Server could benefit from SSDs. If the systems changed their read and write algorithms and used many more threads with SSDs, could we get more throughput? Should our systems be more aware of how many controllers and paths might be on a system? I wonder, especially as some of this hardware becomes cheaper and cheaper. I could certainly see more organizations looking at using lots of smaller SSDs for a few servers that require high performance than a SAN.

However it's not as though SQL Server isn't trying to take advantage of technology changes. The In-Memory OLTP system  and Buffer Pool Extensions in SQL Server 2014 are designed to take advantage of more memory and SSDs to dramatically improve performance. I don't know what else might be coming in the next version of SQL Server, but I do hope that as new ideas emerge, SQL Server considers taking advantage of them.