當我們在 SQL Server 中處理大規模數據插入（INSERT）操作時，往往會因為記錄量龐大、日志寫入多、并發競爭等原因導致性能下降。TABLOCK 提示（Hint）是 SQL Server 提供的一種有效方式，可以通過減少日志記錄和允許并行加載來提升插入性能。下面，我們就來詳細討論它的原理、優勢及使用方式，并給出一個類似的示例供參考。

什么是 TABLOCK Hint

在執行 INSERT、UPDATE 或 DELETE 等操作時，TABLOCK 會在目標表上獲取 表級鎖，并對其施加一段時間的 模式修復鎖 (Sch-M)。這意味著在整個操作執行期間，其他事務無法修改該表的架構或進行并發寫入。雖然聽起來會犧牲一定的并發能力，但對于一次性地批量導入或更新大量數據的場景，該鎖策略可以激活更多的優化：

• 最小日志記錄
  可以顯著減少日志寫入量，尤其在批量插入時不再逐行寫入日志，而是采用批量式記錄的方式。
• 并行化
  在表被完全鎖定后，SQL Server 可以嘗試使用多個線程并行插入數據，從而縮短整體執行時間。

使用場景

一般來說，TABLOCK 適合以下場景：

• 需要一次性插入大量數據，并且頻繁的小批次插入不多。
• 能夠接受在插入過程中暫時鎖定目標表（如批處理或離線數據導入）。
• 提高性能速度比表可用性更加重要的批量場景，例如 ETL 流程、數據倉庫加載 或者 大規模臨時表作業。
• 需要利用 SQL Server 并行處理能力，盡快完成數據插入。

示例：在 AdventureWorks2022 中使用 TABLOCK

以下示例展示了如何在插入數據時應用 TABLOCK，并與不使用 TABLOCK 的情況進行對比。假設我們有一張目標表 Sales.SalesOrderDetailBulk 用來存儲大量明細數據，源表為 Sales.SalesOrderDetail。

創建或清理目標表

-- 如果存在測試表，先刪除  
IF OBJECT_ID('SalesOrderDetailTest', 'U') IS NOT NULL  
    DROPTABLE Sales.SalesOrderDetailTest;  
GO  

-- 創建測試表，模仿原始表結構  
CREATETABLE SalesOrderDetailTest  
(  
    SalesOrderID INTNOTNULL,  
    SalesOrderDetailID INTIDENTITY(1,1) PRIMARY KEY,  
    CarrierTrackingNumber NVARCHAR(25) NULL,  
    OrderQty SMALLINTNOTNULL,  
    ProductID INTNOTNULL,  
    SpecialOfferID INTNOTNULL,  
    UnitPrice MONEY NOTNULL,  
    UnitPriceDiscount MONEY NOTNULL,  
    LineTotal AS (OrderQty * UnitPrice * (1 - UnitPriceDiscount)) PERSISTED,  
);  
GO  

-- 批量插入測試數據的存儲過程  
CREATEORALTERPROCEDURE GenerateSalesOrderDetailTestData  
    @NumberOfRecords INT = 100000
AS
BEGIN
    SET NOCOUNT ON;  

    -- 使用公共表表達式(CTE)生成測試數據  
    ;WITH NumberedRows AS (  
        SELECT TOP (@NumberOfRecords)  
            ROW_NUMBER() OVER (ORDERBY (SELECTNULL)) ASRowNum
        FROM sys.objects o1  
        CROSSJOIN sys.objects o2  
    )  
    INSERTINTO SalesOrderDetailTest  
    (  
        SalesOrderID,  
        CarrierTrackingNumber,  
        OrderQty,  
        ProductID,  
        SpecialOfferID,  
        UnitPrice,  
        UnitPriceDiscount  
    )  
    SELECT   
        -- 隨機生成 SalesOrderID  
        ABS(CHECKSUM(NEWID()) % 50000) + 1AS SalesOrderID,  

        -- 隨機生成跟蹤號  
        'TRK' + RIGHT('00000' + CAST(ABS(CHECKSUM(NEWID()) % 99999) ASVARCHAR(5)), 5) AS CarrierTrackingNumber,  

        -- 隨機訂單數量  
        ABS(CHECKSUM(NEWID()) % 10) + 1AS OrderQty,  

        -- 隨機產品ID（假設產品ID范圍）  
        ABS(CHECKSUM(NEWID()) % 1000) + 1AS ProductID,  

        -- 隨機特殊優惠ID  
        ABS(CHECKSUM(NEWID()) % 10) + 1AS SpecialOfferID,  

        -- 隨機單價  
        ROUND(ABS(CHECKSUM(NEWID())) % 1000 + 10.00, 2) AS UnitPrice,  

        -- 隨機折扣  
        ROUND(ABS(CHECKSUM(NEWID())) % 20 / 100.00, 2) AS UnitPriceDiscount  
    FROM NumberedRows;  
