x

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?

more ▼

asked Jul 21, 2014 at 01:47 PM in Default

avatar image

gotqn
541 12 20 28

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Jul 21, 2014 at 06:04 PM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

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?

Jul 22, 2014 at 06:22 AM gotqn

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.

Jul 29, 2014 at 11:37 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x78
x17
x9
x7

asked: Jul 21, 2014 at 01:47 PM

Seen: 404 times

Last Updated: Jul 29, 2014 at 11:37 AM

Copyright 2018 Redgate Software. Privacy Policy