As a SQL Server enthusiast, I am always intrigued by the questions asked by my readers. Recently, a reader asked me a question that led me to explore more about SQL Server 2014’s new features. In this blog post, I will discuss the question and provide a detailed explanation.

The reader, who had watched my Pluralsight course on SQL Server 2014, had a follow-up question regarding the In-Memory OLTP (Hekaton) and Buffer Pool Extension (BPE) features. They wanted to know if it was possible to use a buffer pool extension and live with 5 GB of RAM and a 50 GB BPE file, while having an In-Memory table of 10 GB in size.

Before diving into the answer, let’s briefly review the basics of these features. In-Memory OLTP stores data in memory without using traditional pages. Instead, it uses free-flowing rows tied to each other via an index. On the other hand, the Buffer Pool is an area where SQL Server keeps every page and query plan. The Buffer Pool Extension feature allows for extending the buffer pool.

Now, let’s tie all these points together to answer the reader’s question. The Buffer Pool Extension cannot be used to hold data belonging to In-Memory tables. In-Memory tables are not part of the Buffer Pool, and therefore, we cannot extend the data from those tables to the extension file.

To illustrate this concept, refer to the image below:

As you can see, if the size of the In-Memory table grows, it cannot be accommodated in the Buffer Pool Extension. The In-Memory tables are separate entities and cannot be extended to the extension file.

I hope this explanation clarifies the reader’s question and helps you understand the relationship between In-Memory OLTP, Buffer Pool, and Buffer Pool Extension in SQL Server 2014.

Stay tuned for more informative blog posts on SQL Server concepts and features!