主页 / dba / 问题 / 233689
Accepted
Hannah Vernon
Asked: 2019-04-02 13:06:57 +0800 CST

对全局临时表进行逻辑读取，但不在会话级临时表上

  • 772

考虑以下简单的 MCVE：

SET STATISTICS IO, TIME OFF;
USE tempdb;

IF OBJECT_ID(N'tempdb..#t1', N'U') IS NOT NULL DROP TABLE #t1;
CREATE TABLE #t1
(
    r int NOT NULL
);

IF OBJECT_ID(N'tempdb..##t1', N'U') IS NOT NULL DROP TABLE ##t1;
CREATE TABLE ##t1
(
    r int NOT NULL
);

IF OBJECT_ID(N'dbo.s1', N'U') IS NOT NULL DROP TABLE dbo.s1;
CREATE TABLE dbo.s1 
(
    r int NOT NULL
        PRIMARY KEY CLUSTERED
);

INSERT INTO dbo.s1 (r)
SELECT TOP(10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.syscolumns sc1
    CROSS JOIN sys.syscolumns sc2;
GO

当我运行以下插入时，插入#t1显示临时表没有统计 I/O。但是，插入##t1 确实会显示临时表的 stats I/O。

SET STATISTICS IO, TIME ON;
GO

INSERT INTO #t1 (r)
SELECT r
FROM dbo.s1;

统计输出：

SQL Server 解析和编译时间：
   CPU 时间 = 0 毫秒，经过时间 = 1 毫秒。
表's1'。扫描计数 1，逻辑读取 19，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

 SQL Server 执行时间：
   CPU 时间 = 16 毫秒，经过时间 = 9 毫秒。

（10000 行受影响）
INSERT INTO ##t1 (r)
SELECT r
FROM dbo.s1;

SQL Server 解析和编译时间：
   CPU 时间 = 0 毫秒，经过时间 = 1 毫秒。
表'##t1'。扫描计数 0，逻辑读取 10016，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。
表's1'。扫描计数 1，逻辑读取 19，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

 SQL Server 执行时间：
   CPU 时间 = 47 毫秒，经过时间 = 45 毫秒。

（10000 行受影响）

当我只插入时，为什么 ##temp 表上有这么多读取？

sql-server sql-server-2016

1 个回答

  1. Best Answer

    INSERT INTO使用和全局临时表时未使用最小日志记录

    通过使用在全局临时表中插入一百万行INSERT INTO

    INSERT INTO ##t1 (r)
SELECT top(1000000) s1.r
FROM dbo.s1
CROSS APPLY  dbo.s1 S2;

    SELECT * FROM fn_dblog(NULL, NULL)在执行上述查询时运行时，将返回约 1M 行。

    在此处输入图像描述

    每行一个LOP_INSERT_ROW操作 + 其他日志数据。

    本地临时表上的相同插入

    INSERT INTO #t1 (r)
SELECT top(1000000) s1.r
FROM dbo.s1
CROSS APPLY  dbo.s1 S2;

    最多只能返回 700 行 SELECT * FROM fn_dblog(NULL, NULL)

    在此处输入图像描述

    最少的日志记录

    通过使用在全局临时表中插入一百万行SELECT INTO

    SELECT top(1000000) s1.r
INTO ##t2
FROM dbo.s1
CROSS APPLY  dbo.s1 S2;

    在此处输入图像描述

    SELECT INTO具有 10k 条记录的全局临时表

    SELECT s1.r
INTO ##t2
FROM dbo.s1;

    时间和 IO 统计

    SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
Table 's1'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 10 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

    基于this blogpost，我们可以添加TABLOCK以启动堆表上的最小日志记录

    INSERT INTO ##t1 WITH(TABLOCK) (r)
SELECT   s1.r
FROM dbo.s1

    低逻辑读取

    Table 's1'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(10000 rows affected)

    @PaulWhite 关于如何在临时表上实现最少日志记录的部分答案

    不可以。本地临时表 (#temp) 是创建会话的专用表，因此不需要表锁定提示。在 tempdb 中创建的全局临时表 (##temp) 或常规表 (dbo.temp) 需要表锁定提示，因为可以从多个会话访问这些表。

    创建一个常规表来测试这个：

    CREATE TABLE dbo.bla
(
    r int NOT NULL 
);

    用 1M 条记录填充它

    INSERT INTO bla 
SELECT   top(1000000)s1.r
FROM dbo.s1
CROSS APPLY  dbo.s1 S2;

    此表上 >1M 逻辑读取

    Table 's1'. Scan count 17, logical reads 155, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'bla'. Scan count 0, logical reads 1001607, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Paul White 的回答解释了全局临时表上报告的逻辑读取

    通常，当插入没有被最低限度地记录时，会报告目标表的逻辑读取。

    这些逻辑读取与在现有结构中查找位置以添加新行相关联。最少记录的插入使用批量加载机制，它分配全新的页面/范围（因此不需要以相同的方式读取目标结构）。

    结论

    结论INSERT INTO是不能使用最少的日志记录，当与全局临时表/普通表结合使用时，导致在 tempdb 的日志文件中单独记录每个插入的行。而本地临时表/ SELECT INTO/INSERT INTO ... WITH(TABLOCK)能够使用最少的日志记录。

    • 11

相关问题