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.

Filter by
Sorted by
Tagged with
0 votes
2 answers
212 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 ...
dcop7's user avatar
  • 29
0 votes
0 answers
17 views

What is the quickest and most efficient way to remove an in-memory filegroup from a large SQL Server 2019 database? [duplicate]

I have a database with a in-memory filegroup that I want to remove (I wont go into much details why, but I want to move the database to another place without this filegroup). Notes: This is running ...
dcop7's user avatar
  • 29
0 votes
0 answers
113 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 ...
SqlNightOwl's user avatar
13 votes
1 answer
848 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 ...
Joe Obbish's user avatar
0 votes
0 answers
1k 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 ...
Iga21207's user avatar
-3 votes
1 answer
84 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....
smartwombat's user avatar
0 votes
1 answer
98 views

Are there any special considerations for a log-shipping role change when the database has memory optimzed files?

We're noticing a problem with our log-shipping setups when the database contains a memory-optimized file. Specifically, after changing the primary/secondary roles, restoring log backups taken after ...
Toby Allsopp's user avatar
0 votes
1 answer
388 views

SQL Server 2017 freezing during bulk insert of large file into in-memory staging table

Running SQL Server 2017 CU20 Standard on a dedicated Dell PowerEdge with 28 cores and 192GB of RAM. I have a stored procedure that processes large files (~7GB each) in a loop by bulk inserting them ...
eek's user avatar
  • 123
1 vote
1 answer
2k views

Can I downgrade from SQL Server 2017 CU25 to CU20?

New instance of SQL Server 2017. I chose to install up to CU25 during the initial installation. Been using the database for a few months, but it seems to have a memory leak when using memory-optimized ...
eek's user avatar
  • 123
1 vote
0 answers
156 views

Bogus SQL Server (XTP) in-memory error 41842 claiming 4B rows updated [closed]

