I am planning to migrate my tables to memory optimized tables. Currently, I have a total of 250 gigabytes database size on disk. If I migrate this to in memory tables, how much physical RAM will I need?
Add a comment
|
2 Answers
Start with the Books Online page on estimating memory requirements for in-memory OLTP. It goes into deep detail, and here's just a snippet:
When there is an active workload, additional memory is needed to account for row versioning and various operations. How much memory is needed in practice depends on the workload, but to be safe the recommendation is to start with two times the expected size of memory-optimized tables and indexes, and observe what are the memory requirements in practice. The overhead for row versioning always depends on the characteristics of the workload - especially long-running transactions increase the overhead. For most workloads using larger databases (e.g., >100GB), overhead tends to be limited (25% or less).
Keep in mind that that's only for the in-memory OLTP data - not your query workspace, buffer pool for other objects, AG log caching, etc.
answered Oct 3, 2017 at 14:06
The answer (of course) is "it depends". Part of what it depends on is: 1. what other features SQL Server you might consider using. For instance, using the temporal feature with memory-optimized tables requires addtional memory, etc. 2. if you will be adding additional indexes (SQL 2017 now supports hundreds of indexes per memory-optimized table). A singe nonclustered index on a memory-optimized table does not support both ascending and descending queries, so you might have to add more indexes than are required for your on-disk workload. 3. if your current data/indexes use any form of compression, i.e. row/page/columnstore
Also, have you done a POC that proves your workload will benefit from migrating to memory-optimized tables?
