Wednesday, June 3, 2015

Buffer Pool Extensions

One cool new feature in SQL Server 2014 is Buffer Pool Extensions, which allow you to use your SSDs like an extension of your system RAM, albeit a tad slower. It's no secret that adding memory to a SQL Server can improve your system's performance. Thankfully, memory prices fall logarithmically decade-over-decade and you can now get RAM for about ten bucks a gigabyte. Not bad! However, what if you've already maxed out your RAM and you still want more? BPE to the rescue!

How Buffer Pool Extensions Work

Buffer pool extensions work by using (fast) non-volatile storage devices, such as Solid State Disks, as an extension of your system RAM. Of course even the fastest SSD is not as fast as the slowest RAM available today, but SQL Server manages BPEs on SSDs in such a way that it's all gain and very little pain. With an available SSD, you can set up a BPE on that device with just a few T-SQL statements.

Where Does BPE Help?

You will likely see good performance gains on OLTP workloads -- particularly if they are of the read-mostly type. You don't have to modify your applications. The feature is transparent to users. There's no risk of data loss, since BPE only works with clean pages (already committed to durable storage). Oh, and if the SSD fails, BPE automatically disables itself, though it can be re-enabled manually should you think it's just a hiccup.