Questions tagged [memory-optimized-tables]
Memory Optimized Tables typically refer to tables where the rows in the table are stored in the server's RAM.
109 questions
1 vote
0 answers
81 views
Under what circumstances will a memory optimized table variable with a hash index outperform other temporary tabular structures?
Context Aside from victims of latch contention, everybody gave up on memory-optimized table variables pretty quickly. Probably around the time they realised that they still don't have statistics. Yet, ...
- 1,342
1 vote
1 answer
98 views
Transaction log file for in-memory database doesn't shrink
I am using SQL Server 2019 Enterprise Edition. We have an in-memory database used for session state data with one table stored in memory, schema only durability, simple recovery, differential backups ...
- 11
0 votes
1 answer
94 views
Is there any disadvantage to moving a small and frequently read read-only table to in-memory OLTP?
Suppose that you have a table which is: Read from so frequently that it is almost always in RAM. Very small. Almost never changed. Is there any disadvantage moving such a table to in-memory OLTP? My ...
- 1,342
0 votes
1 answer
94 views
In-Memory Table Type with Primary Key causing Eager Index Spool
I have defined an In-Memory Table Type to hold a sequence of unique integers for the purpose of passing them between stored procedures. The type has the following definition: CREATE TYPE [dbo].[...
- 165
6 votes
1 answer
397 views
Microsoft SQL In-Memory OLTP in SQL Express 2019/2022
I am trying to use SQL In-Memory OLTP. The limitation is that the solution needs to be compatible with SQL Express 2019 and 2022. This link claims that In-Memory OLTP is compatible with all versions ...
- 187
1 vote
0 answers
44 views
Restoring of OLTP In-Memory DBs in SQL 2022 on Ubuntu really slow
We've started using OLTP In-Memory tables (schema durability only) in a small DB (DB properties show 137 MB of Row data, 1GB of In-Memory data, BAK size of 152MB) and the recovery times (either ...
- 11
0 votes
1 answer
153 views
What happens when you try to use more than 32 GB of memory-optimized data in a database on Standard Edition?
In SQL Server 2019, you cannot put more than 32 GB of memory-optimized data in a database on Standard Edition. What happens if you try anyway? I'd try it myself, but I don't want to risk discovering ...
- 1,342
4 votes
2 answers
968 views
In-memory OLTP databases take very long to recover during startup
we use SQL Server 2019 on Windows with in-memory oltp activated on some databases. After a server reboot/service restart, the in-memory databases take very long to be available (more than an hour) ...
- 43
0 votes
1 answer
711 views
Why use a memory-optimized table variable instead of a temp table or normal table variable?
Table variables have a bad reputation, mostly due to cardinality estimates. However, this reputation was earned before the introduction of memory-optimized table variables. Microsoft covers some use-...
- 1,342
0 votes
1 answer
139 views
If memory-optimised tables always live in RAM, how do they survive a server reboot?
I clearly have a missing piece in my understanding of in-memory OLTP. Suppose that I have a memory-optimised table that is set up to persist both schema and data. Given that these tables always live ...
- 1,342
0 votes
2 answers
780 views
What is the quickest and most efficient way to remove an in-memory filegroup from a large SQL Server 2019 database?
I have a database with an in-memory filegroup that I want to remove (I won't go into many details why, but I want to move the database to another place without this filegroup). I'm trying to move the ...
- 29
0 votes
0 answers
209 views
LocalDB build and Memory Optimized table types
We have a DevOps pipeline for our DB Build/Release process and we're using VS SQL Database projects. We have one DB which makes extensive use of memory optimized table variables. Used for transfering ...
- 55
14 votes
1 answer
2k views
Why does a simple natively compiled stored procedure run out of memory when table variables are used?
My version of SQL Server is SQL Server 2019 (RTM-CU18). The following repro code requires that an in memory filegroup is created. For anyone following along, please remember that an in-memory ...
- 33.3k
0 votes
0 answers
2k views
"There is insufficient system memory in resource pool 'default' to run this query" error with MEMORY_OPTIMIZED tables
I use Memory-Optimized Table in my SQL Server. However the error "There is insufficient system memory in resource pool 'default' to run this query" occures after several drop/create/insert ...
- 1
-2 votes
1 answer
170 views
DELETE ... INTO ... FROM (SELECT)
I am wondering why this is coded this way. declare @messages table ([SEQUENCE_ID] BIGINT, [MESSAGE_ID] BIGINT PRIMARY KEY CLUSTERED ([SEQUENCE_ID])) ; delete T output deleted....
- 21