走进SQL Server 2005 tempdb数据库

http://blog.csdn.net/CN_SQL/article/details/3867694

引言

 

    本文内容来源于一篇白皮书，但在原文的基础上增加了部分内容，为了表示对原文作者

的尊重，并未标示为原创，特此申明。目的只是希望能与各位网友一起分享好文章，如有撰写不好的地方，请多多原谅，也感谢各位的支持！

 

 

tempdb介绍

 

    在SQL Server2005里，tempdb系统数据库经历了许多的改变，包括一些新的用法以及内部的优化，但从SQL Server 2000到SQL Server 2005，其架构大部分是没有改变的。

 

    tempdb系统数据库与用户数据库是非常相似的。主要的区别在于：SQL Server关闭以后，存储在tempdb里的数据将会被自动清除掉。（曾经遇到这样一个网友，在过大年前把一些业务测试数据以及相关数据库对象存储在tempdb里，在过年期间，由于服务器应用程序等某种原因造成磁盘空间填满，SQL Server服务异常关闭，磁盘问题解决以后，重启SQL Server,由于没有备份，存储在tempdb里的所有数据都丢失，我们应该避免这样的问题。）

 

    每次SQL Server重启时，tempdb数据库会从model系统数据库那复制一份，并继承某些model数据库的属性，比如“ALLOW_SNAPSHOT_ISOLATION”属性，当然有些属性并不能

继承，比如“恢复模式”属性，无论model数据库是什么恢复模式，tempdb只能是“简单恢复模式”，这是以便始终自动回收 tempdb 日志空间  ，且我们尝试修改其恢复模式时，也会抛出错误如下，提示你不能修改。

 

Msg 5058, Level 16, State 1, Line 1

Option 'RECOVERY' cannot be set in database 'tempdb'.

 

  在tempdb中，不能创建和使用新的文件组:

 

    ALTER DATABASE tempdb

ADD filegroup tempdev2

GO

 

Msg 1826, Level 16, State 4, Line 1

User-defined filegroups are not allowed on "tempdb".

 

  也就意味着，tempdb只能使用默认的PRIMARY文件组，但可以为tempdb创建多个数据

文件和日志文件。数据文件的默认大小是8M，当SQL Server重新启动以后，tempdb又会

 

 

恢复到这个默认的配置值，而“自动增长“值只是临时性的，当SQL Server重新启动以后，又会重新恢复（最后这个关于自动增长值的说法，原文是这样：

Auto grow is temporary for tempdb (unlike other types of databases). It is reset when SQL Server restarts.）但经过我测试，并不是这样，重新启动以后，并不会重新恢复。

 

  用户可以在tempdb数据库创建和使用表，也可以使用事务和回滚事务。但是，不能在tempdb数据库中重做日志文件，这是因为一旦SQL Server重新启动，所有内容将不覆存在。因为事务日志并不会被闪回，所以在tempdb数据库中提交事务要比在用户数据库中快。在用户数据库中，事务具有ACID属性，但是在tempdb的事务中，并不具有。

 

  SQL Server使用tempdb存储一些内部对象，比如查询的中间结果集。大部分的这些内部操作不会生成日志纪录，因为它们不需要回滚，所以这些操作是非常快的。

 

  当然，在tempdb上也存在一些限制。另外的，一些数据库属性的操作不能使用在tempdb上，比如上面提到的修改恢复模型，更具体的内容，请参考联机帮助相关部分：

http://msdn.microsoft.com/en-us/library/ms190768.aspx

 

  不允许在tempdb上进行自动收缩。其数据库文件和日志文件收缩能力有限，这是因为存储在tempdb里的许多隐藏对象不能通过收缩操作除去。

 

  下面同样是一些tempdb的限制操作：

 

  .不能开启数据库的”CHECKSUM”属性。

  .不能创建数据库快照。

.不支持DBCC CHECKALLOC和DBCC CHECKCATALOG操作。

.最好是离线完成DBCC CHECKTABLE检查操作。这句话的意思是：它需要一个TAB-S锁。

当tempdb在使用时，会发生内部的一致性检查操作。如果检查失败，用户连接将会中断，使用了的空间会被中断的连接释放。

 

http://blog.csdn.net/cn_sql/article/details/3876521

上篇文章（http://blog.csdn.net/CN_SQL/archive/2009/02/07/3867694.aspx），我们对tempdb系统数据库有了一个新的认识,现在我们来慢慢对其进行深入的了解和掌握。

 

    首先，先介绍“tempdb的空间使用”，有下面几种类型的对象会占用tempdb的空间：

 

    .   内部对象

    .   版本存储

    .   用户对象

 

    内部对象

 

       内部对象是指由SQL Server系统内部自动创建的一些系统级别的对象，应用程序不能直接的从这些对象删除或者插入数据，系统对象的元数据存储在内存里面，并且不能通过象“sys.all_objects”这样的系统目录来进行查询，因此，我们可认为内部对象是一些隐藏对象。通常系统对象被用于：

 

    .   存储排序的中间临时结果集。

    .   存储HASH连接以及HASH聚合的中间结果集。

    .   存储XML变量或者象LOB这样的大对象类型变量。LOB数据类型包括：text, image, ntext, varchar(max),varbinary(max)以及其他。

         .        存储会利用到Spool运算符(如CTE查询)保存的中间结果集。

         .        存储键集驱动游标生成的工作表。

         .        通过静态游标存储查询结果。

         .        通过ServiceBroker存储传输中的消息。

         .        通过INSTEAD OF触发器存储数据以及内部的处理。

 

                   内部对象还可以用于下面这些的任何一个功能。比如，DBCC CHECK内部使用的查询需要利用到Spool运算符生成中间结果集，查询通知，以及事件通知，ServiceBroker，因此它们同样需要利用到tempdb的空间。

 

                   更新内部对象不会产生日志纪录。除非涉及排序单位，否则内部对象上页分配是不会生成日志纪录的。如果语句失败，这些对象将会被释放。每个tempdb上的内部对象至少占用9个页（包括1个IAM页和8个数据页的区）。

 

 

