Sql Server Tempdb原理-日志机制解析实践

日期:2020-05-07编辑作者:数据库

测试用例

笔者曾经在面试DBA时的一句”tempdb为什么比其他数据库快?”使得95%以上的应试者都一脸茫然.Tempdb作为Sqlserver的重要特征,一直以来大家对它可能即熟悉又陌生.熟悉是我们时时刻刻都在用,陌生可能是很少有人关注它的运行机制.这次我将通过实例给大家介绍下tempdb的日志机制.

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

 

Code

测试用例

用户数据库testpage

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

use testpagegocreate table t1(id int identity(1,1) not null,str1 char(8000))declare @t datetime2=sysutcdatetime()declare @i intset @i=1while (@i100000)begininsert into t1 select @i,'aa'select @i=@i+1endselect [extime]=DATEDIFF(S,@t,sysutcdatetime())

Code

www.129028.com金沙,tempdb

用户数据库testpage

use tempdbgocreate table #t1(id int not null,str1 char(8000))declare @t datetime2=sysutcdatetime()declare @i intset @i=1while (@i100000)begininsert into #t1 select @i,'aa'select @i=@i+1endselect [extime]=DATEDIFF(S,@t,sysutcdatetime())
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中执行

tempdb

use tempdbgocreate table t1(id int not null,str1 char(8000))declare @t datetime2=sysutcdatetime()declare @i intset @i=1while (@i100000)begininsert into t1 select @i,'aa'select @i=@i+1endselect [extime]=DATEDIFF(S,@t,sysutcdatetime())
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均大大优于普通表中执行情况.

非临时表在tempdb中执行

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

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

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

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

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

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

www.129028.com金沙 1                                                                              图1-1

Tempdb的日志机制

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

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

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

Tempdb使用最小化日志

Tempdb的日志机制

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

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

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

Tempdb使用最小化日志

"快"的原因

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

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

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

本文由www.129028.com金沙发布于数据库,转载请注明出处:Sql Server Tempdb原理-日志机制解析实践

关键词:

Sql Server中的非聚集索引详细介

这篇我们来解开神秘面纱。 3:通过最后的槽位列表,可以得知173号索引页上存放着8条索引记录。 复制代码 代码如下...

详细>>

必须会的SQL语句(一) 创建数据库与删除数据库【www.129028.com金沙

1.创建数据库 drop命令用于删除数据库。 Create database 名称on primary{ name ='名称', filename ='c:xx名称.mdf', size = 10mb, --数据库...

详细>>

SqlServer提示“列前缀tempdb.无效: 未指定表名”问题解决方案www

在查询凭证、审核凭证时出现“列前缀tempdb.无效:未指定表名”的错误提示,怎么解决? 本文我们总结了几个在安装...

详细>>

【www.129028.com金沙】SQL Server中将查询结果转换为Json格式脚本分享

脚本源码: 复制代码 代码如下:SET ANSI_NULLS ONGOSETQUOTED_IDENTIFIER ONGOCREATEPROCEDURE[dbo].[SerializeJSON](@ParameterSQL ASVARCHAR(MAX)...

详细>>