In SQL Server 2016, if we create a database in RAM (i.e using memory optimized tables), the database size should not exceed RAM size, am I right in saying that?
Database size can exceed the RAM capacity as it could contain normal disk based tables as well as In-Memory tables. However, the size of memory based tables should not exceed the available memory. This available memory could be less than the RAM capacity as there are other processes that will be consuming some amount of RAM.
List of various out of memory issues that could arise:
The memory required for In-Memory tables is not just the table size but it should also include the following:
- row versioning
- table variables
Estimating memory requirements for memory optimized tables:
The memory allocated for In-Memory OLTP comes from the default pool, or a user defined pool, if you are running enterprise, and have configured the pool. In both cases, the sql engine will give an out of memory error if you either hit the max for the user defined pool, or arrive at a certain percentage of the default pool, determined by how much memory is available to sql server, and other factors.