Sql Server Tempdb原理-日誌機制解析實踐

標籤：des   style   blog   color   使用   os   io   檔案   

筆者曾經在面試DBA時的一句”tempdb為什麼比其他資料庫快?”使得95%以上的應試者都一臉茫然.Tempdb作為Sqlserver的重要特徵,一直以來大家對它可能即熟悉又陌生.熟悉是我們時時刻刻都在用,陌生可能是很少有人關注它的運行機制.這次我將通過執行個體給大家介紹下tempdb的日誌機制.

 

測試案例

我們分別在使用者資料庫(testpage),tempdb中建立相似對象t1,#t1,並在tempdb中建立建立非暫存資料表,然後執行相應的insert指令碼(用以產生日誌),並記錄執行時間用以比較用以比較說明tempdb”快”

Code

使用者資料庫testpage

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

tempdb

use tempdbgocreate table #t1(id int not null,str1 char(8000))declare @t datetime2=sysutcdatetime()declare @i intset @i=1while (@i<100000)begininsert into #t1 select @i,‘aa‘select @i=@i+1endselect [extime]=DATEDIFF(S,@t,sysutcdatetime())

非暫存資料表在tempdb中執行

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

由圖1-1中我們可以看出,在普通表中執行一分鐘的指令碼,tempdb只需執行22s.而普通表在tempdb中也只需27s均大大優於普通表中執行情況.

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

                                                                              圖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]GOcreate table #nclst(id int identity(1,1) primary key nonclustered,---heaptablestr1 char(8000));create table #clst(id int identity(1,1) primary key,------clusteredstr1 char(8000));checkpoint-----生產環境慎用！DBCC SHRINKFILE (N‘templog‘ , 0, TRUNCATEONLY)GOinsert into #nclst(str1) select ‘aa‘select [Current LSN],Operation,CONTEXT,[Log Record Length] from fn_dblog(null,null) where AllocUnitId is not nullcheckpoint-----生產環境慎用！DBCC SHRINKFILE (N‘templog‘ , 0, TRUNCATEONLY)GOinsert 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的具體資訊,而聚集表中則記錄相應資訊

                                                                               圖1-2

Tempdb為何需要日誌

既然tempdb每次重啟都會重建立立,我們無需重做日誌,但運行過程中是可能需要復原的,這也是tempdb日誌存在的原因.

Tempdb 不支援重做(Redo)但需支援復原(rollback).

關於tempdb復原.

Tempdb中如果記錄檔中無足夠空間應用復原則會引起整個執行個體就宕機!

Tempdb最佳實務-日誌

a 不要tempdb中checkpoint(消耗巨大引起系統效能下滑)

b 不要tempdb中開啟過長事務(無法截斷日誌,造成日誌過大,如復原時無法復原則宕機)

c 一般需要中間表匹配的過程在tempdb中建立進行(建立速度快,需視具體情況而定.)

d tempdb中使用堆錶速度佳.(需視具體情況而定)