1.1 文件的分配方式及文件空间检查方法

谈到检查数据库空间大小，很多读者会想到一条命令：sp_spaceused。它显示行数、保留的磁盘空间以及当前数据库中的表、索引视图等数据库对象所使用的磁盘空间，或显示由整个数据库保留和使用的磁盘空间。语法定义是：

    sp_spaceused [[ @objname = ] 'objname' ]
    [,[ @updateusage = ] 'updateusage' ]

[ @objname =] 'objname'：请求分析其空间使用信息的表、索引视图或队列的名称。如果未指定objname，则返回整个数据库的结果。

所以，如果不带入任何参数，这条语句返回的是当前数据库的使用空间信息。如果我们在范例数据库AdventureWorks上面运行，返回的结果可以是：

    database_name     database_size      unallocated space
    ---------------- ------------------ ------------------
    AdventureWorks    178.75 MB          15.41 MB
    reserved          data               index_size        unused
    ---------------- ------------------ ---------------- ------------------
    165216KB         83656KB           76784KB          4776KB

可是这个结果并不能使人非常满意。首先，从这个结果中，无法直观地看出每个数据文件和日志文件的使用情况。其次，这个存储过程是依赖于SQL Server存储在一些系统视图里的空间使用统计信息来算出结果的。但是，SQL Server并不保证实时更新空间使用统计信息。尤其是数据库刚刚发生大的变化之后，sp_spaceused的结果常常不准确。另外这个系统存储过程的算法主要针对的是普通用户数据库。（如果有兴趣的话，读者可以运行“sp_helptext sp_spaceused”得到存储过程的定义，研究一下它的算法。）对于tempdb数据库里存储的一些系统临时数据对象，是无法用这个存储过程来统计的。所以，会出现tempdb报告空间用尽，而sp_spaceused却显示tempdb还有很多未使用的空间的现象。所以这个结果很有局限性，不是非常可靠。

那么怎么查看才能总是得到准确结果呢？这个要按不同的文件类型来考虑。SQL Server的文件分成数据文件（.mdf，.ndf）和日志文件（.ldf）两种。对于系统数据库tempdb，SQL Server的使用又和其他数据库有所不同。针对这些不同的对象，SQL Server提供了不同的手段来做空间使用分析。下面分别介绍。

1.1.1 数据文件分配

在介绍数据文件空间使用之前，我们先来讨论一些关于数据库空间组织架构的基本概念。了解这些概念，能够帮助我们理解数据库的空间分配。

SQL Server数据库有两种类型的数据文件。

1.主数据文件。

主数据文件是数据库的起点，存储指向数据库中的其他文件路径。每个数据库都有一个主数据文件。主数据文件的推荐文件扩展名是.mdf。

2.辅助数据文件。

除主数据文件以外的所有其他数据文件都是辅助数据文件。某些数据库可能不含有任何辅助数据文件，而有些数据库则含有多个辅助数据文件。辅助数据文件的推荐文件扩展名是.ndf。

SQL Server文件又有两种名字。

logical_file_name（逻辑文件名）

logical_file_name是在所有Transact-SQL语句中引用物理文件时所使用的名称。也就是文件在SQL Server里的名字。逻辑文件名必须符合SQL Server标识符规则，而且在数据库中的逻辑文件名中必须是唯一的。

os_file_name（物理文件名）

os_file_name是包括目录路径的物理文件名。也就是文件在操作系统里的名字和路径。它必须符合操作系统文件命名规则。

图1-1显示了在默认SQL Server 2005实例上创建的数据库的逻辑文件名和物理文件名示例。MyDB_Primary这样的名字，就是逻辑文件名。而以“c:\”开头的这些名字，是物理文件名。

图1-1 数据库的逻辑文件名和物理文件名

用户插入数据库里的那些数据记录在数据文件里是怎么存放的呢？这就要涉及页（Page）和区（Extent）这两个概念了。SQL Server中数据存储的基本单位是页。为数据库中的数据文件（.mdf或.ndf）分配的磁盘空间可以从逻辑上划分成页（从0到n连续编号）。磁盘I/O操作在页级执行。也就是说，SQL Server读取或写入数据的最小单位就是以8KB为单位的页。

