1

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?

Improve this question
1

1 Answer 1

Reset to default
2

SQL Server 2016 memory-optimized tables require that sufficient memory exist to keep all of the rows and indexes in memory.

The size of a memory-optimized table corresponds to the size of data plus some overhead for row headers. When migrating a disk-based table to memory-optimized, the size of the memory-optimized table will roughly correspond to the size of the clustered index or heap of the original disk-based table.

You wouldn't normally create all your database tables as memory-optimised tables, only those with high throughput/insertion OLTP workloads.

So before you go converting all your tables to memory-optimised tables, first identify those with high activity, convert and then measure any performance increase (and monitor RAM usage).

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Not only that, you're unlikely to see any performance improvements without using associated memory optimised stored procedures
Hi @Nick; I think you will see a perf increase for non-durable tables without using associated memory optimised stored procedures as there will be no TLog activity.
That's what I thought too, but in my brief experience, there wasn't any difference. It certainly wasn't a scientific investigation though. Perhaps the TLog wasn't a bottleneck in my case.
>>>you're unlikely to see any performance improvements without using associated memory optimised stored procedures<<< That is not true. Even without natively compiled sp in highLY loaded OLTP there is performance gain on insert due to lack of LOCKS
Interesting - I did some brief reading. My experience is only with bulk data loads against non durable tables rather than highly contentious tables so I guess you can expect to see more of a difference there.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.