In-Memory OLTP - N00b Question

Question

In-Memory OLTP - N00b Question

SQL Espo

SSCrazy

Points: 2678
More actions
August 7, 2019 at 2:38 pm

#3669509

I am new to the In-Memory OLTP world (yes, I am a little behind the times, mock me if you must :))
My question is the following:
If I have a time tracking which is written most often but has occasional updates applied. Data is not deleted. Would these tables be good candidates for In-Memory OLTP?
The data in these tables will be read to create dashboards and to run through a rules engine.
From what I have read, it seems like its both a good and bad candidate. Please provide some advice and/or articles for me to read to make an informed decision.

Thanks in Advance!
Michael

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

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

Steve Jones - SSC Editor SSC Guru Points: 736291 More actions · Answer 1

No mocking needed here. Lots of people don't use MOT (Memory-Optimized Tables) tables yet, so don't worry.

Here are a few thoughts. First, adding a MOT table means a new filegroup and this does start to create some restrictions and minor hassles. Not enough to stop me, but people need to be aware. We have an article coming out on one issue in a week or two, and certainly some people find little gotches in admin changes for DR/HA with MOT groups.

I would try to avoid them if I didn't need them, but if I did, I wouldn't hesitate on SQL 2016+ to use them. There are improvements to the tech in 2016 that removed many of the limitations that made this an iffy decision in 2014.

The big thing is do you need to use them? Are you experiencing blocking issues or problems inserting data? If so, then this might help. If not, use your system that way it works now. This adds complexity, which I would seek to avoid if not needed. These do allow faster access to high volumes of inserts, but this also needs admin changes and more memory. Do you want to sacrifice buffer pool memory for this? Also, if you're on standard, the amount of memory is limited for MOT tables. Get an idea of how large your table is before you move to this tech and if it exceeds the limit.

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

A few things to read and think about:

Steve Collins SSCrazy Points: 2160 More actions · Answer 3

Here's the list of unsupported features in MOT. No triggers has made it not easy (and not even tried in some cases) to migrate to for some of my projects. New instances it's waaaaay easier to design around the available feature set.

https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/unsupported-sql-server-features-for-in-memory-oltp?view=sql-server-2017

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können