监测谁用了SQL Server的Tempdb空间

robin

转自：http://blogs.msdn.com/b/apgcdsd/archive/2011/02/11/sql-server-tempdb.aspx

Tempdb系统数据库是一个全局资源，供连接到SQL Server实例的所有用户使用。在现在的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)
用户对象由用户显式创建。这些对象可以位于用户会话的作用域中，也可位于创建对象所用例程的作用域中。可以是存储过程、触发器或用户定义函数。用户对象可以是下列项之一：

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

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

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

版本存储（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的空间。

select @@spid
go
use adventureworks
go
select getdate()
go
select * into #mySalesOrderDetail
from Sales.SalesOrderDetail
--创建一个temp table
--这个操作应该会申请user objects page
go
waitfor delay '0:0:2'
select getdate()
go
drop table #mySalesOrderDetail
--删除一个temp table
--这个操作后user object page数量应该会下降
go
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];
--这里做了一个比较大的join.
--应该会有internal objects的申请.
go
select getdate()
-- join语句做完以后internal objects page数目应该下降
go

那用什么脚本可用监视上面的行为呢？下面的脚本就可以监视和发现当前的Tempdb使用者。这个脚本需要在使用tempdb的语句开始运行之前开始。（读者当然可以根据自己的喜好，修改这个脚本。）
脚本首先用“dbcc showfilestats”语句查询当前tempdb的总体使用量。再查询sys.dm_db_file_space_usage视图，得到Tempdb里当前总共有多少用户对象、内部对象、以及版本存储。然后查询sys.dm_db_session_space_usage和sys.dm_exec_sessions，找到当前使用Tempdb的所有连接。最后通过sys.dm_exec_sql_text，找到这些连接正在运行的语句。

use tempdb
--每隔1秒钟运行一次，直到用户手工终止脚本运行
while 1=1
begin
select getdate()
--从文件级看tempdb使用情况
dbcc showfilestats
-- 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)*8as 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_usaget1 ,
--反映每个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)
-- 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
wheret1.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

http://blogs.msdn.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-01-42-89/4331.1.png
图1

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

http://blogs.msdn.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-01-42-89/3817.2.png
图2

在连接A的结果中（），可以得到四个时间。图片上的例子，是：
11:39:36.513--开始创建temp table
11:39:38.920 –开始删除temp table
11:39:40.937 –开始查询
11:39:45.733 –查询结束

连接B生成的是一个文本文件。利用一些有“列出所有包含某个特定字符串”行功能的编辑器工具，可以把每个命令结果挑出来。
从连接B生成的文本文件里所有dbcc showfilestats的结果（图3），可以看出tempdb的使用空间有过两次增长（从23到210，从47到118），中间有一次下降（从210到47）。

http://blogs.msdn.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-01-42-89/3005.3.png
图3

从连接B生成的文本文件里所有Query 1的结果（图3），我们可以看到有三段时间，user object和internal object空间有申请和释放动作。它们分别是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增长）。

http://blogs.msdn.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-01-42-89/7103.4.png
图4

从Query 2的结果（图4）可以看到Connection A在这三个时间段都处于运行状态。

http://blogs.msdn.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-01-42-89/8686.5.png
图5

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

http://blogs.msdn.com/resized-image.ashx/__size/550x0/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-01-42-89/4861.6.png
图6

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