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.
The blog reader asked me below question:
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?
This is indeed an interesting question. If we go back to basics, we need to keep below in mind.
- In-Memory OLTP doesn’t uses Pages. It just has free flowing rows in memory, tied to each other via index.
- Buffer Pool is the area where SQL Server keeps every page, query plan.
- 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)