SQL Server 最小化日志操作解析,应用

SQL Server 最小化日志操作解析,应用

大家好,又见面了,我是全栈君。

最小化日志

 

概念: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%’

 

 8fEyhVE6711XoAAAAASUVORK5CYII=

图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%’

wFVyEDGlOgE+wAAAABJRU5ErkJggg==

图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%’

 weWlFqcq5bibAAAAABJRU5ErkJggg==

图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

 5riRQze4wQ1ucIPrxP8PmxD7CIUpaJwAAAAASUVO

图b-2

 

关于trace flag 610

Sql2008新引进的TF,用于非空B-tree结构中仍可最小化日志操作.

 

关于TF610的使用我个人建议是特殊场景谨慎使用.

一般来说我们在对非空表导入数据的场景,堆表在Online的过程中最小化日志锁表本身就会影响线上的应用.聚集表数据在插入过程中批量导入的可能性又极低.(好好的聚集表数据批量导入,情况甚微).

TF610本身是为了减少记录的tran-log大小而设计,并非加快导入而设计.

 

使用时注意:

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的时间段降到了最低.

注:当数据库有应用全日志模式的情况下,如镜像,不宜修改的数据库模式而破坏应用,当全日志情形下产生的大量日志可能导致实例级的全局问题,应仔细权衡操作.

    对有审计需求的数据库来说,注意具体审计需求:是否需要恢复到时间点.

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/108200.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)
blank

相关推荐

  • 搜索优化_百度搜索关键词

    搜索优化_百度搜索关键词一个网站其实很简单,但难的是在于那些搜索引擎如(google,yahoo,msn等)如何找到你,并提高排名!最近,我总结了7条最新、最重要的注意事项1.网站优化是为了访问者,而不是针对搜索引擎。  这一点是做网站的终极目标,请牢记这一点。搜索引擎优化要考虑用户体验,用户体验这个东西最重要的了,我们做站就是为了客户所以用户体验是非常重要是第一位的。其次在用户体验的基础之上很好地进行搜

    2022年10月25日
  • c# dynamic用法_dynamic_cast详解

    c# dynamic用法_dynamic_cast详解dynamic是FrameWork4.0的新特性。dynamic的出现让C#具有了弱语言类型的特性。编译器在编译的时候不再对类型进行检查,编译期默认dynamic对象支持你想要的任何特性。比如,即使你

  • vmware15激活码【永久激活】「建议收藏」

    (vmware15激活码)好多小伙伴总是说激活码老是失效,太麻烦,关注/收藏全栈君太难教程,2021永久激活的方法等着你。https://javaforall.cn/100143.htmlIntelliJ2021最新激活注册码,破解教程可免费永久激活,亲测有效,上面是详细链接哦~MLZPB5EL5Q-eyJsaWNlbnNlSWQiOi…

  • Linux-dosbox使用「建议收藏」

    2019独角兽企业重金招聘Python工程师标准>>>…

  • TCP 详解[通俗易懂]

    上回说到UDP协议,与之对应的便是TCP协议TCP协议TCP协议全称:传输控制协议,顾名思义,就是要对数据的传输进行一定的控制.先来看看它的报头我们来分析分析每部分的含义和作用源端口号/目的端口号:表示数据从哪个进程来,到哪个进程去.32位序号:4位首部长度:表示该tcp报头有多少个4字节(32个bit)6位保留:顾名思义,先保留着,以…

  • JAVA Exception和IOException之间的使用区别

    JAVA Exception和IOException之间的使用区别使用Exception可以保证捕获异常后能继续维持JVM的运行如果Exception换成IOException后,一旦出现IO异常,便会捕获停止运行.“IoException“(流异常,通常用在文件读取中)是”Exception”(用在所有的异常处理中)的一个分支,也就是说“Exception”的范围更大。解释:通过java中在捕获异常的时候需要先捕获“子异常”(范围小,如流异常),之……

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号