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:工具和使用工具的增强功能；很不错哦