SQL SERVER – Using Buffer Pool Extension with In-Memory OLTP?

I am always amazed to see the questions asked by my blog reader which lead me to learn more about SQL Server.  Here is the question which took me little time to figure out the answer.

If you are new to SQL Server 2014 new features which I have in subject, I would recommend you to go through below blogs first.

SQL SERVER – Beginning In-Memory OLTP with Sample Example
Buffer Pool Extension in SQL Server 2014 Pluralsight Course

The blog reader asked me below question:

Hi Pinal,
I watched your Pluralsight course and was able to easily learned about two new features, In-Memory OLTP (Hekaton) and Buffer Pool Extension (BPE). Thanks for making such course. I have some follow-up question and I hope to get answer from you.

My In-Memory table would be 10 GB in size. As per your course, I would need at least 10 GB of RAM so that complete table can fit in memory. Can I use buffer pool extension and live with 5 GB RAM and 50 GB BPE file?

Regards,
<Name Hidden>

This is indeed an interesting question. If we go back to basics, we need to keep below in mind.

1. In-Memory OLTP doesn’t uses Pages. It just has free flowing rows in memory, tied to each other via index.
2. Buffer Pool is the area where SQL Server keeps every page, query plan.
3. Buffer Pool Extension feature is to extend the buffer pool.

If we tie all three points together, we can easily conclude that Buffer Pool Extension cannot be used to hold data belonging to In-Memory tables.

Here is the quick image which can show what would happen if In-Memory table size grow

Hope this would help you in remembering that In-Memory Tables are not part of Buffer Pool and hence we can’t extended the data from those tables to extension file.

Reference: Pinal Dave (https://blog.sqlauthority.com)