Stack Overflow
Stack Overflow

Questions tagged [memory-optimized-tables]

Tables using in memory OLTP ("Hekaton") feature introduced in SQL Server 2014.

61 questions
22
votes
5 answers

ALTER TABLE DROP INDEX failed on a table that isn't memory optimized

I'm trying to drop an index created on a table, but I get this error - The operation 'ALTER TABLE DROP INDEX' is supported only with memory optimized tables. I need to remove this index in order to drop a field in my table. Is there any way of…
xalessi
  • 221
  • 1
  • 2
  • 6
22
votes
2 answers

SQL Server 2014 In-Memory OLTP vs Redis

Is SQL Server 2014's In-Memory OLTP (Hekaton) the same or similar concept with Redis? I use Redis for in-memory storage (storage in RAM) and caching, while having a separate SQL Server database (like StackExchange does). Can Hekaton do the same…
Zafar
  • 3,394
  • 4
  • 28
  • 43
11
votes
2 answers

In-Memory user defined table, not in memory?

I am using SQL Server 2014 CTP2, with READ_COMMITTED_SNAPSHOT ON (I think it's important for the question). I have create an In-Memory table type (very similar to the example the technet blog, SQL Server 2014 In Memory OLTP: Memory-Optimized Table…
Avishai.M
  • 113
  • 1
  • 6
10
votes
1 answer

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. But if the list is large and ever increasing in size then…
Pinank Lakhani
  • 1,109
  • 2
  • 11
  • 31
10
votes
1 answer

Memory Optimized Tables - Slower INSERT than SSD

I have observed that inserting data into a Memory-Optimized Table is much slower than an equivalent parallelized insert to a disk-based table on a 5-SSD stripe set. --DDL for Memory-Optimized Table CREATE TABLE [MYSCHEMA].[WIDE_MEMORY_TABLE] …
Pittsburgh DBA
  • 6,672
  • 2
  • 39
  • 68
6
votes
2 answers

SQL Server Memory Optimized Table - poor performance compared to temporary table

I'm trying to benchmark memory optimized tables in Microsoft SQL Server 2016 with classic temporary tables. SQL Server version: Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) Mar 18 2018 09:11:49 Copyright (c) Microsoft…
empi
  • 15,755
  • 8
  • 62
  • 78
6
votes
2 answers

SQL Server in-memory oltp transaction snapshot isolation

Trying to understand how transaction isolation levels work on SQL Server memory optimized tables (in-memory oltp). If I execute the following query: SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRANSACTION SELECT * FROM tm.Tasks An error…
gio
  • 337
  • 3
  • 8
5
votes
1 answer

How do you change the DURABILITY option on an existing memory-optimized table in SQL Server 2016?

I want to change the DURABILITY of a memory-optimized table in SQL Server 2016 from SCHEMA_AND_DATA to SCHEMA_ONLY. The Microsoft documentation suggests that the following ALTER TABLE statement should work: ALTER TABLE mem_opt_table DURABILITY =…
Rob Streeting
  • 1,675
  • 3
  • 16
  • 27
5
votes
2 answers

UPDATE from a table in SQL Native stored procedure (Hekaton)

I'm migrating a queue in disk to in memory SQL Server 2016 to implement a queue. This is my queue format: CREATE TABLE dbo.SimpleQueue ( MsgId BIGINT NOT NULL PRIMARY KEY NONCLUSTERED IDENTITY(1, 1), Payload VARCHAR(7500) NOT NULL, …
João Antunes
  • 798
  • 8
  • 27
4
votes
1 answer

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 based table has no primary key even though the OID…
Leonardo Lanchas
  • 1,616
  • 1
  • 15
  • 37
4
votes
0 answers

After Update errors on Memory optimized tables SqlServer

Using EF 6.1.3, SqlServer 2016. I have recently modified certain tables in my DB to memory optimized tables. Everything functions correctly, however, when I added an after update trigger on a non-memory optimized table I get the following error. SQL…
David Moseby
  • 41
  • 2
4
votes
2 answers

Does Sql Server 2014's "Hekaton" compiled stored procedures address parameter sniffing issues?

SQL Server 2014's "Hekaton" in-memory table optimization proclaims, "Native compilation of business logic in stored procedures." Because of issues with "parameter sniffing" (see here and here) in SQL Server 2012 and earlier, however, I have always…
Mike
  • 7,500
  • 8
  • 44
  • 62
3
votes
2 answers

"There is insufficient system memory in resource pool 'default' to run this query" during execution of stored procedure

I am using a stored procedure which loops through a cursor. Within the cursor I am calling a natively compiled stored procedure. The natively compiled stored procedure does an insert into a memory-optimized table. My problem is that I run into the…
Reboon
  • 578
  • 2
  • 5
  • 12
3
votes
3 answers

In-Memory, 16x perfomance gains?

I try to unterstand In-Memory... I got the CTP1 and tried In-Memory. The Whitepaper describes the Syntax for In-memory tables. I wrote some queries to find out the performance gain. A normal table with 1 million entries take the same time as a…
Marius Ilg
  • 39
  • 1
2
votes
2 answers

The current transaction attempted to update a record that has been updated since this transaction started. The transaction was aborted

I'm trying to update a table which is In-Memory OLTP. There is a scenario where we may have to update a same row in parallel. During concurrent update of a same record I am getting below reported error. Here is my sample update statement. In SQL…
Guna
  • 113
  • 3
  • 9
1
2 3 4 5