区是8个物理上连续的页的集合，用来有效地管理页。所有页都存储在区中。

页

在SQL Server中，页的大小为8KB。这意味着SQL Server数据库中每MB有128页。每页的开头是一个96 B的页头，用于存储有关页的系统信息。包括页码、页类型、页的可用空间，以及拥有该页的对象的分配单元ID。不同类型的数据，存储在不同类型的页面里。

表1-1说明了SQL Server数据库的数据文件中的各种页类型，以及它们里面存储的数据类型。

表1-1 数据文件中的页面类型

在一个数据库里，绝大多数页面都是Data或者Text/Image类型。而在一个数据文件的开头，则分布了像Global Allocation Map、Shared Global Allocation Map、Page Free Space这样的管理页面。SQL Server通过这些页面知道这个数据文件中哪些页面已经被使用，哪些页面还没有被使用。所以这些页面数量虽少，但却是将数据库页面串联起来的至关重要的页面。

在正常数据页上，数据行紧接着页的标头按顺序放置。页的末尾是行偏移量表，对于页中的每一行，每个行偏移表都包含一个条目。每个条目记录对应行的第一个字节与页首的距离。行偏移表中的条目的顺序与页中行的顺序相反（见图1-2）。

图1-2 数据页结构

读到这里，可能读者会有点好奇，想看看一个页面到底是长什么样子的。SQL Server有一个DBCC PAGE命令可以按一定格式翻译输出指定的页面。（在不同的SQL Server版本里，它的输出格式可能稍有不同。）命令的格式是：

    DBCC PAGE(<db_id>, <file_id>, <page_id>, <format_id>)

比如要看AdventureWorks这个数据库里面的AdventureWorks_Data数据文件里的第3 230页。那就要先得到db_id和file_id。

Db_id可以从sp_helpdb的结果中得到。这里是5（见图1-3）。

图1-3 sp_helpdb结果

File_id可以从sp_helpfile的结果中得到。这里是1（见图1-4）。

图1-4 sp_helpfile结果

Format_id是你指定的输出格式，有1、2、3三个值。一般来讲，3这个输出格式比较直观。在运行DBCC PAGE之前，还需要打开跟踪标志（trace flag）3604。

运行这条命令能够显示这个页面上有些什么内容。先看看Messages里面的文本输出（见图1-5）。

图1-5 DBCC PAGE文本输出

这里面有很多有意思的信息。比如:

    Metadata: ObjectId = 414624520
    Metadata: IndexId = 2

它告诉我们，这个页面属于414624520这个对象，ID为2的索引（index）。运行下面的语句可以看到它是什么（见图1-6）。

图1-6 根据ObjectId和IndexId找到表格和索引

原来它是[Production].[WorkOrder]的索引IX_WorkOrder_ScrapReasionID。这个索引建立在ScrapReasonID这个列上。

我们再来看看DBCC PAGE的网格输出（见图1-7）。

图1-7 DBCC PAGE网格输出

这里显示出3230这个页面是索引IX_WorkOrder_ScrapReasionID在第一层子树上面的一个页面。它里面包含了索引列（Index Column）（ScrapReasonID）的值。由于它是一个非聚集索引，它还存储了指向聚集索引的指针（WorkOrderID）。

这里输出的内容经过了DBCC PAGE的翻译。如果想看页面上原有的内容，可以选用2这个参数。当然得到的结果就不这么容易懂了（见图1-8）。

图1-8 DBCC PAGE的另一种输出

大型行支持

在SQL Server中，行不能跨页，属于同一行的所有字段的数据都要放在同一个页面里。页的最大数据量是8 060 B（8KB）。所以一般数据类型字段所组成的一行，最长加起来不能超过8KB。

但是，这一限制不包括Text/Image类型字段的数据。这些类型字段的数据会被单独存放在LOB（Large Object）页面里。

