question

gotqn avatar image
gotqn asked

OLTP table requires too much RAM

I have try to check the performances improvement that Hekaton engine brings converting a regular table to OLTP one. The regular table is 9 GB, but the Table Memory Optimization Adviser says it will need 553 218 MB RAM memory (that's half of TB ram). Is a such increase normal, or there something wrong with the adviser? If so, how can I calculated with T-SQL the amount of RAM memory needed?
memorymemory-utilizationoltpsql memory consumption
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
The average metric is 3-5 times the amount of memory will be needed that the table actually occupies. It largely depends on how many versions of the rows will have to be maintained during your load processing. The majority of the performance enhancements you'll see will be if you can also use natively compiled procedures. If you're just using in-memory tables and mixing them with standard tables and standard procedures, you'll still see a performance benefit, but it might not be as radical as you expect.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

So, is there a chance, that the Memory Optimization Adviser is not calculating the spaces correctly? I know that it will need more space because of the no-locking nature, but it is seems that this is very radical difference in space (10 GB to 500 GB). Could you tell if this is expected behavior?
0 Likes 0 ·
I'm not sure if the Memory Optimization Advisor takes into account versioning or not. It primarily just checks that you don't have columns that are unsupported. But the 3-5 times measure is the suggestion from Microsoft.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.