169it科技资讯


当前位置:  数据库>sqlserver

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

    来源: 互联网  发布时间:2014-10-14

测试用例

我们分别在用户数据库(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” 的日志已满 问题解决方案

     执行sql 语句,中间没有用到临时表 提示服务器: 消息 9002,级别 17,状态 2,行 1 数据库 'tempdb' 的日志已满。请备份该数据库的事务日志以释放一些日志空间。 网上找了下解决方案,大体是扩大临时库的日志文件的大小解决的 解决过程: 查看了下数据库的属性,是自动增长,不指定文件大小上限。 在网上Google了很久,试了些方法都不行;数据库所在磁盘还有很大的可用空间,试着下重药了。 直接把tempdb的数据文件和日志文件的大小改为3000M, 问题解决。 记录一下清空日志的......


    ▪浅谈tempdb在SqlServer系统中的重要作用

     简介:        tempdb是SQLServer的系统数据库一直都是SQLServer的重要组成部分,用来存储临时对象。可以简单理解tempdb是SQLServer的速写板。应用程序与数据库都可以使用tempdb作为临时的数据存储区。一个实例的所有用户都共享一个Tempdb。很明显,这样的设计不是很好。当多个应用程序的数据库部署在同一台服务器上的时候,应用程序共享tempdb,如果开发人员不注意对Tempdb的使用就会造成这些数据库相互影响从而影响应用程序。 特性: 1、 tempdb中的任何数据在系统重新启动之后都不会持久存在。因为实际上每次SQLServer启动的时候都会重新创建tempdb。......


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

     在查询凭证、审核凭证时出现“列前缀tempdb.无效: 未指定表名”的错误提示,怎么解决? 原因:是因为SQL2000无法识别计算机名称中”-”、”、”以及中文字等特殊字符, 只能使用英文字母及数字。 解决:右键我的电脑“属性”中“计算机名”点击修改,改为纯英文(最好8位以内),切记计算机名不能带“、-”等字符。就可以解决了。 将这个问题改过来之后, 又有问题随之而来: 用友系统管理中注册用户时出现”不能建立跟应用服务器[POWERPC]所指向的数据服务器连接, 不能登录, 请选择其他服务器. 错误描述: (-2147467259)[DBNETLIB][ConnectionOpen (Connect()......


 
最新技术文章:
    ▪Sql Server里删除数据表中重复记录的例子

     [项目] 数据库中users表,包含u_name,u_pwd两个字段,其中u_name存在重复项,现在要实现把重复的项删除! [分析] 1、生成一张临时表new_users,表结构与users表一样; 2、对users表按id做一个循环,每从users表中读出一个条记录,判断new_users中是否存在有相同的u_name,如果没有,则把它插入新表;如果已经有了相同的项,则忽略此条记录; 3、把users表改为其它的名称,把new_users表改名为users,实现我们的需要。 [程序] 代码如下: declare ......


    ▪如何查看SQLSERVER中某个查询用了多少TempDB空间

         在SQL Server中,TempDB主要负责供下述三类情况使用: 内部使用(排序、hash join、work table等) 外部使用(临时表,表变量等) 行版本控制(乐观并发控制)       而对于内部使用,一些比较复杂的查询中由于涉及到了大量的并行、排序等操作时就需要大量的内存空间,每一个查询在开始时都会由SQL Server预估需要多少内存,在具体的执行过程中,如果授予的内存不足,则需要将多出来的部分由TempDB处理,这也就是所谓的Spill to TempDB。     通过下述语句可以观察到某个查询对TempDB造成了多少读写: ......


    ▪在SQL Server中使用ISNULL执行空值判断查询

     有如下查询: 代码如下: select isnull(lastchanged,'') as lastchanged from vhhostlist where s_comment='test202' 本来,ISNULL函数只有一个参数,它表示的含义就是判断这个参数的值是否为NULL,是NULL则返回TRUE,否则返回FALSE; 但是,在SQLServer的查询语句中,isnull需要两个参数,它的含义是如果参数1为NULL,则以参数2为isnull函数的返回值; 即,上面查询的的含义是,查询vhhostlist表中s_comment='test202'那一行lastchanged字段的值,如果它为NULL,则返回一个空字符串; 记得以前不懂这个的时候,遇到这样一......


 


站内导航:


特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

©2012-2017,169IT.COM,E-mail:www_169it_com#163.com(请将#改为@)

浙ICP备11055608号