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 <= 10="" 610="" 10000="" 20088="" 114293="" )="" 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="" heap="" testing******************************="" --insert="" rows="" to="" target="" table="" with="" (tablock)="" minimally="" logged="" tarheap="" with(tablock)="" *="" srcheap="" --="" check="" log="" entries="" top="" operation="" [minimally="" operation],context,="" [log="" record="" fixed="" length],="" allocunitid,="" allocunitname="" fn_dblog(null,="" null)="" where="" order="" by="" length]="" desc;="" --note="" that="" is="" small="" without="" fully="" with(nolock);="" [fully="" big="" indexed="" tables="" testing="" 610******************************="" clustered="" index="" and="" trace="" flag="" off="" --fully="" second="" batch="" --first="" tartable="" checkpoint;="" or="" like="" '%tartable%'="" --4582="" --0="" --second="" col1+10000,col2,col3="" checkpoint="" --from="" 610,="" tested="" twice="" --114308="" rows,="" --20090="" (context="LCX_CLUSTERED" --10000="" (actual="" rows)="" context="LCX_INDEX_INTERIOR" --44="" (description)="" desc="" go="" dbcc="" traceon(610);="" truncate="" tartable;="" on="" --minimally="" for="" all="" batches="" --with="" enables="" +="" tablock,="" the="" first="" bath="" less="" than="" processes="" as="" same="" begining="" --with(tablock)="" --54995="" --10090="" (autual="" traceoff(610)="" 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

博客园精华区责编内容来自:博客园精华区 (源链) | 更多关于

阅读提示:酷辣虫无法对本内容的真实性提供任何保证,请自行验证并承担相关的风险与后果!
本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » 4. 跟踪标记 (Trace Flag) 610 对索引组织表(IOT)最小化日志

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录