No, in-memory really is this unpolished. If you are familiar with Agile you will know the concept of "minimal shippable product"; in-memory is that. I get the feeling that MS needed a response to SAP's Hana and its ilk. This is what they could get debugged in the timeframe for a 2014 release.
As with anything else in-memory has costs and benefits ...
One of the problems with new technology - especially a V1 release that has been disclosed quite loudly as not feature-complete - is that everyone jumps on the bandwagon and assumes that it is a perfect fit for every workload. It's not. Hekaton's sweet spot is OLTP workloads under 256 GB with a lot of point lookups on 2-4 sockets. Does this match your ...
Yes it is a bug, which seems to only affect table variables, with a bw-tree index access method, and an uncorrelated self-join.
Simplified repro using DELETE:
CREATE TYPE dbo.IN_MEMORY_TABLE_TYPE AS TABLE
col integer NOT NULL INDEX i NONCLUSTERED (col)
WITH (MEMORY_OPTIMIZED = ON);
DECLARE @T AS dbo.IN_MEMORY_TABLE_TYPE;
INSERT @T (col)
First make sure that replication is not causing this, as stated in the connect item the "log_wait_reuse_desc=XTP_CHECKPOINT does not necessarily mean that the XTP checkpoint worker is holding up log truncation." so start by running sp_repltrans and make certain that all data has been distributed.
Then there is this little snippet here:
I had a similar problem: I did not have replication but once I used Memory Optimized table as a test, database in Simple recovery mode, but my transaction logs weren't truncated. Manual truncation, even right after a full backup, gave the error:
Cannot shrink log file X because the logical log file located at the end of the file is in use.
A manual ...
While this post won't be a complete answer due to lacking information, it should be able to point you in the proper direction or otherwise gain insight which you can later share with the community.
Unfortunately, this definition results in a degradation of performance with respect to the previous situation with a disk-based table. The order of magnitude ...
Since the architecture of these two types of tables and indexes is completely different, I would say the answer would be "no". If the table needs to be optimized for point lookups, you should use a HASH index, but that could cause you problems, because if the key is comprised of multiple columns - and you only supply the leading column - you'll get a table ...
Looking at the CREATE TABLE definition for 2014 and scrolling down to the box headed "--Memory optimized CREATE TABLE Syntax", there is no mention of compression. Ergo, compression is not available for in-memory tables.
But yes, compression uses more CPU. Our experience is that query elapsed times are still shorter because the extra compute time is more ...
Information about these tasks is, indeed, hard to come by.
The docs (Creating and Managing Storage for In-Memory OLTP -> Scalability) say that
In SQL Server 2016 (13.x), there are multiple concurrent threads responsible to persist changes to memory-optimized tables.
And while the docs don't mention the "cmd" value that might be associated with these ...
In addition to the restrictions pointed out in Evan's answer, there are a number of other things that can prevent dropping tables. Most of them are similar to the restrictions on normal disk-based tables, and you should see them called out in your error message.
For instance, if I have an in-memory table referenced by a natively-compiled (schemabound) ...
I created a small test application to see if there were any issues accessing IMOLTP tables from Entity Framework. My test application uses the latest stable version of Entity Framework (EF6).
With the default settings, I got this error initially trying to insert a row into a memory-optimized table:
SqlException: Accessing memory optimized tables using ...
Bill you are absolutely correct and this is a new feature added in SQL 2016.
Table and Row Size in Memory-Optimized Tables
A memory-optimized table consists of a collection of rows and indexes
that contain pointers to rows. In a memory-optimized table, in-row
data cannot be longer than 8,060 bytes. However, starting SQL Server
2016 it is possible ...
Does SQL Server 2016 support foreign keys in an in-memory table from a disk bound table
No, not at this time - A foreign key between a disk based table and memory optimized table is not allowed.
You will get below error :
Msg 10778, Level 16, State 0, Line 3
Foreign key relationships between memory optimized tables and non-memory optimized tables are not ...
No, databases with In-Memory OLTP enabled do not support DDL triggers.
Both database-level and server-level DDL triggers are not supported with In-Memory OLTP tables and natively compiled modules.
You cannot create DDL triggers in a database where In-Memory OLTP is enabled since the code path required to run the trigger would interfere with the In-...
Partitioning memory-optimized tables directly is not yet supported. See SQL Server Features Not Supported for In-Memory OLTP.
A workaround is described in the documentation Application Pattern for Partitioning Memory-Optimized Tables
You can emulate partitioned tables with memory-optimized tables by maintaining a partitioned table and a memory-optimized ...
It seems you have to drop the columnstore index first. This works for me:
ALTER TABLE A DROP INDEX IX_A_CCS;
ALTER TABLE A SET (SYSTEM_VERSIONING = OFF);
DROP TABLE A;
Note that this leaves behind the temporal history table, so you might want to run DROP TABLE A_History; for good measure (which is the default naming when a history table name is not ...
First of all, if you use durable memory-optimized tables, everything from the memory-optimized side will be fully logged - the database recovery setting is not respected.
Next, is your 2017 install fully patched? There have been a number of fixes to the In-Memory engine since RTM.
In order for the log to be cleared of memory-optimized transactions, they ...
As far as I can tell the feature has not been officially announced yet.
There are a couple of resources available on this feature from members of the community:
Youtube video from Erik Darling
Blog post from Ned Otter
This would appear to be the configuration option that will allow you to put some TempDB metadata tables into memory, preventing many types ...
Is this a bug?
No it is not a bug. Its by design. They are kept for troubleshooting and supportability
From the SQL_Server_2014_In-Memory_OLTP White_Paper
Database administrators do not need to maintain the files that are generated by native compilation. SQL
Server automatically removes generated files that are no longer needed, for ...
No, as defined, a Blob is "a binary large object that can hold a variable amount of data." It can hold between 0 and 2^16 - 1 bytes (65535). A LONGBLOB can hold up to 4,294,967,295 bytes (or 4GB).
In most engines, blobs are implemented dynamically, meaning that it will only take its actual size plus some extra bytes for lenght/addressing, etc. In fact, ...
This is a bug: Connect Bug Submission
A workaround is to not pass the memory-optimized table into the stored procedure. You can use it after that though.
Fixed in SQL Server 2017 CU1:
Fix: Error when a stored procedure passes a memory-optimized table to an inline table-valued function in SQL Server 2017
Because they had to ship the feature. If they waited until it was complete, we'd still be waiting for it, and all of those customers who didn't need foreign keys (or were willing to sacrifice them temporarily for the performance gains) would never have been able to deploy.
As with many large features with deep roots into the engine (remember the first ...
If you are learning MySQL, skipping MEMORY storage engine is not a bad idea. InnoDB is the default storage engine and it is suitable for most cases. Only in edge cases it makes sense to use another one, and you probably shouldn't consider this option before knowing InnoDB quite well.
However, to answer your question, first let's check pro's and con's of ...
Instead of using a cursor*, write the update and insert operations explicitly as set-based operations:
CREATE OR ALTER PROCEDURE cache.MoveInverterData
SET XACT_ABORT, NOCOUNT ON;
DECLARE @RowsAffected integer = 0;
-- TODO: Add error handling
SET TRANSACTION ISOLATION ...
I don't have an answer for what exactly they mean, but I see them on every machine where I have a memory optimized file group.
Since they start with "HK" they seem to be related to In-Memory OLTP (codenamed Hekaton prior to the production release in SQL Server 2014).
I would say that since they are INFO messages, and they are common on instances with ...
Natively-compiled stored procedures can only reference:
in-memory table variables / TVPs
in-memory natively compiled inline TVFs
Sounds like what you want is interop, and that can't be done inside a natively-compiled procedure. If it could, you would be discarding much of the performance benefits of the natively-compiled procedure.
I was able to workaround the issue by adding another log file, which then allowed me to run a full backup, adjust the primary log file size and capped growth along with removing the extra log file added to resolve the XTP_CHECKPOINT issue.
Yes and No. With improper configuration, too much RAM can cause performance degradation.
For example on Linux platform, if HugePages is not configured, with large SGA and many database sessions, the pagetable will hold a significant amount of memory. Once at one our clients, the database server had 512 GB memory installed. They had about 4000-5000 database ...
Some people in our team want to move to SQL Server 2014 to increase performance with In-Memory Tables ...
That entirely depends on:
If more performance is needed
What the current bottleneck is
however I'm not convinced that doing such will increase performance since our tables are already in memory.
You should be suspect that it may or may not improve ...
Start with the Books Online page on estimating memory requirements for in-memory OLTP. It goes into deep detail, and here's just a snippet:
When there is an active workload, additional memory is needed to account for row versioning and various operations. How much memory is needed in practice depends on the workload, but to be safe the recommendation is ...