在SQL Server 2005中更进了一步，提供了varchar(max)、nvarchar(max)、varbinary(max)这样的数据类型，使得varchar、nvarchar、varbinary这样传统的数据类型字段，也可以像Text/Image字段一样突破8KB的限制，方便了用户的使用。如果包含这一类数据类型字段的行，总长不超过8KB，数据还是会一起存放在普通数据页面里。如果总长超过了8KB，SQL Server就会把这些字段的数据分开，单独存放在一种叫Row-Overflow（行溢出）的页面里。

区

区是管理空间的基本单位。一个区是8个物理上连续的页（即64KB）。这意味着SQL Server数据库中每MB有16个区。

为了使空间分配更有效，SQL Server不会将所有区分配给包含少量数据的表。SQL Server有两种类型的区：

● 统一区，由单个对象所有。区中的所有8页只能由一个对象使用。

● 混合区，最多可由8个对象共享。区中8页的每页可由不同的对象所有。但是一页总是只能属于一个对象。

通常从混合区向新表或索引分配页。当表或索引增长到8页时，将变成使用统一区进行后续分配。如果对现有表创建索引，并且该表包含的行足以在索引中生成8页，则对该索引的所有分配都使用统一区进行（见图1-9）。

图1-9 统一区和混合区

所以说，表格和索引所有第8个页面以后的页面都会分布在统一区内。在这种区里，所有的页面都属于同一个对象。这也意味着，在一个大的数据库里，绝大多数区都会是统一区。一个区里面的所有页面都属于一个表或索引。统计区的数目，大致就能知道表格或者索引所占的空间数目。

1.1.2 数据文件空间使用的计算方法

了解一个数据库空间使用的最简单方法，就是在Management Studio里，右键点击数据库名字，选择“Reports”-“Standard Reports”，默认就有4个Disk Usage的报表。它们能很好地统计出从不同角度分析的数据库空间使用情况（见图1-10）。

图1-10 Management Studio的Disk Usage报表

细心的读者可能会发现，这里的结果和sp_spaceused给出的稍有不同。严格来讲，这和统计的单位有关系。比如是准备按照区为单位进行统计呢，还是按照页面为单位进行统计?由于每个区有8个页面，这8个页面不一定都被使用到了。那些没有使用到的页面到底算不算呢？如果按照区来统计，这8个页面就都算是使用了的空间。如果按照页面来统计，没有使用的页面就可以不算。所以这两种统计的方法得到的值会稍有不同。

按照区统计

SQL Server有一个DBCC命令按照区的使用情况统计数据文件的使用大小。Management Studio的报表在这里就调用了这个命令。DBCC语句运行的结果如图1-11所示。

    DBCC SHOWFILESTATS
    GO

图1-11 DBCC SHOWFILESTATS结果

这个命令能直接从GAM和SGAM这样的系统分配页面上面读取区分配信息，直接算出数据库文件里有多少区已被分配。它的优点是能够快速准确地计算出一个数据库数据文件区的总数和已使用过的区的数目。由于SQL Server在绝大多数时间都是按照区为单位来分配新空间的，而系统分配页上的信息永远是实时更新的，所以这种统计方法比较准确可靠。在服务器负载很高的情况下也能安全执行，不会增加额外系统负担。所以要考察数据库数据文件级的使用情况，它是个比较好的选择。

按照页面统计

如果想要知道某个具体的表格或者索引使用了多少空间，就要从页面这个级别来分析。在这里，可以选择sp_spaceused或者DBCC SHOWCONTIG（SQL Server 2005中可以使用sys.dm_db_index_physical_stats）中的一个。这两种方法，各有不同。

Sp_spaceused是根据sys.allocation_units和sys.partitions这两张管理视图来计算存储空间的。有时候（例如删除索引后、表的空间信息不是当前信息时），这两张表可能不能及时反映出数据库的准确信息。可以加入updateusage这个参数，要求SQL Server为这句指令更新管理视图里的统计信息。可是，这样做对一个比较大的数据库来讲，有可能是一件消耗资源的工作。当SQL Server非常繁忙的时候，最好不要用这种任务来打搅它。

