查看: 250|回复: 0

[SQLServer] 4. 跟踪标记 (Trace Flag) 610 对索引组织表(IOT)最小化日志

发表于 2017-12-7 08:00:01

跟踪标记: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]这列的变化和区别

  1. -- Set Recover model to SIMPLE/BULK_LOGGED
  2. ALTER DATABASE testing SET RECOVERY SIMPLE;
  3. /**************************START of CREATE TEST TABLES******************************/
  4. USE testing
  5. GO
  6. IF OBJECT_ID('SrcHeap') IS NOT NULL
  7. DROP TABLE SrcHeap;
  8. IF OBJECT_ID('TarHeap') IS NOT NULL
  9. DROP TABLE TarHeap;
  10. IF OBJECT_ID('TarTable') IS NOT NULL
  11. DROP TABLE TarTable;
  12. CREATE TABLE SrcHeap (col1 INT ,col2 CHAR(4000),col3 CHAR(1000) ) ;
  13. CREATE TABLE TarHeap( col1 INT ,col2 CHAR(4000),col3 CHAR(1000) ) ;
  14. CREATE TABLE TarTable (col1 INT ,col2 CHAR(4000),col3 CHAR(1000) );
  15. create clustered index IX_01 on TarTable(col1);
  16. --Insert row into source table
  17. WITH Nums (col)
  18. AS
  19. (
  20. SELECT 1 col
  21. UNION ALL
  22. SELECT col + 1 FROM Nums
  23. WHERE col+1 <= 10000
  24. )
  25. INSERT INTO SrcHeap(col1,col2,col3)
  26. SELECT col,replicate('A',4000),replicate('B',1000) FROM Nums
  27. OPTION (MAXRECURSION 10000)
  28. /**************************END of CREATE TEST TABLES******************************/
  29. /**************************START of HEAP testing******************************/
  30. --Insert rows to Target Table with (TABLOCK) Minimally logged
  31. INSERT INTO TarHeap WITH(TABLOCK)
  32. SELECT * FROM SrcHeap
  33. -- Check Log Entries
  34. SELECT TOP 10 operation [MINIMALLY LOGGED OPERATION],context, [log record fixed length], [log record length], AllocUnitId, AllocUnitName
  35. FROM fn_dblog(null, null)
  36. WHERE allocunitname='dbo.TarHeap'
  37. ORDER BY [Log Record Length] DESC;
  38. --Note That [Log Record length] is small
  39. --Insert rows to Target Table without (TABLOCK) fully logged
  40. INSERT INTO TarHeap
  41. SELECT * FROM SrcHeap WITH(NOLOCK);
  42. -- Check Log Entries
  43. SELECT TOP 10 operation [FULLY LOGGED OPERATION],context, [log record fixed length], [log record length], AllocUnitId, AllocUnitName
  44. FROM fn_dblog(null, null)
  45. WHERE allocunitname='dbo.TarHeap'
  46. ORDER BY [Log Record Length] DESC;
  47. --Note That [Log Record length] is big
  48. /**************************END of HEAP testing******************************/
  49. /**************************START of INDEXED TABLES testing WITHOUT 610******************************/
  50. --Insert rows to Target Table with clustered index and trace flag 610 off
  51. --Fully logged from second batch
  52. --First Batch
  53. INSERT INTO TarTable WITH(TABLOCK)
  54. SELECT * FROM SrcHeap WITH(NOLOCK);
  55. CHECKPOINT;
  56. --first batch with or without 610
  57. select *
  58. FROM fn_dblog(null, null)
  59. WHERE allocunitname LIKE '%TarTable%' --4582 rows
  60. and operation = 'LOP_INSERT_ROWS'--0 rows
  61. --Second Batch
  62. INSERT INTO TarTable WITH(TABLOCK)
  63. SELECT col1+10000,col2,col3 FROM SrcHeap WITH(NOLOCK);
  64. CHECKPOINT
  65. --from second batch without 610, tested twice
  66. SELECT *
  67. FROM fn_dblog(null, null)
  68. WHERE allocunitname LIKE '%TarTable%' --114308 rows, 114293 rows
  69. and operation = 'LOP_INSERT_ROWS'--20090 rows, 20088 rows
  70. and (context = 'LCX_CLUSTERED' --10000 rows (actual rows)
  71. or
  72. context = 'LCX_INDEX_INTERIOR' --44 rows (description)
  73. )
  74. ORDER BY [Log Record Length] DESC
  75. /**************************END of INDEXED TABLES testing WITHOUT 610******************************/
  76. CHECKPOINT;
  77. GO
  78. DBCC TRACEON(610);
  79. TRUNCATE TABLE TarTable;
  80. GO
  81. /**************************START of INDEXED TABLES testing WITH 610******************************/
  82. --Insert rows to Target Table with clustered index and trace flag 610 on
  83. --Minimally logged for all batches
  84. --with 610 enables + with TABLOCK, the first bath logged less than second batch
  85. --with 610 enables + without TABLOCK, the first batch processes as same as begining with second batch
  86. INSERT INTO TarTable --WITH(TABLOCK)
  87. SELECT * FROM SrcHeap WITH(NOLOCK);
  88. INSERT INTO TarTable --WITH(TABLOCK)
  89. SELECT col1+10000,col2,col3 FROM SrcHeap WITH(NOLOCK);
  90. CHECKPOINT
  91. --from second batch with 610
  92. SELECT *
  93. FROM fn_dblog(null, null)
  94. WHERE allocunitname LIKE '%TarTable%' --54995 rows
  95. and operation = 'LOP_INSERT_ROWS'--10090 rows
  96. and (context = 'LCX_CLUSTERED' --0 rows (autual rows)
  97. or
  98. context = 'LCX_INDEX_INTERIOR' --44 rows (description)
  99. )
  100. ORDER BY [Log Record Length] DESC
  101. /**************************END of INDEXED TABLES testing WITH 610******************************/
  102. DBCC TRACEOFF(610)
  103. 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



回复

使用道具 举报

关闭

站长推荐上一条 /1 下一条