0

i have an SQL Server database, that is almost a read-only db (the write procedure will happen at scheduled time, tipically twice a day). I have recently discovered about the in-memory table (i'am a developer not a DBA expert) and wondering if can be a good idea to move the DB into a "in memory" version; and what are the general issue of this configuration.

In-memory tables is a new topic to me, I am quite confused about the fact if those table are good for speed up only IO operation, or also simple queries (in particular queries that are alredy discrete). Also i am curious to understand if this technology is a good choiche also when the SQL Server can use discretely fast SSD. I don't wanto to be too broad in my question, but i am also courious about possible known issues that may happen during migration to this technology.

PS : To give you a bit of background, the idea come to my mind, because i am facing a little performance issue. Query are already quite performant, but i have few of them (already optimized as much as i can) that 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.

Improve this question
7
  • Your question is too broad as written. I suggest you narrow the scope to a specific problem query, along with table/index DDL, and a link to the actual execution plan uploaded to Paste The Plan.
    – Dan Guzman
    Commented Jul 7, 2023 at 11:38
  • How big are the tables in this database? Standard Edition has a 32 GB limit for in-memory tables per SQL Server instance.
    – Eric Prévost
    Commented Jul 7, 2023 at 11:47
  • If your application does not have high levels of concurrency, specifically for writing, In-Memory tables are not likely to show a big improvement.
    – NedOtter
    Commented Jul 7, 2023 at 11:52
  • @EricPrévost my tables are quite small, arround 100k entries per table (the bigger ones). The whole DB is arround 2,5GB
    – Skary
    Commented Jul 7, 2023 at 12:07
  • @NedOtter thank for pointing it out. That's exactily the kind of consideration i am interested in. I have not the experience to understood which scenario this technology fit in, so learn that it best fit write intensive cases is really important to me.
    – Skary
    Commented Jul 7, 2023 at 12:09

1 Answer 1

Reset to default
2

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:

  1. Unsupported SQL Server Features for In-Memory OLTP

  2. Transact-SQL Constructs Not Supported by In-Memory OLTP

  3. Supported Data Types for In-Memory OLTP

Some of the notable limitations include:

  1. Not being able to do cross-database querying
  2. Not being able to truncate In-Memory tables
  3. Not being able to use Filtered Indexes
  4. They can't be used in Replication
  5. Any sort of change tracking related features are not permitted
  6. Linked Server references can't be made in the same query as an In-Memory table
  7. Not being able use the DATETIMEOFFSET or 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.

Improve this answer
3
  • 1
    Thank a lot, very useful answer. I have understood that in-memory table it's clearly a very specific optimization that fit corner cases, definetly not something that will help me. It's very likely i may optimize DB structure and query, but i think it's better to open a specific question about that, because this one is focused on in-memory tables
    – Skary
    Commented Jul 7, 2023 at 12:45
  • Sorry if i go OT but i really would like to provide a bit of better context, even if it's not really important. What i mean with the timing provided, is that the web request should complete in <1s. There are few fixed cost on which i have no or very little control (request creation by the client, network latency, response rendering on client). So wasting 0.2-0.3 server side, for just a query is too much IMHO, and increase to much the "risk" that the overall time exceed the 1s mentioned above. Not to mention the fact that the machine will become more prone to overload.
    – Skary
    Commented Jul 7, 2023 at 13:19
  • @Skary Np, glad to be helpful! Yes, separate question more specifically targeted to the queries you're trying to optimize probably makes sense, good idea. Depending on how critical the performance is needed, this I where frameworks like localized Memory caches such as Redis can be useful. Instead of trying to optimize the 200ms you have control over, you can completely eliminate the entire ~1s web request call by introducing a localized cache as a middle man, especially since your data only changes twice a day. But I will also say this, 200ms does sound high for the tiny database you have.
    – J.D.
    Commented Jul 7, 2023 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.