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
2
votes
2answers
28 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 ...
6
votes
0answers
36 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. ...
2
votes
2answers
52 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 ...
0
votes
2answers
33 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 ...
7
votes
0answers
360 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 ...
0
votes
1answer
40 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?
1
vote
2answers
125 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 ...
0
votes
0answers
72 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 [...
0
votes
1answer
32 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 ...
1
vote
1answer
44 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 ...
5
votes
3answers
180 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 ...
2
votes
2answers
355 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 (...
3
votes
1answer
253 views

Memory Optimized table: Index size 10X bigger than data

On one of our production servers, I found out that Index size is 10X times bigger than data size. These servers and schemas are identical with a similar workload. CREATE TABLE [dbo].[...
0
votes
1answer
60 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 ...
1
vote
0answers
323 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 ...
1
vote
2answers
49 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 ...
0
votes
0answers
365 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 ...
0
votes
1answer
154 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 ...
5
votes
1answer
146 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 ...
2
votes
1answer
982 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?
4
votes
3answers
2k 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].[...
3
votes
1answer
501 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 ...
4
votes
1answer
293 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 ...
2
votes
1answer
1k 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 ...
5
votes
1answer
1k 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 ...
0
votes
0answers
37 views

BigData IoT table without foreign key

We thinking about switching our database structure to a more modern schema. We hope to reduce server performance peaks and gain more performance. This for I read some concepts about general so called ...
3
votes
1answer
154 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 ...
2
votes
1answer
703 views

Adding index - There is insufficient system memory in resource pool 'default' to run this query

Here's the query I'm trying to run in order to add an index to my new memory optimized table ALTER TABLE [s_Dim_Response_ram] ADD INDEX response_ram_clientsurveydid NONCLUSTERED ([Clientid],[...
1
vote
2answers
339 views

Stored Procedure return unwanted cursor result

Context I have created a stored procedure that simulates a merge command for an OLTP in-memory table. It used the simulated merge for upsert moving massive data form a normal table to an OLTP one. ...
0
votes
2answers
759 views

Cons of using a star schema in a columnar database

My organisation is implementing a new data warehouse using a columnar data store (Redshift) and the performance is currently abysmal. There are many causes for this but I think the key reason is ...
2
votes
1answer
326 views

Create table partitions on different filegroups, the first boundary on a Memory Optimised filegroup

99% of all our transactions are against the latest week's data, a WEEK_SELECTOR column describing which week is being described. Partitioning our billion+ row table by week would be beneficial, more ...
3
votes
0answers
91 views

In-Memory OLTP Non-Durable

I created In-Memory OLTP Non-Durable Tables in 4 different SQL servers.Lately one of the server performance has degraded and I see lot of processes (Almost 200-300) running with command XTP ...
0
votes
1answer
769 views

How to enable Oracle Database In-Memory feature in Oracle 11g R2 to reduce report generation time?

Problem: Each report generation takes a few hours in Oracle 11g R2 and a few tens of reports has to be generated every day. Objective: To reduce report generation time Understood: a) "reports that ...
1
vote
1answer
396 views

What are some practical use cases for memory tables?

Trying to learn mysql and having tons of other material to learn, I'm considering skipping memory tables, since they have many special characteristics to consider especially that they vanish, after ...
2
votes
1answer
755 views

MEMORY_OPTIMIZED table as replacement for temporary tables

I use temporary tables for complex processing which is done in several "child" procedures. These procedures are working in the same session, so the temp tables are accessible for them. Is there a ...
1
vote
2answers
120 views

SQL SERVER 2016 In-Memory Database

In SQL Server 2016, if we create a database in RAM (i.e using memory optimized tables), the database size should not exceed RAM size, am I right in saying that?
1
vote
2answers
292 views

How do I use SQL Server's “Hekaton”?

The SQL Server 2017 version on Linux comes packaged with a utility called mtlogreader.exe that is absent from the Windows distribution, MtLog Reader - Dump Hekaton MtLog files Usage: \\VBOXSVR\mssql\...
1
vote
2answers
172 views

SQL Server 2016 memory optimised table: Precreated files constantly growing

I have a memory optimised table in SQL Server 2016, with file durability enabled. The table is only 100MB. SQL Server is constantly creating new files on disk, which start off at a few hundred MB, but ...
1
vote
0answers
62 views

Why are pages being removed from the buffer cache

I have an test environment with SQL Server 2016 SP1 installed. My machine has 32GB of RAM and I give 30GB to SQL Server. I have 2 databases in my server - BGD and, BGD_Mem. The second one contains ...
3
votes
1answer
773 views

SQL Server In-Memory Table Conversion [closed]

I have a table that I want to convert to memory optimized. I have 160 million rows in my table. According to this topic my table will need: (24B(header size) + 8B(IndexPointer) + 30B(dataRowSize)) * ...
0
votes
2answers
562 views

Diskbased to in memory tables sql server 2016 ram requirement

I am planning to migrate my tables to memory optimized tables. Currently, I have a total of 250 gigabytes database size on disk. If I migrate this to in memory tables, how much physical RAM will I ...
4
votes
1answer
926 views

Load into memory-optimized table gives insufficient memory error

I am trying to load a memory-optimized table with rows from a disk-based table. It is just a simple INSERT..SELECT since the table structures are almost the same. The database is on a dedicated ...
0
votes
1answer
486 views

in-memory table quota reached in error

Edit 1: From article posted by first answer > this: Additionally, if you are using memory optimized table in the context of table valued parameters repeatedly, you may encounter this error even you ...
2
votes
1answer
96 views

Will I benefit from SQL Server 2014 In-Memory tables if my tables are already fully cached in-memory?

Our most frequently used database is relatively small (5GB), and as a consequence, our 16GB memory dedicated SQL Server 2012 database server chooses to cache all used tables in memory (Buffer Cache ...
0
votes
2answers
68 views

Can I migrate 2 tables (with one table foreign key referencing the other) to in-memory OLTP

SQL 2016 - I have 2 tables, Foo & Bar - Bar references Foo. I'd like to turn Foo into an in-memory optimized table, however as Bar references this, I can't do it. I also can't make Bar in-memory ...
0
votes
1answer
150 views

SQL Server 2016 issue regarding migration of in-memory table and foreign keys

Trying to use table memory optimization advisor on SQL Server 2016, seems I need to drop and re-create the foreign keys of some disk based tables, the size of the table is 300 GB and I'm wondering if ...
1
vote
0answers
225 views

Disk space for non-persisted In-Memory tables

I have a database on a SQL Server 2014 SP2, CU5 Enterprise edition. The database has a memory optimized filegroup. There are two tables in it, both are set to DURABILITY = SCHEMA_ONLY and have a low ...
3
votes
0answers
1k views

There is insufficient system memory in resource pool 'default'

I'm running SQL Server 2016 and I'm getting this error: There is insufficient system memory in resource pool 'default' ...when running batch queries with millions of rows. I decreased the batch ...
3
votes
1answer
765 views

Use Natively-Compiled Stored Procedures in disk-based tables

Is it completely worthless to use a Natively-Compiled Stored Procedure over disk-based tables? (SQL Server 2016) Should all the queries inside a Natively-Compiled Stored Procedure point only to in-...
3
votes
1answer
441 views

Passing Memory-Optimized Table Into Inline Function Causes Error When Called From Stored Proc

When I use a memory-optimized table with a stored Procedure that passes the table to an inline table-valued function I get an error: Msg 596, Level 21, State 1, Line 0 Cannot continue the ...