i am also courious about possible known issues that may happen during migration to this technology.
In-Memory tables sound like a great idea in theory but there are a number of issues and limitations with them in practice, that they're usually only good for very specific use cases that most people don't have.
Here are some Microsoft docs on their limitations:
Unsupported SQL Server Features for In-Memory OLTP
Transact-SQL Constructs Not Supported by In-Memory OLTP
Supported Data Types for In-Memory OLTP
Some of the notable limitations include:
- Not being able to do cross-database querying
- Not being able to truncate In-Memory tables
- Not being able to use Filtered Indexes
- They can't be used in Replication
- Any sort of change tracking related features are not permitted
- Linked Server references can't be made in the same query as an In-Memory table
- Not being able use the
XML data types
If you want to avoid the hassles and limitations of In-Memory tables but still benefit from Memory optimized operations and workflow, then just add an adequate amount of Memory to your server, as discussed in How to Implement In-Memory OLTP Quickly and Easily. (There's also some other good links in there on issues encountered with the In-Memory tables feature, such as having to write your own conflict detection and retry logic.) The more Memory available to your SQL Server instance, the more tables and pages of data it will automatically keep cached in Memory for you. No need to manage it yourself then.
Query are already quite performant, but i have few of them...take roughtly 0.2-0.3s, too much for my need. Just for curiosity, i will add that this timing need is due the fact that those "slow" queries are bound to a web request, that should complete in less than a second.
I don't follow this statement fully. 200ms is already less than 1 second. If there are other non-database things adding more time to that web request making it take longer than a second, then you should be focusing your efforts on optimizing the biggest piece of the bottleneck pie. Otherwise, as others said in the comments, In-Memory tables are probably not the solution for your 200ms latency (especially if you're seeing that on subsequent runs of the same query, when the data pages should be already cached in Memory).
The best path forward would be to optimize the queries or re-architect the database. Your database is tiny based on your comment "my tables are quite small, arround 100k entries per table (the bigger ones). The whole DB is arround 2,5GB". So I'm sure your issue is a query or architecture problem, and the execution plans would likely expose the main bottlenecks. You can upload the query plans to Paste The Plan and link them in your Post, if you want further help.