4. 跟蹤標記 (Trace Flag) 610 對索引組織表(IOT)最小化日誌

跟蹤標記：610

功能：

• 用批量導入操作(Bulk Import Operations)載入數據時，對於索引組織表(即有聚集索引的表) 最小化日誌；

上圖為simple/bulk-logged恢復模式下，最小化日誌的幾種操作，其中包含了批量導入操作，而批量導入操作的最小化日誌有一些前提條件，概括如下：

1. 目標表未用於複製；

2. 目標表上指定了TABLOCK；

3. 目標表上的索引情況，這條規則最複雜，見下表：

從表格可以看出：

(1) 堆表的數據頁一直可以最小化日誌；

(2) 聚集索引和非聚集索引，一直是完全記錄日誌的，除了在空表的情況下(即索引也是空的)，第一個批次(batch)導入的數據可以最小化日誌，從第二個批次(batch)起就不再是最小化日誌，原因就是第一個批次(batch)結束後，就不再是空表了，跟蹤標記610也正是因為這個而出現；

用途：

• 提升索引組織表(即有聚集索引的表)批量導入操作的性能；

備註：

(1) 從SQL Server 2008 開始，引入了跟蹤標記610；

(2) 從SQL Server 2016開始，跟蹤標記610所具備的功能，已經被資料庫引擎所預設，不需要再額外手動開啟跟蹤標記 (同樣的，也就沒有開關去關閉) ；

測試：觀察[Log Record Length]這列的變化和區別

-- Set Recover model to SIMPLE/BULK_LOGGED
ALTER DATABASE testing SET RECOVERY SIMPLE;

/**************************START of CREATE TEST TABLES******************************/
USE testing
GO

IF OBJECT_ID('SrcHeap') IS NOT NULL 
 DROP TABLE SrcHeap;

IF OBJECT_ID('TarHeap') IS NOT NULL 
 DROP TABLE TarHeap;

IF OBJECT_ID('TarTable') IS NOT NULL 
 DROP TABLE TarTable;

CREATE TABLE SrcHeap (col1 INT ,col2 CHAR(4000),col3 CHAR(1000) ) ; 

CREATE TABLE TarHeap( col1 INT ,col2 CHAR(4000),col3 CHAR(1000) ) ;

CREATE TABLE TarTable (col1 INT ,col2 CHAR(4000),col3 CHAR(1000) ); 
create clustered index IX_01 on TarTable(col1);

--Insert row into source table
WITH Nums (col)
AS 
(
 SELECT 1 col
 UNION ALL 
 SELECT col + 1 FROM Nums
 WHERE col+1 <= 10000
)
INSERT INTO SrcHeap(col1,col2,col3) 
 SELECT col,replicate('A',4000),replicate('B',1000) FROM Nums 
 OPTION (MAXRECURSION 10000)
/**************************END of CREATE TEST TABLES******************************/

/**************************START of HEAP testing******************************/
--Insert rows to Target Table with (TABLOCK) Minimally logged
INSERT INTO TarHeap WITH(TABLOCK)
 SELECT * FROM SrcHeap 

-- Check Log Entries
SELECT TOP 10 operation [MINIMALLY LOGGED OPERATION],context, [log record fixed length], [log record length], AllocUnitId, AllocUnitName
 FROM fn_dblog(null, null)
 WHERE allocunitname='dbo.TarHeap'
 ORDER BY [Log Record Length] DESC;
--Note That [Log Record length] is small 

--Insert rows to Target Table without (TABLOCK) fully logged
INSERT INTO TarHeap 
 SELECT * FROM SrcHeap WITH(NOLOCK);

-- Check Log Entries
SELECT TOP 10 operation [FULLY LOGGED OPERATION],context, [log record fixed length], [log record length], AllocUnitId, AllocUnitName
 FROM fn_dblog(null, null)
 WHERE allocunitname='dbo.TarHeap'
 ORDER BY [Log Record Length] DESC;