Sp_spaceused的另一个缺点是一次只能查询一个对象。我们无法一次对数据库内所有的对象进行统计。然而可以通过直接查询sys.dm_db_partition_stats以及相关的管理视图来达到这个目标。下面就是一个例子（结果如图1-12所示）：

    SELECT o.name ,
    SUM (p.reserved_page_count) AS reserved_page_count,
    SUM (p.used_page_count) AS used_page_count,
    SUM (
    CASE
    WHEN (p.index_id < 2) THEN (p.in_row_data_page_count +
    p.lob_used_page_count + p.row_overflow_used_page_count)
    ELSE p.lob_used_page_count + p.row_overflow_used_page_count
    END
    ) AS DataPages,
    SUM (
    CASE
    WHEN (p.index_id < 2) THEN row_count
    ELSE 0
    END
    ) AS rowCounts
    FROM sys.dm_db_partition_stats p INNER JOIN sys.objects o
    ON p.object_id = o.object_id
    GROUP BY o.name

图1-12 sys.dm_db_partition_stats查询结果

总之，sp_spaceused是一种使用简单，但是功能比较弱，也不是最准确的方法。不特别推荐用sp_spaceused来检查数据库空间使用情况。查系统管理视图sys.dm_db_partition_stats会来得更直接一些。

DBCC SHOWCONTIG（或者sys.dm_db_index_physical_stats）是检查数据空间分配情况的另一种方法，也可以说是最精确的方法。它可以告诉你某张表（或索引）用了多少页面、多少区，甚至页面上的平均数据量。从这些值可以算出一张表格占用了多少空间。然而，得到这些精确的数据也是要付出代价的。SQL Server从整体性能的角度出发，不可能一直维护这样底层的统计信息。为了完成这个命令，SQL Server必须要对数据库进行扫描。而扫描的过程中，SQL Server是要加锁的。例如sys.dm_db_index_physical_stats，它就有3种扫描模式。要得到的结果越精确，扫描的范围就越大。

● LIMITED模式运行最快，扫描的页数最少。对于堆，它将扫描所有页，但对于索引，则只扫描叶级上面的父级别页。

● SAMPLED模式将返回基于索引或堆中所有页的1％样本的统计信息。如果索引或堆少于10 000页，则使用DETAILED模式代替SAMPLED。

● DETAILED模式将扫描所有页并返回所有统计信息。

所以说，这种方式虽然精确，但是在数据库处于工作高峰时应避免使用。

小结

在这一节里，一共介绍了5种分析数据文件存储空间的方法。表1-2比较了它们的优缺点和使用特点。

表1-2 5种数据文件存储空间分析方法比较

如果管理者只要看数据文件的整体使用情况，DBCC SHOWFILESTATS是比较好的选择。如果要看每个对象的空间使用情况，可以使用动态管理视图sys.dm_db_partition_stats。如果还想了解每个页、每个区的使用情况、碎片程度，那DBCC SHOWCONTIG是比较好的选择。

1.1.3 日志文件

数据库的另一种文件类型是日志文件。日志文件包含用于恢复数据库的所有日志信息。每个数据库必须至少有一个日志文件，当然也可以有多个。日志文件的推荐文件扩展名是.ldf。与数据文件按8KB组织不同，日志文件不是按页/区来组织的。

SQL Server数据库引擎在内部将每一物理日志文件分成多个虚拟日志单元。虚拟日志单元没有固定大小，且一个物理日志文件所包含的虚拟日志单元数不固定。管理员不能配置或设置虚拟日志单元的大小或数量。但SQL Server会尝试控制虚拟日志单元的数目，把它限制在一个合理的范围里。例外情况是，日志文件每自动增长一次，会至少增加一个虚拟日志单元。所以，如果一个日志文件经历了多次小的自动增长，里面的虚拟日志单元数目会比正常的日志文件多很多。这种情况会影响到日志文件管理的效率，甚至造成数据库启动要花很长时间。