We run a large, very active set of in-memory (schema-only) tables - the main table has been split (sharded) into 5 separate databases because the table is a total size of > 50gb (Standard Edition ...
DavidS's user avatar
  • 29
2 votes
2 answers
1k views

SQL Server 2016 database backup fails to restore on SQL Server 2019

We were trying to restore the database from SQL Server 2016 Developer Edition to SQL Server 2019 Developer Edition and it was failing with an exception. Details about this DB: It has in-memory ...
Siva Dasari's user avatar
0 votes
1 answer
67 views

Sql Server consuming lot of memory Creating Bottleneck for my web application Hosted in the same server

HI I have a server with 64 GB Ram and I have assigned around 52GB to the SQl Server itself. However Even this has effected the performance of the server and the web application hosted in the same ...
srikanth reddy varkala's user avatar
20 votes
2 answers
3k views

SQL Server in-memory optmized tempdb metadata memory usage continually growing

Problem We have enabled sp_configure 'tempdb metadata memory-optimized' = 1, and now the tempdb meta data is taking over 400 GB on one of our servers and continues to grow. There are some drops in ...
SQLing4ever's user avatar
5 votes
1 answer
578 views

Memory Optimized intermittently very slow perfomance

I have a memory optimized table with (in my test) 10 million rows. Running some UPDATE in parallel against it, I get intermittent spikes in CPU and huge spikes in execution time (>1s from 1ms) of ...
Juri Robl's user avatar
  • 151
5 votes
2 answers
433 views

In-Memory OLTP SQL Server 2016 - insufficient system memory

I am trying to place an In-Memory OLTP table, Here is the memory estimate you will need: But when the migration starts, the following error message is displayed: The size of my table is ...
denis ciola's user avatar
1 vote
2 answers
146 views

What is the best practice location for Memorize Optimization (OLTP) files?

Best practices for log and mdf files is to split across drives (e.g. G:\SQL Data & I:\SQL Logs). What are the best practices for memory optimization files? Should they reside on their own drive? ...
Chris Gonzales's user avatar
1 vote
1 answer
1k views

Accessing Memory Optimised Tables Under Snapshot Isolation

We have a high traffic database, that I am looking to convert to using IM-OLTP for performance gains. Due to the large size of the table in question, I'm looking to do a hot/cold solution, with a view ...
Obsidian Phoenix's user avatar
1 vote
1 answer
174 views

How to enable INMEMORY JOIN GROUP in Oracle 12.2

I have read official Oracle documentation on INMEMORY JOIN GROUP and run code from their tutorials. However I unable to successfully create INMEMORY JOIN GROUP. Installed database is Oracle 12.2 ...
BrilliantContract's user avatar
1 vote
1 answer
380 views

Defining OLAP or OLTP on table level in Oracle/postgres/SQLserver

Usually companies have separate databases for OLTP and OLAP (Row stores and column stores) where data is loaded through ETL jobs to another database for analytical processing. I am particularly ...
user3102917's user avatar
0 votes
1 answer
205 views

In Memory OLTP causing OOM Issue in SQL Server even though no data in In Memory OLTP Table

We have only 1 memory optimized table in each database and there are total 10 databases as such. This table is completely empty. But still we can see in DBCC MEMORYSTATUS Output that In memory OLTP is ...
sachin-SQLServernewbiee's user avatar
6 votes
2 answers
492 views

Cannot reclaim Index Unused Memory in In-Memory OLTP

Steps to reproduce the problem Create a database with memory-optimized filegroup and container Create schema only in-memory table with nonclustered pk Simulate insert and delete activity. My result is ...
Zikato's user avatar
  • 3,931
1 vote
2 answers
386 views

in-memory DB startup time SQL Server

I have a question on amount of time it takes for an in-memory OLTP SQL Server 2019 database (no Accelerated DB recovery configured) to come online. I have 2 tables each with 40GB in size (in-memory) ...
kevin's user avatar
  • 83
0 votes
0 answers
73 views

In-memory table performance

I hope someone can help me troubleshoot the cause of the sudden drop in Transaction created/sec, batch request/sec and the slow performance of in-mem table. This is a POC exercise - I am trying to ...
user1716729's user avatar
0 votes
1 answer
31 views

MySQL controlling shutdown

We are running a financial application using MySQL as database. For performance reasons we are using memory tables for a few of our live tables where the data is populated ever few milli seconds. ...
Vivek's user avatar
  • 13
2 votes
2 answers
1k views

Error creating memory optimized filegroup in SSDT

I am attempting to add a memory optimized filegroup to a SQL server database project in SSDT. This is for SQL Server 2017, using Visual Studio 2017. However compiling the project (pressing F5 to ...
Definite's user avatar
  • 145
10 votes
1 answer
1k views

Efficiently store large list structure in RocksDB so that the data can be retrieved in pages

Description: RocksDB is a key-value storage so we can simply serialise the list of objects & store the value corresponding to a key. This would be ok if the data in the list is small enough. ...
Pinank Lakhani's user avatar
2 votes
2 answers
885 views

Is an in-memory table faster to read from than a table cached in memory?

Is an in-memory (aka Hekaton) table faster to read from than a non-in-memory table that's already cached in the memory of a SQL server? I ask this because I have a case where I'm considering ...
J.D.'s user avatar
  • 35.1k
0 votes
2 answers
751 views

How to prevent memory optimized tables from blowing out OPT_MEMORY_OPTIMIZED\$HKv2

Microsoft SQL Server 2017 database, Developer edition. Just updated to latest version (RTM-CU17) (KB4515579) - 14.0.3238.1 (X64). It uses memory optimized tables. When I run a very large stored ...
user1147862's user avatar
9 votes
1 answer
1k views

Database Stuck 'In Recovery' with command XTP_DB_RECOVERY

We recently converted a table to memory optimized data. Our backups have all ballooned (3x300GB files to 3x600GB files for Full, 3x50GB to 3x250GB for Diffs), and startup has been getting ...
InMemoryHardships's user avatar
0 votes
1 answer
103 views

Can we migrate Azure SQL Database's objects to In-Memory OLTP?

Can we migrate Azure SQL Database's objects to In-Memory OLTP? IF yes then how to perform this step by step?
Parth Mehta's user avatar
3 votes
2 answers
2k views

Move table from InnoDB to Memory

I am using mariadb 10.4 with nignx with joomla 4 on Debian 8 system. Is it possible for a table that is already created with records in a working database to be moved on memory? I find only ...
Christoforos's user avatar
0 votes
0 answers
367 views

How to calculate count of rows of Memory-Optimized Table?

Performance is important for me so I don't use select count(*) from MyTable. I use the following query to calculate rows count: SELECT SCHEMA_NAME(schema_id) AS [SchemaName], [Tables].name AS [...
isxaker's user avatar
  • 67
0 votes
1 answer
91 views

Modifying Schema on secondary replica

I am looking for a solution for a secondary replica change. I have a vendor database that uses memory optimized tables. I am trying to find a solution that would allow the In Memory tables be ...
TallKewlOnez's user avatar
2 votes
1 answer
181 views

Is there any benefit for natively compiled TVFs when used in non native modules?

I have some schema-bound inline table valued functions that I could natively compile. However, my workflow does not use store procedures and it would only be invoked from entity framework or ado.net ...
Michael B's user avatar
  • 519
5 votes
3 answers
399 views

"tempdb metadata memory-optimized" configuration option

Checking SQL Server 2019 CTP 2.5 (15.0.1500.28) I found this new configuration option and was wandering what is behind it. Couldn't find any information so far and was wondering if you know where to ...
Grzegorz Lewicki's user avatar
1 vote
2 answers
2k views

SQL Server Internal Memory Error

I am using SQL Server 2016 Standard Edition that shows the following errors on DBCC CHECKDB and also on updating statistics. Version: Microsoft SQL Server 2016 (SP2-CU2) (KB4340355) - 13.0.5153.0 (...
lemniscate's user avatar
6 votes
1 answer
1k views

Memory-optimized table index size 10X bigger than data

On one of our production servers, I found that index size is 10X times bigger than data size. These servers and schemas are identical with a similar workload. CREATE TABLE [dbo].[...
Yorik's user avatar
  • 193
0 votes
1 answer
130 views

Execution plan for staging In-Memory tables estimate row count =1

We are using In-Memory tables (durability schema only) as temporary staging tables for part of the ETL process. the nature of these staging tables is empty most of the time. The ETL process executed ...
Yorik's user avatar
  • 193
1 vote
0 answers
462 views

What is the best option for a large slowly changing dimension lookup table

This question is an extension of a previously asked question Index Strategy and Query optimization, which further extends this question: Search between IP string addresses I implemented the two new ...
J Weezy's user avatar
  • 199
1 vote
2 answers
178 views

Insert streamed RFID data

I have RFID readers currently writing data to files on a hard drive, which then SSIS reads and inserts into the database. Instead, I'd prefer that SQL Server (2016+) read the data stream directly into ...
VBStarr's user avatar
  • 109
1 vote
0 answers
2k views

PostgreSQL Optimize bulk values CTE and SELECT INTO TEMP TABLE to prevent OOM kills

Recently we moved some code from Rails way to raw SQL for performance reasons. We have some jobs which fetch some data from APIs, data can be in 100K+ rows of count sometimes. This data needs to be ...
Zia Ul Rehman Mughal's user avatar
0 votes
1 answer
785 views

move a database on sql server 2017 with memory OLTP feature [duplicate]

I have a database on sql server 2017 standard edition with in memory OLTP feature enabled, I have to move it on another disk, which is the best way to do it? are there any possible problems? Can I ...
Gabriele D'Onufrio's user avatar
6 votes
1 answer
1k views

HKHostFreezeCkptTrimming on SQL Server 2017

Have a SQL Server 2017 Standard edition box with build 14.0.3045.24 with one memory-optimized table. When our 3rd party backups complete the SQL Server 2017 error log shows several [INFO] messages ...
SQLDBAND's user avatar
  • 345
3 votes
1 answer
3k views

Can Entity Framework access In Memory OLTP Tables?

Are there any restrictions with having Entity Framework access In Memory OLTP Tables? Or is this not possible?
user avatar
5 votes
3 answers
8k views

How can I drop a Memory Optimized Table?

Running SQL Server Standard 2016 SP2-CU1. I am not as concerned with the In memory Filegroup, but I must be able to drop the table objects? Or possibly rename them? Running: DROP TABLE [REF].[...
Rocko's user avatar
  • 91
3 votes
1 answer
1k views

SQL Server 2017 log file growth with memory optimized table

I'm start to using the memory optimized table, the problem that I have is the log file growth also with the database put in simple mode. On my maintenance schedule I checkpoint and shrink, but still ...
Alberto Cigliutti's user avatar
4 votes
1 answer
836 views

How to drop an in-memory temporal columnstore

Consider the following: CREATE DATABASE [Foo] ALTER DATABASE [Foo] ADD FILEGROUP XTP CONTAINS MEMORY_OPTIMIZED_DATA ALTER DATABASE [Foo] ADD FILE (NAME=XTP,FILENAME='C:\Program Files\Microsoft SQL ...
Jeroen Mostert's user avatar
2 votes
1 answer
3k views

SQL Server - Nonclustered Index with Include on User Defined Table Type?

I was working with in-memory User Defined Table Types and got stuck on creating indexes on them. Here's what I am trying to do: CREATE TYPE dbo.CustomersTbl AS TABLE ( customerID int NOT ...
Sequenzia's user avatar
  • 123
5 votes
2 answers
4k views

What are all the XTP_THREAD_POOL processes doing?

I just ran sp_who on my SQL Server 2016 instance that uses MEMORY_OPTIMIZED tables, and I see several XTP_THREAD_POOL processes running: Some other details about the output: There are exactly 6 ...
Josh Darnell's user avatar
3 votes
1 answer
192 views

Indexes: Normal tables vs memory optimized

If two tables have the same schema but one is "normal" and the other is memory optimized, if I research on how indexes should be for the "normal", can I do a direct extrapolation for the memory ...
FEST's user avatar
  • 133