In-Memory Processing - Existing Tables

Question

In-Memory Processing - Existing Tables

arrjay

Hall of Fame

Points: 3455
More actions
November 11, 2014 at 6:34 am

#291807

Hi there - Is there a method of forcing existing tables into the in-memory filegroup so the table data can benefit from in-memory processing. If so, can anyone point me in the direction of a how-to-guide. Thanks!!
--------------------------------------------
Laughing in the face of contention...

Viewing 8 posts - 1 through 7 (of 7 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Lots of limitations apply: it's not a simple "switch" or "move to the in-memory filegroup".

Here's a "getting started" article: http://blogs.technet.com/b/dataplatforminsider/archive/2013/06/26/getting-started-with-sql-server-2014-in-memory-oltp.aspx

-- Gianluca Sartori

arrjay Hall of Fame Points: 3455 More actions · Answer 2

Thanks for this. I am learning that its not as straight forward however the link you provided just covers off creating in-memory tables from scratch. Do you know if you can push an existing table into in-memory filegroup and if so what are the consideration? Thanks again!

--------------------------------------------

Laughing in the face of contention...

Grant Fritchey SSC Guru Points: 398715 More actions · Answer 3

No, there is no way to "push" a table to be in-memory. You must create an in-memory table following all the restrictions and then move the data into that table. That's how it works. Also remember that there are limits on the size of the table, and you must have enough memory to put the table there, plus all the memory you need for the normal operations of SQL Server.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

arrjay Hall of Fame Points: 3455 More actions · Answer 4

Ok, great. Thanks for explaining.

There seems to be a lot of considerations which impacts whether or not in-memory processing is worth the amount of effort that is needed to get the table into in-memory. I think we will start with all new tables should go into in-memory with the view of migrating the existing tables over time.

Thanks All!!!

--------------------------------------------

Laughing in the face of contention...

Grant Fritchey SSC Guru Points: 398715 More actions · Answer 5

No. No, no, no. Don't do that. Everything just doesn't belong within the in-memory sphere. If you're not experiencing significant slow downs due to latch waits, you really shouldn't even be considering it. In-memory is fast, yes. But it has a ton of restrictions that are going to make using standard database designs extremely difficult in the near term. For example, you can't set a foreign key constraint on in-memory tables. That's just for starters. Currently, you can't alter an in-memory table. You have to drop and recreate them. There are so many reasons why you should only use this for very targeted needs, it's hard to summarize it all.

Please, only consider this functionality if you can demonstrate a real need for it, not just go with "it's faster."

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

arrjay Hall of Fame Points: 3455 More actions · Answer 6

Thanks Grant - I have actually started another thread on the limitations to see if there are any more. Looks like I'm going to be taking a hybrid approach to this technology.

http://www.sqlservercentral.com/Forums/Topic1633521-3411-1.aspx

Thanks.

--------------------------------------------

Laughing in the face of contention...

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

arrjay (11/11/2014)
I think we will start with all new tables should go into in-memory with the view of migrating the existing tables over time.

Are you intentionally trying to destroy that application? If so, good start.

In-memory means exactly that. IN MEMORY. They aren't normal tables which live on disk and are read into memory as needed. They have to be in memory always. If there isn't enough memory for all of the data of all of the in-memory tables, the DB will stop working.

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