--Note That [Log Record length] is big 
/**************************END of HEAP testing******************************/

/**************************START of INDEXED TABLES testing WITHOUT 610******************************/
--Insert rows to Target Table with clustered index and trace flag 610 off 
--Fully logged from second batch

--First Batch
INSERT INTO TarTable  WITH(TABLOCK)
 SELECT * FROM SrcHeap WITH(NOLOCK);

CHECKPOINT;
--first batch with or without 610
select * 
 FROM fn_dblog(null, null)
 WHERE allocunitname LIKE '%TarTable%' --4582 rows
  and operation = 'LOP_INSERT_ROWS'--0 rows

--Second Batch
INSERT INTO TarTable  WITH(TABLOCK)
 SELECT col1+10000,col2,col3 FROM SrcHeap WITH(NOLOCK);

CHECKPOINT
--from second batch without 610, tested twice
SELECT * 
 FROM fn_dblog(null, null)
 WHERE allocunitname LIKE '%TarTable%' --114308 rows, 114293 rows
  and operation = 'LOP_INSERT_ROWS'--20090 rows, 20088 rows
  and (context  = 'LCX_CLUSTERED'   --10000 rows (actual rows)
       or 
       context = 'LCX_INDEX_INTERIOR' --44 rows (description)
      )
 ORDER BY [Log Record Length] DESC
/**************************END of INDEXED TABLES testing WITHOUT 610******************************/

CHECKPOINT;
GO
DBCC TRACEON(610);
TRUNCATE TABLE TarTable;
GO

/**************************START of INDEXED TABLES testing WITH 610******************************/
--Insert rows to Target Table with clustered index and trace flag 610 on 
--Minimally logged for all batches
--with 610 enables + with TABLOCK, the first bath logged less than second batch
--with 610 enables + without TABLOCK, the first batch processes as same as begining with second batch
INSERT INTO TarTable  --WITH(TABLOCK)
 SELECT * FROM SrcHeap WITH(NOLOCK);

INSERT INTO TarTable  --WITH(TABLOCK)
 SELECT col1+10000,col2,col3 FROM SrcHeap WITH(NOLOCK);

CHECKPOINT
--from second batch with 610
SELECT * 
 FROM fn_dblog(null, null)
 WHERE allocunitname LIKE '%TarTable%' --54995 rows
  and operation = 'LOP_INSERT_ROWS'--10090 rows
  and (context  = 'LCX_CLUSTERED'   --0 rows (autual rows)
       or 
       context = 'LCX_INDEX_INTERIOR' --44 rows (description)
      )
 ORDER BY [Log Record Length] DESC
/**************************END of INDEXED TABLES testing WITH 610******************************/

DBCC TRACEOFF(610)
DBCC TRACESTATUS(-1)

小結：

(1) 條件允許情況下，批量導入操作還是跑在堆表上性能最佳；

(2) 跟蹤標記610被開啟後，對於聚集索引，只有新分配的數據頁才會最小化日誌，數據插入已有數據頁，仍然是fully logged，所以建表時還得考慮聚集索引鍵的選擇；

(3) 跟蹤標記610被開啟後，對於非聚集索引，並不一定可以最小化日誌，這取決於查詢優化器對執行計劃的選擇；

(4) 跟蹤標記610被開啟後，對於堆表，仍然要指定TABLOCK；對於索引組織表，可不指定TABLOCK，也仍然可以最小化日誌，每個批次(batch)最小化日誌方式一致；

參考:

Operations That Can Be Minimally Logged

https://technet.microsoft.com/en-us/library/ms191244(v=sql.105).aspx

Prerequisites for Minimal Logging in Bulk Import

https://technet.microsoft.com/en-us/library/ms190422(v=sql.105).aspx

DBCC TRACEON - Trace Flags (Transact-SQL)

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql

The Data Loading Performance Guide

https://technet.microsoft.com/en-us/library/dd425070(v=sql.100).aspx