For those of you who run SQL Server on cloud VMs (AWS or Azure), here's an awesome performance hack I have recently discovered. It's called "buffer pool extensions" and it's been introduced in SQL Server 2014 and later ("standard" edition or higher).
If your Amazon EC2 instance (or Azure VM) has a local SSD drive, run this command:
ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON (FILENAME = 'Z:\MyFastSSDCache.BPE', SIZE = 30 GB);
Z: is the SSD drive.
You get upto 170% faster i/o performance, cause SQL Server will use it as an extra cache layer between RAM and HDD.
First, let's see, how SQL Server works with data. A very, very simplified version.
Now, you can set the SQL server to purge those "clean" pages from memory, but still keep 'em in a "faster than HDD" storage. Something faster than a magnetic hard-drive, but slower than RAM. Bingo - an SSD drive.
Microsoft has introduced this feature back in 2013, when SSD drives were not that reliable, but offered amazing I/O performance. We all needed a way to benefit from that super-fast I/O but still keep the integrity of our data if that I/O storage suddenly goes away, crashes or gets corrupted in some way.
You might ask - how is that relevant today, when SSD drives became cheaper, faster and way more reliable?
Amazon's EBS drives are super slow, even compared to a 10-year old magnetic hard-drive. Because it is, essentially, network storage. That's the price you pay for availability - the data is kept safe even if the server literally burns down. AWS has been trying to solve this problem for a while now: by introducing "provisioned I/O" volumes, "burstable" disks, advising to combine slow drives into RAID-arrays etc.
But here's the thing. Most of AWS "instances" (or Azure VMs) come with a local "ephemeral" storage. Which, of course, is wiped every time you stop/start the server (because the server launches on a different physical machine). But this disk is an ideal candidate for "buffer pool extensions".
Just make sure you grant read/write permissions to your MSSQLSERVER user-account on that SSD and run the self-explanatory command from the first paragraph.
Oh, and don't forget to read my epic blog post on what else can be moved onto an ephemeral drive. I keep updating it with new info every month.
MS recommends the size of this buffer pool extension to be from 1:4 to 1:8 of the server's RAM. If you have enough space, set it to your database size.
Enabling this feature on a running production server is fine. But once enabled - avoid making changes in production. Reconfiguring the size or turning the feature on/off will severely impact performance for a couple a minutes.
Remember, that data pages are not the only thing SQL Server uses RAM for. For example, cached execution plans will still stay in memory. Which is why buffer pool extension is NOT a replacement of adding more RAM.
This feature will be most effective in "write heavy" workload. It won't speed things up in a reporting/data analysis environment with terrabyte-scaled storage.