SqlServer中tempdb的日志机制原理解析及示例分享_Sql Server

SqlServer中tempdb的日志机制原理解析及示例分享

2020-04-07 16:24hebedich Sql Server

tempdb为什么比其他数据库快?估计95%以上的程序员们都一脸茫然.Tempdb作为Sqlserver的重要特征,一直以来大家对它可能即熟悉又陌生.熟悉是我们时时刻刻都在用,陌生可能是很少有人关注它的运行机制.这次我将通过实例给大家介绍下t

测试用例

我们分别在用户数据库(testpage),tempdb中创建相似对象t1,#t1,并在tempdb中创建创建非临时表,然后执行相应的insert脚本(用以产生日志),并记录执行时间用以比较用以比较说明tempdb”快”

Code

用户数据库testpage

				?

									use testpage

									go

									create table t1

									(

									id int identity(1,1) not null,

									str1 char(8000)

									)

									declare @t datetime2=sysutcdatetime()

									declare @i int

									set @i=1

									while (@i<100000)

									begin

									insert into t1 select @i,'aa'

									select @i=@i+1

									end

									select [extime]=DATEDIFF(S,@t,sysutcdatetime())

tempdb

				?

									use tempdb

									go

									create table #t1

									(

									id int not null,

									str1 char(8000)

									)

									declare @t datetime2=sysutcdatetime()

									declare @i int

									set @i=1

									while (@i<100000)

									begin

									insert into #t1 select @i,'aa'

									select @i=@i+1

									end

									select [extime]=DATEDIFF(S,@t,sysutcdatetime())

非临时表在tempdb中执行

				?

									use tempdb

									go

									create table t1

									(

									id int not null,

									str1 char(8000)

									)

									declare @t datetime2=sysutcdatetime()

									declare @i int

									set @i=1

									while (@i<100000)

									begin

									insert into t1 select @i,'aa'

									select @i=@i+1

									end

									select [extime]=DATEDIFF(S,@t,sysutcdatetime())

由图1-1中我们可以看出,在普通表中执行一分钟的脚本,tempdb只需执行22s.而普通表在tempdb中也只需27s均大大优于普通表中执行情况.

感兴趣的朋友亦可在执行过程中观察日志相关的性能技术器的运行情况如(Log Bytes Flusged \sec 等)

SqlServer中tempdb的日志机制原理解析及示例分享
图1-1

由此测试我们可以看出本文开始提到的”tempdb比其他数据库快”.

实际并不是tempdb有什么魔法,而是tempdb的日志机制与其他数据库大有不同.

Tempdb的日志机制

Tempdb Simple恢复模式(重启后无需还原操作)

Tempdb使用最小化日志

Tempdb 不受系统CheckPoint影响(系统checkpoint不涉及tempdb,但人为tempdb中执行会落盘)

Tempdb 在刷入数据页到磁盘前,日志无需落盘(事务提交日志无需落盘)

"快"的原因

可以看到系统检查点自身会绕过tempdb,tempdb执行时无需日志先落盘.且会最小化日志记录(关于此一个特性我会稍候陈述)这些都极大的缓解了磁盘IO瓶颈,使得tempdb相比其他DB会快很多.

注意:虽然系统checkpoint检查点会绕过tempdb,但tempdb中人为执行checkpoint还是会起作用,大家只应测试环境中使用,正式环境中慎用！

在上面的实例中我们可以看到无论在表的类型是什么,在tempdb中速度都会有很大提升,但普通表的执行时间还是略长于临时表,这是因为普通表的的日志记录信息还是要略多于临时表的.

关于tempdb最小化日志

在堆表(heap)中 insert,update操作的的更新信息日志无需记录.

我们通过简单实例来看.

				?

									USE [tempdb]

									GO

									create table #nclst

									(

									id int identity(1,1) primary key nonclustered,---heaptable

									str1 char(8000)

									);

									create table #clst

									(

									id int identity(1,1) primary key,------clustered

									str1 char(8000)

									);

									checkpoint-----生产环境慎用！

									DBCC SHRINKFILE (N'templog' , 0, TRUNCATEONLY)

									GO

									insert into #nclst(str1) select 'aa'

									select [Current LSN],Operation,CONTEXT,[Log Record Length] 

									from fn_dblog(null,null) where AllocUnitId is not null

									checkpoint-----生产环境慎用！

									DBCC SHRINKFILE (N'templog' , 0, TRUNCATEONLY)

									GO

									insert into #clst(str1) select 'aa'

									select [Current LSN],Operation,CONTEXT,[Log Record Length] 

									from fn_dblog(null,null) where AllocUnitId is not null