内存中的用户定义表，不在内存中？

Question

我正在使用SQL Server 2014 CTP2，启用了READ_COMMITTED_SNAPSHOT（我认为这对问题很重要）。

我创建了一个类似于technet博客的示例（SQL Server 2014 In Memory OLTP: Memory-Optimized Table Types and Table Variables）的In-Memory表类型，并且有多个In-Memory表。

在查询中，我将常规In-Memory表与充当过滤器的In-Memory表类型进行了连接，当执行查询时，我会收到此错误消息：“使用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 

出现了上述详细的错误。

消息41359，级别16，状态0，过程P，行62 访问内存优化表时使用READ COMMITTED隔离级别的查询，当数据库选项READ_COMMITTED_SNAPSHOT设置为ON时，无法访问基于磁盘的表。对内存优化表使用支持的隔离级别，例如WITH（SNAPSHOT）表提示。

- Avishai.M

---

您是否提供了支持内存优化表的隔离级别，例如错误消息建议的WITH（SNAPSHOT）等表提示？ - Robert Harvey

我认为移除READ_COMMITTED_SNAPSHOT并不等同于添加WITH（SNAPSHOT）。 - Robert Harvey

“常规内存表”实际上并不是内存表。它是基于磁盘的，因此我认为错误信息指的是那个基于磁盘的表，而不是您的内存表类型。 - Mikael Eriksson

“在常规内存表和内存表类型之间进行连接”是什么意思？我认为那里的“常规”表是2014年之前可用的类型的表变量。 - Mikael Eriksson

@MikaelEriksson 在 CTP2 中，它们有一种内存优化的用户定义表，我没有使用过 SQL 2012，所以我不知道内存优化的用户定义表是否可用，这种类型应该只存在于内存中，就像普通内存表中的 schema_and_data 和 schema_only 的区别一样。 - Avishai.M

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 

只要在内存表类型实例中至少包含一行，则将其连接到（空或其他）内存表会引发错误，但反之不成立。

使用 READ COMMITTED 隔离级别访问内存优化表的查询不能访问磁盘基础表，当数据库选项 READ_COMMITTED_SNAPSHOT 被设置为 ON 时。为内存优化表提供受支持的隔离级别，例如 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 

这将把内存中优化表的隔离级别设置为SNAPSHOT（就像您在每个内存中优化表中包含WITH(SNAPSHOT)提示一样）来源。据我所知，这两者实际上都不会改变语义，而在某些情况下省略提示的能力只是一种编程便利。

对于自动提交事务，内存优化表的隔离级别READ COMMITTED被隐式映射为SNAPSHOT。因此，如果TRANSACTION ISOLATION LEVEL会话设置被设置为READ COMMITTED，则在访问内存优化表时不需要通过表提示指定隔离级别。 来源 内存优化表支持自动提交事务的隔离级别READ COMMITTED。READ COMMITTED不支持显式或隐式用户事务。仅当查询不访问任何基于磁盘的表时，内存优化表才支持隔离级别READ_COMMITTED_SNAPSHOT。来源

我不确定为什么这种混合使用内存表类型会导致这个特定的错误信息。我猜想这只是测试版的一个副作用，在正式版本中，这种组合可能会被允许，或者错误消息和文档将被更新，不仅仅是针对基于磁盘的表。

- Martin Smith

---

连接项目链接 - Martin Smith

Answer 2

您看到的错误信息是不正确的。我们将内存优化的表变量处理为磁盘型表，这是错误的。在SQL Server 2014 RTM CU1中修复了此问题。 请注意，数据库选项READ_COMMITTED_SNAPSHOT不适用于内存优化的表。它仅适用于磁盘型表。内存优化的表始终是多版本的。

- Jos de Bruijn - MSFT

---

在这种情况下，这个连接项可以关闭吗？https://connect.microsoft.com/SQLServer/feedback/details/816739/confusing-error-message-when-joining-memory-optimized-table-to-memory-optimized-table-type-at-rcsi - Martin Smith