如何监测谁用了SQL Server的Tempdb空间？

Tempdb 系统数据库是一个全局资源，供连接到 SQL Server 实例的所有用户使用。在现在的SQL Server里，其使用频率可能会超过用户的想象。如果Tempdb空间耗尽，许多操作将不能完成。

下面是3种 常见的 tempdb使用对象

用户对象(user_object_reserved_page_count)

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

• 用户定义的表和索引
• 系统表和索引
• 全局临时表和索引
• 局部临时表和索引
• table 变量
• 表值函数中返回的表

内部对象（internal_object_reserved_page_count）

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

• 用于游标。
• 用于哈希联接或哈希聚合操作的查询。
• 某些 GROUP BY、ORDER BY 或 UNION 查询的中间排序结果。

版本存储（version_store_reserved_page_count）

版本存储区主要用来支持Snapshot事务隔离级别，以及SQL 2005以后推出的一些其他提高数据库并发度的新功能。

由此可见，光从用户发过来的语句本身，是很难判断这个连接的操作是否会使用Tempdb的。一个典型的例子，就是某些查询。如果表格上有良好的索引做支持，SQLServer不需要做哈希联接(Hash Join)，那这个查询就不会用Tempdb。反之，如果表格很大，又没有好的索引，那Tempdb使用量就可能不小。

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

下面脚本 可以查询tempdb使用情况

-- 从文件级看tempdb使用情况
dbcc showfilestats                                                      
GO
-- Query 1
-- 返回所有做过空间申请的session信息

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                                                    
GO
-- 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 ,                               
-- 反映每个session累计空间申请
sys.dm_exec_sessions as t3
-- 每个session的信息
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)
GO
-- Query 3
-- 返回正在运行并且做过空间申请的session正在运行的语句
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)     
GO

参考地址：http://blogs.msdn.com/b/apgcdsd/archive/2011/02/11/sql-server-tempdb.aspx