In-Memory tables candidate

Question

In-Memory tables candidate

89netanel

SSCrazy

Points: 2130
More actions
January 21, 2018 at 3:24 am

#325503

Hello All,
how can i decided which table is candidate from disk table to in-memory table?
and how can i decide which one of the candidate tables will be schema or data and schema?
Thanks in advance!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 1

89netanel - Sunday, January 21, 2018 3:24 AM
Hello All,
how can i decided which table is candidate from disk table to in-memory table?
and how can i decide which one of the candidate tables will be schema or data and schema?
Thanks in advance!

"It Depends". You first have to identify what problem you're trying to solve vs the resources (mostly memory, in this case) needed to use in-memory tables. For example, if you only have 64 GB of RAM, putting tables in RAM may actually hurt the rest of what needs to be done by the server. If the table exists on SSDs, it may be a bit of a waste to do the in-memory thing. I'll also state that the general consensus as to how much performance you may gain for in-memory tables is between 5 and 20 times faster. That's actually quite pitiful compared to what you can get if you take the time to identify and fix performance challenged code along with a decent indexing strategy.

For detailed recommendations, estimating how much memory will be dedicated to the table, etc, etc, a search on Yabingooglehoo will give tell you how it works, how to plan for it, how to do the migration, and a lot of the caveats with it. Here's a Google link to get you started.

https://www.google.com/search?q=determine+which+tables+are+good+for+in+memory+sql+server

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Evgeny Garaev SSCertifiable Points: 7428 More actions · Answer 2

The good candidate would be a table in OLTP database with huge number of inserts per second. Have you checked this article - https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/overview-and-usage-scenarios ?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

89netanel - Sunday, January 21, 2018 3:24 AM
Hello All,
how can i decided which table is candidate from disk table to in-memory table?
and how can i decide which one of the candidate tables will be schema or data and schema?
Thanks in advance!

The fact that you are asking those questions actually means that you cannot decide those things. And no amount of forum back-and-forth will get you that knowledge for your system(s) either. That stuff is FAR to complex and with a HUGE number of caveats, provisos, limitations, gotchas, etc, etc, etc.

If you have a performance problem that you think may be solved with it I would be willing to bet that you can get where you need to be without In-Memory OLTP. If you REALLY do need it, hire a professional to ensure that a) you do and b) help you get there successfully.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

89netanel - Sunday, January 21, 2018 3:24 AM
and how can i decide which one of the candidate tables will be schema or data and schema?

Um, may I suggest you start with the documentation as to what those two options mean. Because which you use should be obvious for any table you consider.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass