程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> 更多數據庫知識 >> SqlServer中tempdb的日志機制原理解析及示例分享，sqlservertempdb

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

編輯：更多數據庫知識

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

測試用例

我們分別在用戶數據庫(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 等)

圖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

由圖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中使用堆表速度佳.(需視具體情況而定)

怎清除sqlserver tempdb數據

收縮 Tempdb 的方法
此方法要求您重新啟動 SQL Server。

1. 停止 SQL Server。打開命令提示符，然後鍵入以下命令啟動 SQL Server：
sqlservr -c -f
-c 和 -f 參數使 SQL Server 以最小配置模式啟動，讓數據文件的 tempdb 大小為 1 MB，日志文件的 tempdb 為 0.5 MB。
注意：如果使用 SQL Server 命名實例，必須切換到適當的文件夾 (Program Files/Microsoft SQL Server/MSSQL$instance name/Binn)，並使用 -s 開關 (-s%instance_name%)。
2. 用查詢分析器連接到 SQL Server，然後運行下列 Transact-SQL 命令： ALTER DATABASE tempdb MODIFY FILE
(NAME = 'tempdev', SIZE = target_size_in_MB)
--Desired target size for the data file

ALTER DATABASE tempdb MODIFY FILE
(NAME = 'templog', SIZE = target_size_in_MB)
--Desired target size for the log file

3. 通過在命令提示符窗口中按 Ctrl-C 停止 SQL Server，將 SQL Server 作為服務重新啟動，然後驗證 Tempdb.mdf 和 Templog.ldf 文件的大小。
此方法的局限是它只能對默認的 tempdb 邏輯文件 tempdev 和 templog 進行操作。如果將其他文件添加到了 tempdb，您可以在將 SQL Server 作為服務重新啟動後收縮它們。在啟動過程中將重新創建所有 tempdb 文件；因此，它們是空的並可刪除。要刪除 tempdb 中的其他文件，請使用帶有 REMOVE FILE 選項的 ALTER DATABASE 命令。

sql server 2005怎清理tempdb日志

1.Service的增強功能； 2.數據庫引擎增強功能; 3:數據庫訪問接口方面的增強功能; 4:Analysis Services的增強功能; 5:Integration Services的增強功能; 6:全文搜索的增強功能; 7:Notification Services的增強功能; 8:復制增強; 9::Reporting Services的增強功能; 10:工具和使用工具的增強功能；很不錯哦