事务日志是一种回绕的文件。例如，假设有一个数据库，它包含一个分成5个虚拟日志单元的物理日志文件。当创建数据库时，逻辑日志文件从物理日志文件的始端开始。新日志记录被添加到逻辑日志的末端，然后向物理日志的末端扩张。事务日志的外观与图1-13所示相似。

图1-13 事务日志的外观

当逻辑日志的末端到达物理日志文件的末端时，新的日志记录将回绕到物理日志文件的始端，继续向后写（见图1-14）。

图1-14 事务日志循环使用

查看日志文件的使用情况非常简单。当然，不能用sp_spaceused。要用如下代码，结果如图1-15所示。

    DBCC SQLPERF(LOGSPACE)

图1-15 DBCC SQLPERF(LOGSPACE)结果

图1-15清楚地显示了SQL Server上所有数据库的日志大小，以及使用比率。语句的执行不会对SQL Server带来负担，这个语句返回的结果总是准确的。可以随时在SQL Server上运行这句话。

1.1.4 Tempdb

Tempdb系统数据库是一个全局资源，可供连接到SQL Server实例的所有用户使用，在SQL Server 2005中，用于保存下列各项：

用户对象

用户对象由用户显式创建。这些对象可以位于用户会话的作用域中，也可位于创建对象所用例程的作用域中。例程可以是存储过程、触发器或用户定义函数。用户对象可以是下列项之一：

● 用户定义的表和索引。

● 系统表和索引。

● 全局临时表和索引。

● 局部临时表和索引。

● table变量。

● 表值函数中返回的表。

内部对象

内部对象是根据需要由SQL Server数据库引擎创建的，用于处理SQL Server语句。内部对象可以在语句的作用域中创建和删除。内部对象可以是下列项之一：

● 用于游标或假脱机操作以及临时大型对象（LOB）存储的工作表。

● 用于哈希联接或哈希聚合操作的工作文件。

● 用于创建或重新生成索引等操作（如果指定了SORT_IN_TEMPDB）的中间排序结果，或者某些GROUP BY、ORDER BY或UNION查询的中间排序结果。

每个内部对象至少使用9页：一个IAM页，一个8页的区。

版本存储区

版本存储区是数据页的集合，它包含支持使用行版本控制的功能所需的数据行。主要用来支持快照（Snapshot）事务隔离级别，以及SQL Server 2005推出的一些其他提高数据库并发度的新功能。在SQL Server 2005中，有两个版本存储区：公用版本存储区和联机索引生成版本存储区。版本存储区包含下列项：

● 由使用快照隔离级别或已提交读隔离级别（基于行版本控制）的数据库中的数据修改事务生成的行版本。

● 由数据修改事务为实现联机索引操作、多个活动的结果集（MARS）以及AFTER触发器等功能而生成的行版本。

在SQL Server 2005以后，tempdb需要的磁盘空间会多于早期版本。因此当升级到SQL Server 2005或SQL Server 2008后，管理员提供的tempdb磁盘空间可能得比以前要大一些，用以容纳当前生产工作负荷并满足使用tempdb的SQL Server功能的额外空间要求。

Tempdb空间使用跟踪

Tempdb空间使用的一大特点，是只有一部分对象，例如用户创建的临时表、table变量等，可以用sys.allocation_units和sys.partitions这样的管理视图来管理。许多内部对象和版本存储在这些管理视图里没有体现。所以，sp_spaceused的结果和真实使用情况会有很大差异，tempdb的空间使用是不能用sp_spaceused来跟踪的。

DBCC SHOWFILESTATS当然还是可以反映数据文件使用的整体情况的。但是这些空间是被谁以什么样的方式用掉的呢？在SQL Server 2000的时候，这是个很难回答的问题。

SQL Server 2005后，引入了一张新的管理视图：sys.dm_db_file_space_usage（见表1-3）。这张视图能反映tempdb在如下几个大类里的空间使用分布。

表1-3 sys.dm_db_file_space_usage字段列

通过对这个表的监视，就能知道tempdb的空间是被哪一块对象使用掉的，是用户对象（user_object_reserved_page_count），还是系统对象（internal_object_reserved_page_count），还是版本存储区（version_store_reserved_page_count）。在1.1.5节的案例分析里，会对这种方法有所展示。

