如何监测谁用了SQL Server的Tempdb空间？
作为一个支持工程师，会被经常问到象“我的Tempdb为什么这么大？”“是谁把我的Tempdb空间用完的？”在SQL 2000的时候，这个问题很难回答。好在SQL 2005以后，引入了一张新的管理视图：sys.dm_db_file_space_usage。通过查询这张视图，能了解tempdb的空间使用情况，能知道tempdb的空间是被哪一块对象使用掉的，是用户对象(user_object_reserved_page_count字段)，还是系统对象（internal_object_reserved_page_count字段），还是版本存储区（version_store_reserved_page_count字段）。
在讨论Tempdb空间使用之前，我们先简单介绍一下通常什么操作会大量使用Tempdb。在SQL 2005和SQL 2008里，使用Tempdb空间的远远不止是临时表。常见的使用对象有：
用户对象(user_object_reserved_page_count)
用户对象由用户显式创建。这些对象可以位于用户会话的作用域中，也可位于创建对象所用例程的作用域中。 可以是存储过程、触发器或用户定义函数。 用户对象可以是下列项之一：
内部对象（internal_object_reserved_page_count）
内部对象是根据需要由 SQL Server 数据库引擎创建的，用于处理 SQL Server 语句。 内部对象可以在语句的作用域中创建和删除。 内部对象可以是下列项之一：
版本存储（version_store_reserved_page_count）
版本存储区主要用来支持Snapshot事务隔离级别，以及SQL 2005以后推出的一些其他提高数据库并发度的新功能。
由此可见，光从用户发过来的语句本身，是很难判断这个连接的操作是否会使用Tempdb的。一个典型的例子，就是某些查询。如果表格上有良好的索引做支持，SQL Server不需要做哈希联接(Hash Join)，那这个查询就不会用Tempdb。反之，如果表格很大，又没有好的索引，那Tempdb使用量就可能不小。
tempdb空间使用的一大特点，是只有一部分对象，例如用户创建的临时表、table变量等，可以用sys.allocation_units 和sys.partitions这样的管理视图来管理。许多内部对象和版本存储在这些管理视图里没有体现。所以，sp_spaceused的结果和真实使用会有很大差异，tempdb的空间使用是不能用sp_spaceused来跟踪的。必须借助sys.dm_db_file_space_usage这样的管理视图和管理函数，才能看到全貌。
下面以一个实例，讨论一下如何用DBCC命令、管理视图(DMV)以及管理函数(DMF)来监视是什么语句正在使用tempdb。
为了使结果简单，我们在测试之前先把SQL Server重起一次。
然后我们在Management Studio里做一个连接（连接A），将下面语句输入。这些语句会使用tempdb的空间。
在运行这个脚本的连接（连接B）里，我们选择好“ 将结果保存到文本”。先开始运行它，指定输出文件路径。然后，我们再运行连接A（图2）。连接A运行结束后，手工停止连接B的运行。连接B生成的是一个文本文件。文本里面可以看出tempdb的使用空间有过增长和下降。结果我在这里不做过多的分析，有兴趣的可以尝试一下
如果没有太好的分析，可以创建几个表，然后把查询结果插入到表里面，然后对表进行分析。从而来确定导致你tempdb 暴增的原因。下面附上建表的脚本，供大家参考使用
-- 1-- 创建表 tb_showfilestats 记录 DBCC showfilestats 返回的信息IF OBJECT_ID('tb_showfilestats' , 'U') IS NOT NULL DROP TABLE tb_showfilestatsGO CREATE TABLE tb_showfilestats ( id INT IDENTITY(1,1) PRIMARY KEY, Fileid INT , FileGroup INT , TotalExtents INT , UsedExtents INT , Name VARCHAR(100) , FILENAME VARCHAR(255) )GO -- 2-- 创建表 tb_db_file_space_usage 记录 所有做过空间申请的 session 信息 IF OBJECT_ID('tb_db_file_space_usage' , 'U') IS NOT NULL DROP TABLE tb_db_file_space_usageGOCREATE TABLE tb_db_file_space_usage ( id INT IDENTITY(1,1) PRIMARY KEY, database_name VARCHAR(50) , InDate DATETIME , user_objects_mb DECIMAL(18,4) , internal_objects_mb DECIMAL(18,4) , version_store_kb DECIMAL(18,4) , freespace_mbFILENAME DECIMAL(18,4) )GO-- 3-- 创建表 tb_db_session_space_usage 记录正在运行的 tempdb 空间的总体分配IF OBJECT_ID('tb_db_session_space_usage' , 'U') IS NOT NULL DROP TABLE tb_db_session_space_usageGOCREATE TABLE tb_db_session_space_usage ( id INT IDENTITY(1,1) PRIMARY KEY, session_id INT , internal_objects_alloc_page_count INT , user_objects_alloc_page_count INT , internal_objects_dealloc_page_count INT , user_objects_dealloc_page_count INT , login_time DATETIME , login_name VARCHAR(100) , host_name NVARCHAR(128) , nt_domain NVARCHAR(128) , nt_user_name NVARCHAR(128) , program_name NVARCHAR(128) , status VARCHAR(50) , client_interface_name NVARCHAR(32) , cpu_time INT , memory_usage INT , total_scheduled_time INT , total_elapsed_time INT , last_request_start_time DATETIME , last_request_end_time DATETIME , reads INT , writes INT , logical_reads INT , is_user_process BIT , row_count BIGINT , prev_error INT , original_security_id VARBINARY(85) , original_login_name NVARCHAR(128) , last_successful_logon DATETIME , last_unsuccessful_logon DATETIME , unsuccessful_logons BIGINT , group_id INT )GO-- 4-- 记录正在运行并且做过空间申请的session正在运行的语句.IF OBJECT_ID('tb_db_sql_text' , 'U') IS NOT NULL DROP TABLE tb_db_sql_textGOCREATE TABLE tb_db_sql_text ( id INT IDENTITY(1,1) PRIMARY KEY, session_id VARCHAR(50) , text VARCHAR(MAX), InDate DATETIME )GO-- 5-- 记录正在运行的活动的空间使用情况以及语句内容和执行计划.IF OBJECT_ID('tb_task_space_usage' , 'U') IS NOT NULL DROP TABLE tb_task_space_usageGOCREATE TABLE tb_task_space_usage ( id INT IDENTITY(1 , 1)PRIMARY KEY , session_id INT , internal_object_space_MB DECIMAL(18 , 4) , internal_object_dealloc_space_MB DECIMAL(18 , 4) , text VARCHAR(MAX) , statement_text VARCHAR(MAX) , query_plan XML )
