When MS-SQL hits the performance wall, flash storage has become the “go to” problem solver. The high performance and low latency of the technology can eliminate many of the storage performance issues that a MS-SQL environment may face. But IT planners have to be careful about which flash option they should select.
As we discuss in our on demand webinar “Which Flash is Best for MS-SQL” there are essentially three solutions to consider: server-side, hybrid arrays and all-flash arrays. Of these solutions, server-side tends to be the impulse choice. A solid state drive (SSD) or PCIe Flash card in the server combined with the right software caching solution can be the performance quick fix, but IT planners should know before approaching these solutions that there are two key problems to be aware of.
1) – Lack of efficient utilization
A SSD or PCIe Flash card inside of a server can have the same utilization issues that any direct attached storage (DAS) would have. The problem with server flash is that the utilization may look good initially. Most caching solutions will fill up all the available flash storage based on access frequency, so the flash is full, but that does not mean that it is properly utilized. If there are zero cache misses, your cache is probably too big and you’ve wasted money on a premium storage device.
2) – Lack of Safe Write Acceleration
There are two sides of I/O (Input/Output), reads and writes. Read caching, also known as write-through, is a very safe and effective way to accelerate read performance, but it does little to accelerate write performance. Even if they are a smaller percentage of the total I/O mix, writes more heavily burden storage. Even if the percentage of writes is less than 20%, most databases will see a significant performance boost when write caching is enabled.
The problem is when a write is cached to flash; the database is told the write is committed to disk when it is not. The write cache will flush the data to disk, typically within seconds, but there is clearly the chance of a failure, power related or otherwise, causing a dramatic database corruption. As a result, systems that cache writes should provide redundancy and that redundancy should be external to the server. The problem is this now makes server-side caching more expensive and removes its “local” advantage.
There are more problems with server-side flash, especially in a clustered MS-SQL environment, than some might expect. But there are also places where it can make sense. In our webinar, we will discuss the pros and cons of server-side flash, hybrid arrays and all-flash arrays. We will also discuss how and when you should mix these solutions together for the ultimate performance boost.