另一个问题是tempdb初始大小设多少比较好呢？tempdb在生产环境中的适当大小取决于多种因素，所以这个问题无法给出固定的答案。如本主题中前面所述，这些因素包括现有工作负荷以及使用的SQL Server功能。所以每个SQL Server都会不一样。同一个SQL Server，如果新加入了某个会使用tempdb的功能，其空间使用也会发生变化。建议通过在SQL Server测试环境中执行下列任务来分析现有的工作负荷：

（1）设置tempdb的自动增长。

（2）模拟各个单独的查询或工作任务，同时监视tempdb空间使用。

（3）模拟执行一些系统维护操作，例如，重新生成索引，同时监视tempdb空间。

（4）使用前面2和3步中tempdb空间使用值来预测总的工作负荷下，会使用多少空间；并针对计划的并发度调整此值。例如，如果一个任务会使用10 GB的tempdb空间，而在生产环境里，最多可能会有4个这样的任务同时运行，那就要至少预留40 GB的空间。

（5）根据第4步得到的值，设置tempdb在生产环境下的初始大小。同时也开启自动增长。

Tempdb文件个数以及大小设置，不但要满足用户任务的需求，还要考虑到性能优化。在11.4节里，会有进一步的讨论。

1.1.5 案例：通过脚本监视tempdb空间使用

在实际使用中，有时候会遇到tempdb数据库在很短一段时间里，莫名其妙地增长了很多的情况。数据库管理员要能解释，到底是什么样的操作导致了SQL Server要这样大规模地使用tempdb。是一种正常的行为，还是某种异常。

监视SQL Server的行为当然可以借助于SQL Trace来完成。可是管理员并不能预期造成大量使用tempdb的语句会在什么时候运行。这些语句可能一周只做一次。而且开跟踪毕竟对SQL Server来讲是个比较昂贵的动作。（参见第14章的讨论）就算没有性能副作用，如果一直开着SQL Trace，也会产生很大量的跟踪文件，对硬盘是个比较重的负担。

那怎么能够比较“轻量级”地对SQL Server进行监视呢？比较经济的方法，是以一定的间隔时间运行能够监视系统运行状况的DBCC命令、查询管理视图（DMV）以及管理函数（DMF）等，把结果输出到一个文件里。这样的监视方法相对安全。大部分时候信息量也足够进行问题定位。所以在一个很繁忙的SQL Server系统上，这是个不错的选择。

下面以一个实例，讨论一下如何用DBCC命令、管理视图（DMV）以及管理函数（DMF）来监视是什么语句使用了tempdb。

为了使结果简单，我们在测试之前先把SQL Server重启一次。

然后我们在Management Studio里做一个连接（连接A），将下面语句输入。

    select @@spid
    go
    use adventureworks
    go
    select getdate()
    go
    select * into #mySalesOrderDetail
    from Sales.SalesOrderDetail
    -- 创建一个临时表
    -- 这个操作应该会申请用户对象页面
    go
    waitfor delay '0:0:2'
    select getdate()
    go
    drop table #mySalesOrderDetail
    -- 删除一个临时表
    -- 这个操作后用户对象页面数量应该会下降
    g
    o
    waitfor delay '0:0:2'
    select getdate()
    go
    select top 100000 * from
    [Sales].[SalesOrderDetail]
    INNER JOIN [Sales].[SalesOrderHeader]
    ON [Sales].[SalesOrderHeader] .[SalesOrderID] =
    [Sales].[SalesOrderHeader].[SalesOrderID];
    -- 这里做了一个比较大的联接
    -- 应该会有系统对象的申请.
    go
    select getdate()
    -- join 语句做完以后系统对象页面数目应该下降
    go

