主页 / dba / 问题 / 300289
Accepted
Mike S
Asked: 2021-09-29 12:02:43 +0800 CST

为什么 SQL Server 跟踪标志 715 的行为与 TABLOCK 查询提示不同？

  • 772

这是我的理解，基于众多来源（包括这个来源：https ://techcommunity.microsoft.com/t5/sql-server/migrating-sap-workloads-to-sql-server-just-got-2-5x-faster /ba-p/384910 ) SQL Server 跟踪标志 715 应该等同于 TABLOCK 查询提示——不同之处在于 TF715 可以在会话和全局级别设置，而 TABLOCK 只能用作查询提示。

我的问题是，我在 SQL Servers 2016 和 2019 开发版上都以简单的恢复模型进行了测试，无论我使用哪种服务器，TABLOCK 和 TF715 都得到了不同的结果。TABLOCK 按预期工作，而 TF715 没有。具体来说，TABLOCK 告诉 SQL Server 为批量操作取出整个表锁，并最少记录操作。

考虑以下代码。这会将 10,000 行插入到堆中。

DROP TABLE IF EXISTS Test
GO

CREATE TABLE Test (t VARCHAR(100))
GO

INSERT INTO Test (t)
SELECT TOP (10000)
    x = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM       sys.columns AS s1
CROSS JOIN sys.columns AS s2

现在，如果您运行下一个代码块，您可以看到它记录了 10,000 行。

SELECT 
    [Fully Logged Rows] = COUNT_BIG(*) 
FROM sys.fn_dblog(NULL, NULL) AS FD
WHERE
    FD.Operation = N'LOP_INSERT_ROWS'
    AND FD.Context = N'LCX_HEAP'
    AND FD.AllocUnitName = N'dbo.Test';

好的，所以现在如果我们使用 TABLOCK 提示重新执行此操作，您可以看到它变成了最小记录操作（即，完全记录的行数 = 0）。

DROP TABLE IF EXISTS Test
GO

CREATE TABLE Test (t VARCHAR(100))
GO

INSERT INTO Test WITH (TABLOCK) (t)
SELECT TOP (10000)
    x = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM       sys.columns AS s1
CROSS JOIN sys.columns AS s2

--0 Fully Logged Rows
SELECT 
    [Fully Logged Rows] = COUNT_BIG(*) 
FROM sys.fn_dblog(NULL, NULL) AS FD
WHERE
    FD.Operation = N'LOP_INSERT_ROWS'
    AND FD.Context = N'LCX_HEAP'
    AND FD.AllocUnitName = N'dbo.Test';

但是，当我尝试启用 TF715（并取消 TABLOCK 提示）时，我仍然会得到 10,000 行完全记录的行，而我希望进行最少记录的操作。

--Enable TF715 on both a session and global level to cover all bases...
DBCC TRACEON(715)
GO
DBCC TRACEON(715, -1)
GO
DBCC TRACESTATUS
GO

DROP TABLE IF EXISTS Test
GO

CREATE TABLE Test (t VARCHAR(100))
GO

INSERT INTO Test (t)
SELECT TOP (10000)
    x = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM       sys.columns AS s1
CROSS JOIN sys.columns AS s2

--10000 Fully Logged Rows
SELECT 
    [Fully Logged Rows] = COUNT_BIG(*) 
FROM sys.fn_dblog(NULL, NULL) AS FD
WHERE
    FD.Operation = N'LOP_INSERT_ROWS'
    AND FD.Context = N'LCX_HEAP'
    AND FD.AllocUnitName = N'dbo.Test';

DBCC TRACEOFF(715)
GO
DBCC TRACEOFF(715, -1)
GO
DBCC TRACESTATUS
GO

我在看什么？我不需要重新启动 SQL Server 来通过 DBCC 启用 TF，对吗？

先感谢您。

sql-server database-internals

1 个回答

  1. Best Answer

    Parikshit Savjani 引用的文章中的重要引述（强调添加）：

    TF 715 可以设置在实例级别（全局）或会话级别，这使得 TABLOCK 能够将批量加载操作批量加载到没有非聚集索引的堆中。启用此跟踪标志后，在将数据批量复制到没有非聚集索引的堆中时，批量加载操作默认获取批量更新 (BU) 锁。批量更新 (BU) 锁允许多个线程同时将数据批量加载到同一个表中，同时防止其他不是批量加载数据的进程访问该表。该行为类似于用户在执行批量加载时显式指定 TABLOCK 提示，或者当 sp_tableoption 'table lock on bulk load' 为给定的表打开时，但是启用此 TF 会使此行为默认，而不进行任何查询更改或数据库更改。

    跟踪标志 715为在指定 TABLOCK 时支持 BU锁的批量加载方法启用 BU 锁。

    除 INSERT...SELECT外，所有批量加载方法都支持 BU 锁。

    其他批量加载方法的示例包括：

    • SSIS SQL Server 和 OLEDB 数据目标
    • bcp
    • 批量插入
    • ADO.NET 中的 SQLBulkCopy
    • OLE DB 和 SQL Server Native Client ODBC 库中的 IRowsetFastload

    将 TF715 视为始终设置“批量加载表锁定”可能会有所帮助：

    价值 描述
    批量加载表锁 禁用时（默认设置），它会导致用户定义表上的批量加载进程获取行锁。启用后，它会导致用户定义表上的批量加载进程获得批量更新锁。

    TF 715 的实际效果在很多情况下与指定 TABLOCK类似，但并不完全相同。特别是，BU 锁确保批量加载进程对表具有独占访问权。

    批量导入优化是一组功能。在不同的情况下，您可以获得一些好处，但不能获得其他好处。

    在您的示例中，您没有获得 BU 锁，因为 INSERT...SELECT 不支持它们。TF715 仅对支持该选项的批量加载方法启用 BU 锁定。因此，TF715在您的示例中无效。

    当您指定 TABLOCK 时，引擎有一个可接受的保证，即批量加载具有对表的独占访问权限，因此允许最少的日志记录。

    上述讨论仅适用于堆表目标。加载聚簇表可以受益于相同的底层机制 ( RowsetBulk ) 以及FastLoadContext，它不需要 TABLOCK 或 BU 锁来启用最少的日志记录。

    • 2

相关问题