sql-server - SQL Server 内存优化表 - 与临时表相比性能较差

Question

我正在尝试使用经典临时表对 Microsoft SQL Server 2016 中的内存优化表进行基准测试。

SQL Server 版本:

Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)  Mar 18 2018 09:11:49   
Copyright (c) Microsoft Corporation  
Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 17134: ) (Hypervisor) 

我正在执行此处描述的步骤:https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/faster-temp-table-and-table-variable-by-using-memory-optimization?view=sql-server-ver15 .

CrudTest_TempTable 1000, 100, 100
go 1000

对比

CrudTest_memopt_hash 1000, 100, 100
go 1000

这个测试的作用是什么？

• 1000 个插入
• 100 次随机更新
• 100 个随机删除

这会重复 1000 次。

第一个使用经典临时表的存储过程运行大约需要 6 秒。

第二个存储过程至少需要 15 秒，并且通常会出错:

Beginning execution loop

Msg 3998, Level 16, State 1, Line 3
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

Msg 701, Level 17, State 103, Procedure CrudTest_memopt_hash, Line 16 [Batch Start Line 2]
There is insufficient system memory in resource pool 'default' to run this query.

我做了以下优化(在情况变得更糟之前):

• 哈希索引同时包含 Col1 和 SpidFilter

• 在单个事务中完成所有操作可以使其运行速度更快(但是如果没有它也可以运行)

• 我正在生成随机 ID - 如果没有它，每次迭代的记录都会在同一个存储桶中

我还没有创建 native 编译的 SP，因为我的结果很糟糕。

我的机器上有足够的可用 RAM，SQL Server 可以使用它 - 在不同的场景中它会分配大量内存，但在这个测试用例中它只是出错。

对我来说，这些结果意味着内存优化表无法取代临时表。你有类似的结果还是我做错了什么？

使用临时表的代码是:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

DROP PROCEDURE IF EXISTS CrudTest_TempTable;
GO

CREATE PROCEDURE CrudTest_TempTable 
    @InsertsCount INT, @UpdatesCount INT, @DeletesCount INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRAN;

    CREATE TABLE #tempTable  
    (  
        Col1 INT NOT NULL PRIMARY KEY CLUSTERED,  
        Col2 NVARCHAR(4000),
        Col3 NVARCHAR(4000),
        Col4 DATETIME2,
        Col5 INT NOT NULL
    ); 

    DECLARE @cnt INT = 0;
    DECLARE @currDate DATETIME2 = GETDATE();


    WHILE @cnt < @InsertsCount
    BEGIN
        INSERT INTO #tempTable (Col1, Col2, Col3, Col4, Col5)
        VALUES (@cnt, 
        'sdkfjsdjfksjvnvsanlknc kcsmksmk ms mvskldamvks mv kv al kvmsdklmsdkl mal mklasdmf kamfksam kfmasdk mfksamdfksafeowa fpmsad lak',
        'msfkjweojfijm  skmcksamepi  eisjfi ojsona npsejfeji a piejfijsidjfai  spfdjsidjfkjskdja kfjsdp fiejfisjd pfjsdiafjisdjfipjsdi s dfipjaiesjfijeasifjdskjksjdja sidjf pajfiaj pfsdj pidfe',
        @currDate, 100);

        SET @cnt = @cnt + 1;
    END
    SET @cnt = 0;

    WHILE @cnt < @UpdatesCount
    BEGIN
        UPDATE #tempTable SET Col5 = 101 WHERE Col1 = cast ((rand() * @InsertsCount) as int);

        SET @cnt = @cnt + 1;
    END
    SET @cnt = 0;

    WHILE @cnt < @DeletesCount
    BEGIN
        DELETE FROM #tempTable WHERE Col1 = cast ((rand() * @InsertsCount) as int);

        SET @cnt = @cnt + 1;
    END

    COMMIT;
END
GO

内存测试中使用的对象是:

DROP PROCEDURE IF EXISTS CrudTest_memopt_hash;
GO

DROP SECURITY POLICY IF EXISTS tempTable_memopt_hash_SpidFilter_Policy;
GO

DROP TABLE IF EXISTS tempTable_memopt_hash;
GO

DROP FUNCTION IF EXISTS fn_SpidFilter;
GO

CREATE FUNCTION fn_SpidFilter(@SpidFilter smallint)  
    RETURNS TABLE  
    WITH SCHEMABINDING , NATIVE_COMPILATION  
AS  
    RETURN  
        SELECT 1 AS fn_SpidFilter  
            WHERE @SpidFilter = @@spid;
GO

