sql-server - 内存中用户定义表，不在内存中？

Question

我正在使用 SQL Server 2014 CTP2，并且 READ_COMMITTED_SNAPSHOT 为 ON(我认为这对于这个问题很重要)。

我创建了一个内存表类型(与 technet 博客 SQL Server 2014 In Memory OLTP: Memory-Optimized Table Types and Table Variables 的示例非常相似)，并且我有几个内存表。

在查询本身中，我在常规内存表和内存表类型之间有一个联接，充当过滤器，当我执行查询时，我收到此错误消息:“访问内存优化表的查询使用 READ COMMITTED 隔离级别，当数据库选项 READ_COMMITTED_SNAPSHOT 设置为 ON 时，无法访问基于磁盘的表。使用表提示(例如WITH (SNAPSHOT))为内存优化表提供支持的隔离级别。”

当我写这个问题时，我正在删除 READ_COMMITTED_SNAPSHOT ON，但问题仍然存在，如果我创建了内存中数据类型，并且这种特定类型将“永远不会溢出到磁盘”，正如博客所说，为什么服务器“看到”它是一个“磁盘表”？

为了澄清问题，我尝试仅使用 In-Mem 表进行连接，并且它有效，一旦表类型进入，我就收到错误。

更新:当我删除 READ_COMMITTED_SNAPSHOT(现在已关闭)时，查询可以工作，但现在我失去了多版本/无锁定/速度，我想听到另一个解决方案。

谢谢

重现步骤。

使用内存优化文件组创建数据库

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 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 

以下工作没有错误

ALTER DATABASE [MemOptimized] 
SET READ_COMMITTED_SNAPSHOT OFF WITH ROLLBACK IMMEDIATE

GO

USE MemOptimized

EXEC P 

但是这个

ALTER DATABASE [MemOptimized] 
SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE

GO

USE MemOptimized

EXEC P 

给出上面详细说明的错误

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

我也看到了这个。

启用 RCSI 后，将内存表类型的两个实例连接在一起时，默认读取已提交级别的自动提交事务可以正常工作。

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 

连接两个不同的“正常”内存优化表也可以正常工作，无需任何提示。

此外，将空的内存优化表类型连接到普通的内存优化表效果很好。

DECLARE @t [dbo].[tType];

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

但反之则不然。只要内存表类型实例至少包含一行，那么将其连接到内存表(空或其他)就会引发错误。

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).

解决方案很简单，错误消息中已指出。只需添加表提示 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

或者一个不太精细的解决方案是

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

据我所知，这些实际上都没有改变语义，并且在某些情况下省略提示的能力只是一种编程便利。

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

我不确定为什么内存表类型不同的混合会导致此特定错误消息。我认为这只是 CTP 的产物，并且在 RTM 中要么允许组合，要么更新错误消息和文档以不仅仅引用基于磁盘的表。