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.
1
vote
0answers
38 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
42 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
40 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
45 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 ...
4
votes
1answer
70 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 ...
1
vote
1answer
398 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
721 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].[...
2
votes
1answer
209 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
139 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
521 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 ...
3
votes
1answer
526 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
36 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
105 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 ...
3
votes
1answer
377 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
118 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
455 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
191 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
82 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
340 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
160 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
433 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
107 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
194 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
116 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
55 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
333 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
464 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
700 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
276 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 ...
1
vote
1answer
95 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
57 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
111 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
156 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
682 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
580 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
331 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 ...
0
votes
0answers
407 views
Queries to in Memory table are slower than to disk based
I'm working with an already built table which I must migrate to a memory optimized one. The following is a clone from the disk based. Both have the same structure with the exception that the disk ...
13
votes
1answer
444 views
Incorrect behaviour of SQL Server 2016 with memory-optimized tables
Please have a look at the following SQL query:
CREATE TYPE dbo.IN_MEMORY_TABLE_TYPE AS TABLE
(
source_col INT NULL,
target_col INT not NULL
INDEX ix_InMemoryTable NONCLUSTERED (target_col)
)
...
3
votes
1answer
446 views
Conditions for memory optimized tables stored in off-row data table
Memory optimized tables store LOB types in internal tables.
I have a table with no explicit LOB types but when I run a query against sys.memory_optimized_tables_internal_attributes, I see varchar(...
6
votes
1answer
625 views
In-Memory Optimized Error: The code generation directory cannot be created or set up correctly
I ran into a problem yesterday trying to create the first memory_optimized table on a SQL Server 2016 SP1 Enterprise Edition.
I created the database and filegroup as so
CREATE DATABASE imoltp -- ...
3
votes
1answer
1k views
Should I use tempdb or memory optimized table variable?
I would like to change some of my stored procs to use memory optimized tables.
Before I do this, could you help me to clarify some things?
parallel execution
Here I can read, the tempdb doesn’t ...
1
vote
1answer
167 views
sys.dm_db_partition_stats - incorrect row count for InMemory tables
I have a problem with sys.dm_db_partition_stats DMV. When having InMemory tables (with hash indexes, columnstores, classical b-tree) and trying to get row count via this query there is 0 displayed for ...
1
vote
1answer
431 views
Delete from table with(snapshot)
I was trying to learn querystore. When going through few queries involved in query store one of the query is below
DELETE TOP (1000000)
FROM [sys].[memory_optimized_history_table_1179151246]
WITH (...
0
votes
2answers
132 views
How would you optimize this 2 milion records table or queries?
I have a relatively large InnoDB table that contains domain names, I want to track when they expire, their traffic rank, owner email, and many parameters like that.
Currently I have around 2 million ...
2
votes
1answer
290 views
How to get space used by In-Memory table in SqlServer
I'm trying to understand how much storage In-Memory table consumes in SqlServer:
SELECT is_memory_optimized,durability_desc FROM sys.tables WHERE name='TBL_HSD_AD_OR'
select count(*) [#Rows] from ...
1
vote
1answer
691 views
Does SQL Server 2016 support foreign keys in an in-memory table from a disk bound table
If using an in-memory table in SQL Server 2016, can I define a column in that table as a foreign key from a non in-memory (or disk bound) table? I know that the foreign key does need to be the primary ...
1
vote
1answer
135 views
Using an in-memory table with a regular stored procedure
I am working on converting one of the tables in my database to an in-memory table in SQL Server Enterprise 2014. Let's call the table in question table A. When I convert table A from a regular table ...
11
votes
1answer
3k views
Performance of an In-Memory Table is worse than a disk-based table
I have a table in SQL Server 2014 that looks like the following:
CREATE TABLE dbo.MyTable
(
[id1] [bigint] NOT NULL,
[id2] [bigint] NOT NULL,
[col1] [int] NOT NULL default(0),
[col2] [int] NOT NULL ...
3
votes
1answer
4k views
How can I efficiently optimise MariaDB?
I am running a 24 GB KVM with 8 Xeon 3.2 SSD RAID 0. And I would like to get the most from my MariaDB sql server.
I am using following my.cfg
#
# This group is read both both by the client and the ...
1
vote
1answer
324 views
Why does In-Memory feature not support foreign keys?
Why does Sql-Server 2014 In-Memory feature not support primary/foreign key relations? Can anyone explain what the problem with foreign key relationships and in-memory is?
I guess just and only this ...
