In-Memory 用户定义表，不在内存中? [英] In-Memory user defined table, not in memory?

问题描述

我使用的是 SQL Server 2014 CTP2，READ_COMMITTED_SNAPSHOT ON(我认为这对问题很重要).

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

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

我在写这个问题时删除了 READ_COMMITTED_SNAPSHOT ON，但问题仍然存在，如果我创建了一个 In-Memory 数据类型，并且这个特定类型将永远不会溢出到磁盘"，正如博客所说，为什么服务器把它看"为磁盘表"?

为了解决问题，我尝试仅使用 In-Mem 表进行连接，并且可以正常工作，只要输入表类型，我就会收到错误消息.

更新:当我删除 READ_COMMITTED_SNAPSHOT(现在已关闭)时，查询有效，但现在我丢失了多版本/无锁定/速度，我想听听另一种解决方案.

谢谢

重现步骤.

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

CREATE DATABASE MemOptimized走更改数据库 MemOptimized添加文件组 mofg包含 MEMORY_OPTIMIZED_DATA走更改数据库 MemOptimized添加文件 (NAME = N'mofg',FileName = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MemOptimized.ndf')到文件组 mofg

创建一些对象

CREATE TYPE [dbo].[tType] AS TABLE([C] [int] 非空索引 ix 非聚集散列 (C) WITH (BUCKET_COUNT = 8)) WITH ( MEMORY_OPTIMIZED = ON )创建表 [dbo].[tTable] ([C] [int] NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 8)) WITH ( MEMORY_OPTIMIZED = ON )插入 [dbo].[tTable] 值 (1)走创建过程作为声明@t [dbo].[tType]插入@t价值观 (1)选择 *从 [dbo].[tTable] t内连接@tON [@t].C = t.C

以下工作没有错误

ALTER DATABASE [MemOptimized]SET READ_COMMITTED_SNAPSHOT OFF WITH ROLLBACK IMMEDIATE走使用内存优化执行程序

但是这个

ALTER DATABASE [MemOptimized]SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE走使用内存优化执行程序

给出上面详述的错误

<块引用>

消息 41359，级别 16，状态 0，过程 P，第 62 行使用 READ COMMITTED 隔离访问内存优化表级别，当数据库选项时无法访问基于磁盘的表READ_COMMITTED_SNAPSHOT 设置为 ON.提供受支持的隔离使用表提示的内存优化表的级别，例如 WITH(快照).

解决方案

我也看到了.

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

DECLARE @t1 [dbo].[tType]声明@t2 [dbo].[tType]插入@t1 值(1)；插入@t2 值(1)；选择 *来自@t1加入@t2ON [@t1].C = [@t2].C

加入两个不同的普通"内存优化表也可以正常工作，没有任何提示.

另外将一个空的内存优化表类型连接到一个普通的内存优化表可以正常工作.

DECLARE @t [dbo].[tType];选择 *从 [dbo].[tTable] t内连接@tON [@t].C = t.C

但反过来就不对了.只要内存表类型实例包含至少一行，然后将其连接到内存表(空或其他)中就会引发错误.

<块引用>

使用 READ COMMITTED 访问内存优化表的查询隔离级别，当数据库无法访问基于磁盘的表时选项 READ_COMMITTED_SNAPSHOT 设置为 ON.提供支持使用表提示的内存优化表的隔离级别，比如 WITH (SNAPSHOT).

解决方法很简单，在错误消息中有所指示.只需添加表提示 WITH (SNAPSHOT)

DECLARE @t [dbo].[tType]插入@t价值观 (1)选择 *从 [dbo].[tTable] t WITH(SNAPSHOT)内连接@tON [@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 会话设置设置为 READCOMMITTED，不需要通过指定隔离级别访问内存优化表时的表提示.来源

内存优化支持隔离级别READ COMMITTED具有自动提交事务的表.不支持 READ COMMITTED具有显式或隐式用户交易.隔离级别内存优化表支持 READ_COMMITTED_SNAPSHOT自动提交事务并且仅当查询未访问任何基于磁盘的表.来源

我不确定为什么这种不同的内存表类型混合会导致此特定错误消息.我认为这只是作为 CTP 的一个人工制品，在 RTM 上，要么允许组合，要么更新错误消息和文档，以不仅仅引用基于磁盘的表.

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

解决方案

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.

这篇关于In-Memory 用户定义表，不在内存中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！