SQL Server 最小化日志操作解析,应用
Sql Server 中数据库在BULK_LOGGED/SIMPLE模式下的一些操作会采用最小化日志的记录方式,以减小tran log落盘日志量从而提高整体性能.
这里我简单介绍下哪些操作在什么样的情况下会最小化日志记录.以及现实生产环境中如何应用最小化日志.
概念:SQL Server在满足相应条件的基础上时进行一些特定的操作如Rebuild Index时会进行最小化Tran Log记录操作,从而改善系统性能.
注意:含最小化操作日志操作段日志无法按时间点恢复(point in time)
需要还原模式为简单或大容量日志
最小化日志的操作
Create Index,Alter Index Rebulid
Bulk import操作(BCP,Bulk insert)
Select into
Blob数据操作(使用Write等)
Insert select(sql 2008后特定条件下可以)
Merge(特定条件)
应用:实际应用过程中我们实际使用insert select的时候居多,就此介绍
关于insert select操作的最小化日志
聚集表
当聚集表为空时,使用TABLOCK 锁提示将会最小化日志
当聚集表非空时,无论如何将不会最小化日志
非聚集表
当堆表为空时,使用TABLOCK锁提示,表中行数据,索引数据(非聚集索引)都会最小化日志
当堆表非空时,使用TABLOCK锁提示,表中存在非聚集索引,则行数据,索引数据均非最小化日志
注:最小化日志中表非复制表
一些文档中在堆表有索引非空的情况认为堆行数据会最小化日志,实际是错误的.见图b-2中说明
聚集表实例
聚集空最小化日志 图a-1
create database testbulk go use master ALTER DATABASE [testbulk] SET RECOVERY BULK_LOGGED WITH NO_WAIT go use testbulk go create table t1(id int not null identity (1,1),dystr varchar(200),fixstr char(500)); go set nocount on declare @i int set @i=0 while(@i<20000) begin insert into t1(dystr,fixstr)values('aaa'+str(RAND()*100000000),'bbb'+str(RAND()*100000000)) set @i=@i+1 end create table tcls ( id int , dystr varchar(200), fixstr char(500) ) go CREATE UNIQUE CLUSTERED INDEX inx_id ON dbo.tcls (id) insert into dbo.tcls with(tablockx) select * from dbo.t1 ----cluster table empty select operation,CONTEXT,[Log Record Length],AllocUnitName from fn_dblog(null,null) where AllocUnitName like '%tcls%'
a-1
聚集非空非最小化日志图a-2
truncate table tcls DBCC SHRINKFILE (N'testbulk_log' , 0, TRUNCATEONLY) insert into dbo.tcls with(tablockx) values (100000,'aaa','bbb')----made not empty clustered table go insert into dbo.tcls with(tablockx) select * from dbo.t1 ----cluster table not empty select operation,CONTEXT,[Log Record Length],AllocUnitName from fn_dblog(null,null) where AllocUnitName like '%tcls%'
a-2
非聚集索引实例
非聚集非空堆表无索引实例 图b-1
create table tnoncls ( id int , dystr varchar(200), fixstr char(500) ) go insert into dbo.tnoncls with(tablockx) values (100000,'aaa','bbb')----made not empty heap table no index go insert into dbo.tnoncls with(tablockx) select * from dbo.t1 with(tablockx)----heap table not empty with no index select operation,CONTEXT,[Log Record Length],AllocUnitName from fn_dblog(null,null) where AllocUnitName like '%tnoncls%'
图b-1
非聚集非空堆表含索引实例 图b-2
truncate table tnoncls----truncate table DBCC SHRINKFILE (N'testbulk_log' , 0, TRUNCATEONLY) CREATE UNIQUE NONCLUSTERED INDEX inx_id ON dbo.tnoncls (id)----add non clustered index insert into dbo.tnoncls with(tablockx) values (100000,'aaa','bbb')----made not empty heap table with index go insert into dbo.tnoncls with(tablockx) select * from dbo.t1 with(tablockx)----heap table not empty with index select operation,CONTEXT,[Log Record Length],AllocUnitName from fn_dblog(null,null) where AllocUnitName like '%tnoncls%'----both datapage and indexpage full log
b-2
关于trace flag 610
Sql2008新引进的TF,用于非空B-tree结构中仍可最小化日志操作.
关于TF610的使用我个人建议是特殊场景谨慎使用.
一般来说我们在对非空表导入数据的场景,堆表在Online的过程中最小化日志锁表本身就会影响线上的应用.聚集表数据在插入过程中批量导入的可能性就更低.(好好的聚集表数据批量导入,情况甚微).
TF610本身是为了减少记录的tran-log大小而设计,并非加快导入而设计.
使用TF610时注意:
1:特定情况下session级打开 dbcc traceon(610)
2:当批量事务提交时所有数据页需落盘,如果此之前没有检查点执行落盘会带来大量的随机IO从而导致性能下降,有时甚至不如全日志记录的插入.
3:避免单个事务过大.超大事务可能导致其他问题.
最小化日志(Minimal Log)最佳实践
BULK_LOGGED模式:现实生产环境中的数据库一般是简单,或者全日志. BULK_LOGGED模式使用常态下寥寥无几.但当我们的数据操作中存在大量可最小化的日志操作中(如索引重建维护)我们可以开启BULK_LOGGED模式从而提高操作效率.
例:索引维护
1:选取操作时间窗口:日常全备份前
2:全备份完成后,人工干预执行一次日志备份.
3:修改数据库模式由Full->BULK_LOGGED
4:大容量日志操作(索引维护)
5:人工干预备份日志
6:重新调整为全日志(模式)
BULK_LOGGED模式下是不会破坏日志链,在这样的模式下我们把Non point time的时间段降到了最低.
注:当数据库有应用全日志模式的情况下,如镜像,不宜修改的数据库模式而破坏应用,当全日志情形下产生的大量日志可能导致实例级的全局问题,应仔细权衡操作.
对有审计需求的数据库来说,注意具体审计需求:是否需要恢复到时间点.