END
GO

-- 執行存儲過程生成測試數據  
EXEC GenerateSalesOrderDetailTestData @NumberOfRecords = 500000;  
GO  

-- 創建索引以提高查詢性能  
CREATE NONCLUSTERED INDEX IX_SalesOrderDetailTest_ProductID   
ON SalesOrderDetailTest (ProductID);  

CREATE NONCLUSTERED INDEX IX_SalesOrderDetailTest_SalesOrderID   
ON SalesOrderDetailTest (SalesOrderID);

-- 如果已存在，先刪除后再創建
IF OBJECT_ID('SalesOrderDetailBulk', 'U') IS NOT NULL
    DROPTABLE SalesOrderDetailBulk;
GO

-- 創建一張用于演示的表，結構與 Sales.SalesOrderDetail 相似
CREATETABLE SalesOrderDetailBulk
(
    SalesOrderID       INT,
    SalesOrderDetailID INT,
    CarrierTrackingNumber NVARCHAR(25),
    OrderQty           SMALLINT,
    ProductID          INT,
    UnitPrice          MONEY,
    UnitPriceDiscount  MONEY,
    LineTotal          AS (OrderQty * UnitPrice)
);
GO

不使用 TABLOCK 的插入操作

-- 第一次插入：不使用 TABLOCK
SETSTATISTICSTIMEON;  -- 打開時間統計
INSERTINTO SalesOrderDetailBulk
    (SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty,
     ProductID, UnitPrice, UnitPriceDiscount)
SELECT
    SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty,
    ProductID, UnitPrice, UnitPriceDiscount
FROM SalesOrderDetailTest
WHERE SalesOrderID < 50000;  -- 只插入部分數據用于演示
SETSTATISTICSTIMEOFF;     -- 關閉時間統計

?

觀察執行結果，記錄 CPU 時間、總持續時間（Elapsed Time）。

帶有 TABLOCK 提示的插入

為方便比較，先清空目標表，然后使用 TABLOCK 提示：

TRUNCATE TABLE SalesOrderDetailBulk;  -- 清空目標表

SETSTATISTICSTIMEON;
INSERTINTO SalesOrderDetailBulk WITH (TABLOCK)
    (SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty,
     ProductID, UnitPrice, UnitPriceDiscount)
SELECT
    SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty,
    ProductID, UnitPrice, UnitPriceDiscount
FROM SalesOrderDetailTest
WHERE SalesOrderID < 50000;
SETSTATISTICSTIMEOFF;

因為使用了表級鎖和最小日志記錄機制，執行時間往往會顯著縮短，尤其在數據量更大的情況下效果更明顯。

性能對比與注意事項

在上面的示例中，通常會出現以下結論：

• 不使用 TABLOCK
  可能耗時更長，SQL Server 需要更多的日志寫入。同樣在并發場景下，可能有一定鎖沖突，但不會一次性全表鎖定。
• 使用 TABLOCK
  在批量插入時速度會明顯加快，但插入期間禁止其他事務對該表進行更新、插入或刪除，直到操作完成。

需要注意以下幾點：

1. 表鎖時機
   TABLOCK 會鎖住整個目標表，在多用戶訪問頻繁的線上 OLTP 系統中需慎用。
2. 日志空間
   雖說最小日志記錄會減少寫入量，但在非常龐大的插入規模下仍會對事務日志造成壓力，需確保數據庫日志文件有足夠空間。
3. 并行插入
   SQL Server 版本和數據庫兼容級別可能影響并行度，如果想發揮最大化效果，需確認實例和查詢設置允許并行執行。

總結

TABLOCK 提示是 SQL Server 為了應對大規模數據載入所提供的非常實用的手段之一。在臨時表操作、批量數據遷移和數據倉庫加載等場景中，通過最小化日志寫入和開啟并行插入，往往能成倍縮短插入時間。不過，需要根據實際業務需求，綜合考慮表鎖定帶來的影響和可接受度，再決定是否使用 TABLOCK。在合適的場景中合理運用，可以為整體加載流程帶來顯著的性能提升。

閱讀原文：原文鏈接