版本存储

 

       版本存储用于存储新特性中通过事务产生的行版本。例如快照隔离级别，触发器，MARS（多个活动的结果集）以及联机索引创建。在SQL Server 2005中，有两个版本的存储区。关于更多的相关信息，请参见联机帮助：

 

http://msdn.microsoft.com/en-us/library/ms175492.aspx

 

        版本存储由append-only存储单位构成。对于连续的插入和随机的查找，append-only存储单位是非常高效的。它们不会显示在系统目录，比如sys.all_objects。插入版本存储不会产生日志纪录。每个单位可以存储很多行的版本。如果有新的版本需要被存储，大约每1分钟就有1个新的存储单位被创建。

        由于版本存储涉及的内容比较多且复杂，在这里就不再一一论述，更多内容，请网友们自行参考联机丛书关于“版本存储”的章节。

 

 

用户对象

 

     可以在系统目录sys.all_objects找到用户对象。Sp_spaceused可以显示这些对象占用的空间。用户对象包括用户定义的表和索引以及系统表和索引。这两种类型的表在磁盘上的数据结构都完全一样。可以通过T-SQL语句对用户表进行操作，不能通过T-SQL语句直接的对系统表进行修改，它们对于使用系统目录是可见的。通常用户对象上的操作是可以被纪录日志的，包括BCP，BULK INSERT，SELECT INTO以及索引重建操作，这些行为和其他的简单恢复模型的数据库的行为是完全一样的。

     

      用户定义的表包括全局临时表例如##t，以及局部临时表例如#t.全局临时表会在其依赖的整个会话过期或者中止以后释放，而局部临时表只存在于其依赖的某种作用域。在另外一方面，也会伴随一个作用域的结束而被释放。（例如存储过程）局部临时表包括表变量@t,表值函数的返回值，以及那些使用了带有sort_in_tempdb选项的联机聚集索引创建所需要的Mapping索引。

 

  对于临时表的作用域，网友们经常遇到下面的这些问题，顺便在这里提出来：

 

  1．

 

     我们先看下面的代码：

 

        use tempdb

go

 

create proc p1

as

set nocount on

select top 1 * into #t from sys.objects

select * from #t

go

 

我们创建了个存储过程，先通过系统表插入1行纪录生成一个局部临时表#t,然后再通过SELECT语句查询这个临时表。

然后，我们创建完以后，通过执行:

 

exec p1

go

 

我们可以看到结果集，但如果，我们再次通过SELECT  查询语句对#t进行查询：

 

select * from #t

go

 

我们将会看到下面的错误信息，这是因为在存储过程的作用域完之后，这个局部临时表对象也随之被销毁了。

Msg 208, Level 16, State 0, Line 1

Invalid object name '#t'.

 

 

如果想在执行完存储过程以后，还可以进行SELECT查询，把局部临时表替换为全局临时表就可以了。

 

 

2．

 

    还是利用上面提到的存储过程p1

 

    create proc p1

as

set nocount on

 

select top 1 * into #t from sys.objects

select * from #t

go

 

突然某天，需求改变，创建临时表的源表需要改变或者需要适应可变的需求，那么我们肯定会考虑传入一个表名参数，使用动态语句来实现，代码如下：

 

alter proc p1

@table_name varchar(100)

as

set nocount on

 

declare @sql varchar(200)

 

set @sql = 'select top 1 * into #t from '+@table_name

exec(@sql)

select * from #t

go

 

 

然后我们执行存储过程：

 

exec p1 @table_name='sys.objects'

 

我们并未得到我们想要的结果，而是一个错误信息：

 

Msg 208, Level 16, State 0, Procedure p1, Line 9

Invalid object name '#t'.

 

这也是因为，这里的局部临时表只存在于动态语句内的作用域，对于这样的需求我们可以通过下面两个方式来解决：

 

第一种方式是通过将查询语句一并包含到动态语句里，代码如下：

 

alter proc p1

@table_name varchar(100)

as

set nocount on

 

declare @sql varchar(200)

 

set @sql = 'select top 1 * into #t from '+@table_name + '

select * from #t'

exec(@sql)

go

 

第二种方式则是将局部临时表修改为全局临时表##t。

 

3．

 

    我们来考虑全局临时表的作用域，我们做个测试，这个测试还是利用到最开始的那个存储过程P1，只不过代码我们把局部临时表调整为了全局临时表：

 

        alter proc p1

as

set nocount on

 

select top 1 * into ##t from sys.objects

select * from ##t

go

 

 

我们创建完这个存储过程以后，先在查询窗口1执行1次这个存储过程，那么按照我们之前说的，则会创建一个全局的的临时表，我们可以在执行完存储过程以后，依然可以使用SELECT语句对其进行查询。

然后我们试着重新开启第2，第3个……查询窗口，同样执行语句查询：

 

select * from ##t

 

我们依然可以看到我们想要的结果集。我们再次把查询窗口1关闭，然后再次在窗口2，3……执行上面这个查询代码，结果出错了。

 

通过这个测试，说明了什么，大家都已经很清楚了。

 

 

最后，谢谢各位看完这篇文章，下一次，我将介绍SQL Server 2005中tempdb的改进以及tempdb的磁盘空间需求，欢迎各位捧场，谢谢大家。