In-Memory user defined table, not in memory?

Question

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 Types and Table Variables), and I have several In-Memory tables.

In the query itself I have a join between the regular In-Memory tables and the In-Memory table type, acting as a filter, when I execute the query I get this error message: "A query that accesses memory optimized tables using the READ COMMITTED isolation level, cannot access disk based tables when the database option READ_COMMITTED_SNAPSHOT is set to ON. Provide a supported isolation level for the memory optimized table using a table hint, such as WITH (SNAPSHOT)."

I'm removing the READ_COMMITTED_SNAPSHOT ON as I'm writing this question, but the question remains, If I have created a In-Memory data type, and this specific type will "never spill to disk", as the blog says, why does the server "see" it as a "disk table?

To clear things up, I have tried to do the join with only In-Mem tables and it works, as soon as the table type came in I got the error.

Update: when I removed the READ_COMMITTED_SNAPSHOT (now it's off) the query works, but now I have lost multi-version/no lock/speed, I would like to hear another solution.

Thanks

Steps to reproduce.

Create a database with a memory optimised file group

CREATE DATABASE MemOptimized

GO

ALTER DATABASE MemOptimized 
    ADD FILEGROUP mofg 
    CONTAINS MEMORY_OPTIMIZED_DATA

GO

ALTER DATABASE MemOptimized 
    ADD FILE (  NAME = N'mofg', 
                FileName = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MemOptimized.ndf') 
    TO FILEGROUP mofg

Create some objects

CREATE TYPE [dbo].[tType] AS TABLE(
        [C] [int] NOT NULL
        INDEX ix NONCLUSTERED HASH (C) WITH (BUCKET_COUNT = 8)
    ) WITH ( MEMORY_OPTIMIZED = ON )

CREATE TABLE [dbo].[tTable] (
        [C] [int] NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 8)
    ) WITH ( MEMORY_OPTIMIZED = ON )


INSERT INTO [dbo].[tTable] VALUES(1)

GO

CREATE PROC P
AS
    DECLARE @t [dbo].[tType]

    INSERT INTO @t
    VALUES     (1)

    SELECT *
    FROM   [dbo].[tTable] t
           INNER JOIN @t
             ON [@t].C = t.C 

The following works without error

ALTER DATABASE [MemOptimized] 
SET READ_COMMITTED_SNAPSHOT OFF WITH ROLLBACK IMMEDIATE

GO

USE MemOptimized

EXEC P 

But this

ALTER DATABASE [MemOptimized] 
SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE

GO

USE MemOptimized

EXEC P 

Gives the error detailed above

Msg 41359, Level 16, State 0, Procedure P, Line 62 A query that accesses memory optimized tables using the READ COMMITTED isolation level, cannot access disk based tables when the database option READ_COMMITTED_SNAPSHOT is set to ON. Provide a supported isolation level for the memory optimized table using a table hint, such as WITH (SNAPSHOT).

Answer 1

I see this as well.

When RCSI is enabled then auto commit transactions at default read committed level work fine when joining together two instances of the in memory table type.

DECLARE @t1 [dbo].[tType]
DECLARE @t2 [dbo].[tType]

INSERT INTO @t1 VALUES (1);

INSERT INTO @t2 VALUES (1);

SELECT *
FROM   @t1
       JOIN @t2
         ON [@t1].C = [@t2].C 

Also joining two different "normal" memory-optimized tables works fine without any hints.

Additionally joining an empty memory-optimized table type to a normal memory-optimized table works fine.

DECLARE @t [dbo].[tType];

SELECT *
FROM   [dbo].[tTable] t
        INNER JOIN @t
            ON [@t].C = t.C 

But the reverse is not true. As long as the in memory table type instance contains at least one row then joining it to an (empty or otherwise) in memory table raises the error.

A query that accesses memory optimized tables using the READ COMMITTED isolation level, cannot access disk based tables when the database option READ_COMMITTED_SNAPSHOT is set to ON. Provide a supported isolation level for the memory optimized table using a table hint, such as WITH (SNAPSHOT).

The solution is simple and is indicated in the error message. Just add the table hint WITH (SNAPSHOT)

DECLARE @t [dbo].[tType]

INSERT INTO @t
VALUES     (1)

SELECT *
FROM   [dbo].[tTable] t WITH(SNAPSHOT)
       INNER JOIN @t
         ON [@t].C = t.C

Or a less granular solution is

ALTER DATABASE [MemOptimized] 
SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON WITH ROLLBACK IMMEDIATE 

which will set the isolation level for memory-optimized tables to SNAPSHOT (as if you included WITH(SNAPSHOT) hints to every memory-optimized table) Source

As far as I can gather neither of these actually change the semantics and the ability to omit the hint in some circumstances is just a programming convenience.

For autocommit transactions, the isolation level READ COMMITTED is implicitly mapped to SNAPSHOT for memory-optimized tables. Therefore, if the TRANSACTION ISOLATION LEVEL session setting is set to READ COMMITTED, it is not necessary to specify the isolation level through a table hint when accessing memory-optimized tables. Source

The isolation level READ COMMITTED is supported for memory-optimized tables with autocommit transactions. READ COMMITTED is not supported with explicit or implicit user transactions. Isolation level READ_COMMITTED_SNAPSHOT is supported for memory-optimized tables with autocommit transactions and only if the query does not access any disk-based tables. Source

I'm not sure why this mix of different in memory table types causes this particular error message. I assume it is just an artefact of being a CTP and that at RTM either the combination will be allowed or the error message and documentation will be updated to refer not just to disk based tables.

Answer 2

The error message you are seeing is incorrect. We were treating the memory-optimized table variable as if it's a disk-based table. The issue was fixed in SQL Server 2014 RTM CU1.

Note that the database option READ_COMMITTED_SNAPSHOT does not apply to memory-optimized tables. It applies only to disk-based tables. Memory-optimized tables are always multi-versioned.