How big job it is for a DBA to enable a db and/or table to become IN-MEMORY?

Question

How big job it is for a DBA to enable a db and/or table to become IN-MEMORY?

VoldemarG

Hall of Fame

Points: 3883
More actions
March 5, 2020 at 11:02 am

#3731336
What are the minimum pre-requisite requirements that need to be met
before being able to create an IN-MEMORY /Memory Optimized table in SQL Server 2016?
Like do the file groups have to be (re)created differently or any other Database-wide or Server-wide settings have to be changed?
- In addition to that, after table becomes in-memory, or many such tables -- does it add more things for a DBA to do to support and maintain all that new IN-MEMORY OLTP ?
Likes to play Chess

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

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

DinoRS SSCrazy Points: 2683 More actions · Answer 1

You need to create a FileGroup for In-Memory Objects, you cannot undo this at a later point in time. You will most likely need to configure Resource Governor to Limit Memory usage of In-Memory Objects.

Not entirely sure what you mean by "maintain all that new IN-MEMORY OLTP". The assumption would be you have regular maintenance already in place as a DBA.

You should know that TRUNCATE TABLE and MERGE won't work against In-Memory Objects which raises the question: Do you maintain application code as a DBA or not? If you do, you might have to rework some of your application code.

Foreign Key Relationships will not work.

But ofcourse you've checked the official documentation in regards to supported statements and features.

If your worry is that it might be complicated to change a table to In-Memory for a DBA, no worries there is a wizard available just for you.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

It's easy to do, but there are potential issues, as Dino pointed out.

You might read this: https://www.red-gate.com/simple-talk/sql/sql-development/beginner-guide-to-in-memory-optimized-tables-in-sql-server/

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

This is not a trivial task and great care must be taken in any such consideration because some of it is incredibly difficult to reverse if it just doesn't happen to work out. I won't insult you with a link to LMGTFY.com but you really need to do your homework on this. Start with a search for "requirements for in-memory in sql server". At least readn the Microsoft Documentation on the subject,.

--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)