Have you ever noticed when you are working on a query for SQL Server and the second time you execute the query it runs much faster than the first time you had executed it? There is a reason for this and most likely it is caused by your buffer.
When your SQL Server starts there are a few things that happen in regards to the memory. One of these is SQL Server is going to start to claim memory space from the Operating System for it to use. Inside this memory space that is has captured SQL Server then allocates space for a buffer.
A SQL Server buffer is a space that SQL Server uses to store data pages based on data that has been accessed recently. As data is retrieved from disk the data is placed in the buffer. This is done because reading from memory is much faster than reading from storage. 15 years ago when the storage was much slower than it is now, this was a critical part of making SQL Server faster. And with such a wide gap in the performance differences between storage and memory many DBA’s found performance benefits in adding more memory to the SQL Server as a way that they could keep more pages in memory for faster access.
Fast forward to 2014 and look into storage performance and you will see so many different changes you may not be thinking you are looking at the same thing. The use of SAN technology is common with small companies and the storage spindle speed is faster than it has ever been before. We have seen the introduction to SSD (solid state drives) that removes all the moving parts in the storage. These changes have made a big difference in the performance we can now get out of our SQL Server installs.
SQL Server 2014 takes advantage of these new storage options by giving us the ability to use a buffer extension file. Think of this as a page file that we all know and love however this is for you database, and is intended to reside on the new faster storage we have access to. A Buffer Extension creates that middle level between storage and memory, and works well when you see your SQL Server needs a little extra memory and yet you have already maxed out your memory in your hardware.