CREATE TABLE tempTable_memopt_hash  
(  
    Col1 INT NOT NULL,
    Col2 NVARCHAR(4000),
    Col3 NVARCHAR(4000),
    Col4 DATETIME2,
    Col5 INT NOT NULL,

    SpidFilter  SMALLINT    NOT NULL   DEFAULT (@@spid),  
    INDEX ix_SpidFiler NONCLUSTERED (SpidFilter), 
    INDEX ix_hash HASH (Col1, SpidFilter) WITH (BUCKET_COUNT=100000),
    CONSTRAINT CHK_SpidFilter CHECK ( SpidFilter = @@spid )
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
GO


CREATE SECURITY POLICY tempTable_memopt_hash_SpidFilter_Policy  
    ADD FILTER PREDICATE dbo.fn_SpidFilter(SpidFilter)  
    ON dbo.tempTable_memopt_hash  
    WITH (STATE = ON); 
GO

使用它们的存储过程是:

CREATE PROCEDURE CrudTest_memopt_hash 
    @InsertsCount INT, @UpdatesCount INT, @DeletesCount int
AS
BEGIN

    SET NOCOUNT ON;
    BEGIN TRAN;

    DECLARE @cnt INT = 0;
    DECLARE @currDate DATETIME2 = GETDATE();

    DECLARE @IdxStart INT = CAST ((rand() * 1000) AS INT);

    WHILE @cnt < @InsertsCount
    BEGIN
        INSERT INTO tempTable_memopt_hash(Col1, Col2, Col3, Col4, Col5)
        VALUES (@IdxStart + @cnt, 
        'sdkfjsdjfksjvnvsanlknc kcsmksmk ms mvskldamvks mv kv al kvmsdklmsdkl mal mklasdmf kamfksam kfmasdk mfksamdfksafeowa fpmsad lak',
        'msfkjweojfijm  skmcksamepi  eisjfi ojsona npsejfeji a piejfijsidjfai  spfdjsidjfkjskdja kfjsdp fiejfisjd pfjsdiafjisdjfipjsdi s dfipjaiesjfijeasifjdskjksjdja sidjf pajfiaj pfsdj pidfe',
        @currDate, 100);

        SET @cnt = @cnt + 1;
    END
    SET @cnt = 0;

    WHILE @cnt < @UpdatesCount
    BEGIN
        UPDATE tempTable_memopt_hash 
        SET Col5 = 101 
        WHERE Col1 = @IdxStart + cast ((rand() * @InsertsCount) as int);

        SET @cnt = @cnt + 1;
    END
    SET @cnt = 0;

    WHILE @cnt < @DeletesCount
    BEGIN
        DELETE FROM tempTable_memopt_hash 
        WHERE Col1 = @IdxStart + cast ((rand() * @InsertsCount) as int);

        SET @cnt = @cnt + 1;
    END

    DELETE FROM tempTable_memopt_hash;
    COMMIT;
END
GO

索引统计:

table   index   total_bucket_count  empty_bucket_count  empty_bucket_percent    avg_chain_length    max_chain_length
[dbo].[tempTable_memopt_hash]   PK__tempTabl__3ED0478731BB5AF0  131072  130076  99  1   3

更新

我包含了最终的测试用例和用于创建过程、表等的 SQL 代码。我已经在空数据库上执行了测试。

SQL 代码:https://pastebin.com/9K6SgAqZ

测试用例:https://pastebin.com/ckSTnVqA

我的最后一次运行如下所示(临时表是表中最快的，但我能够使用内存优化表变量实现最快的时间):

Start CrudTest_TempTable 2019-11-18 10:45:02.983
Beginning execution loop
Batch execution completed 1000 times.
Finish CrudTest_TempTable 2019-11-18 10:45:09.537
Start CrudTest_SpidFilter_memopt_hash 2019-11-18 10:45:09.537
Beginning execution loop
Batch execution completed 1000 times.
Finish CrudTest_SpidFilter_memopt_hash 2019-11-18 10:45:27.747
Start CrudTest_memopt_hash 2019-11-18 10:45:27.747
Beginning execution loop
Batch execution completed 1000 times.
Finish CrudTest_memopt_hash 2019-11-18 10:45:46.100
Start CrudTest_tableVar 2019-11-18 10:45:46.100
Beginning execution loop
Batch execution completed 1000 times.
Finish CrudTest_tableVar 2019-11-18 10:45:47.497

Answer 1

恕我直言，OP中的测试无法显示内存优化表的优势因为这些表的最大优点是它们是lock-and-latch free，这意味着您的更新/插入/删除 根本不采用允许并发更改这些表的锁。

但是所做的测试根本不包括并发更改，显示的代码在一个 session 中进行所有更改。

另一个观察结果:表上定义的哈希索引是错误的，因为您仅在一列上搜索，并且哈希索引是在上定义的>两列。两列上的哈希索引意味着哈希函数应用于两个参数，但您仅在一列上搜索，因此无法使用哈希索引。

Do you think by using mem opt tables I can get performance improvements over temp tables or is it just for limiting IO on tempdb?

内存优化表不应该替代临时表，正如已经提到的，您将看到高度并发的利润OLTP 环境，虽然您猜测临时表仅对您的 session 可见，但根本没有并发性。

Eliminate latches and locks. All In-Memory OLTP internal data structures are latch- and lock-free. In-Memory OLTP uses a new multi-version concurrency control (MVCC) to provide transaction consistency. From a user standpoint, it behaves in a way similar to the regular SNAPSHOT transaction isolation level; however, it does not use locking under the hood. This schema allows multiple sessions to work with the same data without locking and blocking each other and improves the scalability of the system allowing fully utilize modern multi-CPU/multi-core hardware.

引用的书:Dmitri Korotkevitch 的《Pro SQL Server Internals》

What do you think about the title "Faster temp table and table variable by using memory optimization"

我打开这篇文章并看到这些示例(按照它们在文章中的顺序)

• A.内存优化表变量的基础知识
• B.场景:替换全局tempdb##table
• C.场景:替换session tempdb #table

A.我仅在表变量包含很少行的情况下才使用它们。为什么我要关心这几行？

B.替换 global tempdb ##table 。我只是根本不使用它们。

C.替换session tempdb #table。正如已经提到的，session tempdb #table 对任何其他 session 都是不可见的，那么有什么好处呢？数据不写入磁盘吗？如果您确实遇到 tempdb 问题，您是否应该为您的 tempdb 考虑最快的 SSD 磁盘？从 2014 年开始，即使在批量插入的情况下，tempdb对象也不一定会进入磁盘，无论如何，我什至在我的设备上启用了RCSI数据库，并且 tempdb 没有问题。