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中執(zhí)行

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中我們可以看出,在普通表中執(zhí)行一分鐘的腳本,tempdb只需執(zhí)行22s.而普通表在tempdb中也只需27s均大大優(yōu)于普通表中執(zhí)行情況.

感興趣的朋友亦可在執(zhí)行過程中觀察日志相關的性能技術器的運行情況如(Log Bytes Flusged \sec 等)

圖1-1

由此測試我們可以看出本文開始提到的”tempdb比其他數據庫快”.

實際并不是tempdb有什么魔法,而是tempdb的日志機制與其他數據庫大有不同.

Tempdb的日志機制

Tempdb Simple恢復模式(重啟后無需還原操作)

Tempdb使用最小化日志

Tempdb 不受系統(tǒng)CheckPoint影響(系統(tǒng)checkpoint不涉及tempdb,但人為tempdb中執(zhí)行會落盤)

Tempdb 在刷入數據頁到磁盤前,日志無需落盤(事務提交日志無需落盤)

"快"的原因

可以看到系統(tǒng)檢查點自身會繞過tempdb,tempdb執(zhí)行時無需日志先落盤.且會最小化日志記錄(關于此一個特性我會稍候陳述)這些都極大的緩解了磁盤IO瓶頸,使得tempdb相比其他DB會快很多.

注意:雖然系統(tǒng)checkpoint檢查點會繞過tempdb,但tempdb中人為執(zhí)行checkpoint還是會起作用,大家只應測試環(huán)境中使用,正式環(huán)境中慎用！

在上面的實例中我們可以看到無論在表的類型是什么,在tempdb中速度都會有很大提升,但普通表的執(zhí)行時間還是略長于臨時表,這是因為普通表的的日志記錄信息還是要略多于臨時表的.

關于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-----生產環(huán)境慎用！
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-----生產環(huán)境慎用！
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

由圖1-2中可以看出堆表中并未記錄Insert中的#ncls.str1的具體信息,而聚集表中則記錄相應信息