那用什么脚本可以监视上面的行为呢？下面是一个例子。读者当然可以根据自己的喜好，修改这个脚本。

    use tempdb
    -- 每隔1秒钟运行一次，直到用户手工终止脚本运行
    while 1=1
    begin
    select getdate()
    -- 从文件级看tempdb使用情况
    dbcc showfilestats
    -- Query 1
    -- 返回所有做过空间申请的会话信息
    Select 'Tempdb' as DB, getdate() as Time,
        SUM (user_object_reserved_page_count)*8 as user_objects_kb,
        SUM (internal_object_reserved_page_count)*8 as internal_objects_kb,
        SUM (version_store_reserved_page_count)*8  as version_store_kb,
        SUM (unallocated_extent_page_count)*8 as freespace_kb
    From sys.dm_db_file_space_usage
    Where database_id = 2
    -- Query 2
    -- 这个管理视图能够反映当时tempdb空间的总体分配
    SELECT t1.session_id,
    t1.internal_objects_alloc_page_count,  t1.user_objects_alloc_page_count,
    t1.internal_objects_dealloc_page_count , t1.user_objects_dealloc_page_count,
    t3.*
    from sys.dm_db_session_space_usage  t1 ,
    -- 反映每个会话累计空间申请
    sys.dm_exec_sessions as t3
    -- 每个会话的信息
    where
    t1.session_id = t3.session_id
    and (t1.internal_objects_alloc_page_count>0
    or t1.user_objects_alloc_page_count >0
    or t1.internal_objects_dealloc_page_count>0
    or t1.user_objects_dealloc_page_count>0)
    -- Query 3
    -- 返回正在运行并且做过空间申请的会话正在运行的语句
    SELECT t1.session_id,
    st.text
    from sys.dm_db_session_space_usage as t1,
    sys.dm_exec_requests as t4
    CROSS APPLY sys.dm_exec_sql_text(t4.sql_handle) AS st
    where  t1.session_id = t4.session_id
        and t1.session_id >50
    and (t1.internal_objects_alloc_page_count>0
    or t1.user_objects_alloc_page_count >0
    or t1.internal_objects_dealloc_page_count>0
    or t1.user_objects_dealloc_page_count>0)
    waitfor delay '0:0:1'
    end

在运行这个脚本的连接（连接B）里（见图1-16），我们选择好“Results to File”。先开始运行它，指定输出文件路径。然后，我们再运行连接A（见图1-17）。连接A运行结束后，手工停止连接B的运行。

图1-16 检测tempdb使用的脚本（连接B）

图1-17 测试脚本输出（连接A）

在连接A的结果中（见图1-17），可以得到4个时间。图片上的例子，是：

11:39:36.513—开始创建temp table

11:39:38.920—开始删除temp table

11:39:40.937—开始查询

11:39:45.733—查询结束

连接B生成的是一个文本文件。利用一些有“列出所有包含某个特定字符串行”功能的编辑器工具，可以把每个命令结果挑出来。

从连接B生成的文本文件里所有DBCC SHOWFILESTATS的结果（见图1-18），可以看出tempdb的使用空间有过两次增长（从23到210，从47到118），中间有一次下降（从210到47）。

图1-18 Tempdb数据文件使用变化

从连接B生成的文本文件里所有Query 1的结果（见图1-19），我们可以看到有3段时间，用户对象和系统对象空间有申请和释放动作。它们分别是11:39:36-11:39:37（user_objects_kb增长），11:39:40-11:39:41（user_objects_kb下降），11:39:40-11:39:43（internal_objects_kb增长）。

图1-19 Tempdb使用量随时间变化

从Query 2的结果（见图1-20）可以看到连接A在这3个时间段都处于运行状态。

图1-20 连接A在这3个时间段都处于运行状态

根据时间，可以从Query 3的结果（见图1-21）里找到连接A当时正在运行的语句。例如在11:39:40-11:39:43（internal_objects_kb增长）这段时间里，一直都在运行下面这句话：

图1-21 连接A当时正在运行的语句

从上面的结果可以看出，连接A的语句中，用tempdb最多的时间点在11:39:41和11:39:42之间，连接正在做图1-21里面的那条查询语句。SQL Server需要空间存放一些内部对象，来完成Inner Join。

读者可以自己试试，用类似的方法监视自己的SQL Server。