（译）走进SQL Server 2005 tempdb数据库（二）

上篇文章（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的磁盘空间需求，欢迎各位捧场，谢谢大家。