[转]如何监测谁用了SQL Server的Tempdb空间

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的空间。

 

[sql] view plaincopyprint?
  1. USE [My_Test_DB] 
  2. go 
  3.  
  4. SELECT  GETDATE() 
  5. go 
  6.  
  7. SELECT  * 
  8. INTO    #MyOrderSource 
  9. FROM   OrderSource AS o 
  10.  
  11. -- 创建一个temp table,OrderSource 是一个很大的表这样效果更明显  
  12. -- 这个操作应该会申请user objects page  
  13. go 
  14.  
  15. WAITFOR DELAY '0:0:2' 
  16.  
  17. SELECT  GETDATE() 
  18. go 
  19.  
  20. DROP TABLE #MyOrderSource 
  21.  
  22. -- 删除一个temp table  
  23. -- 这个操作后user object page数量应该会下降  
  24.  
  25. go 
  26.  
  27. WAITFOR DELAY '0:0:2' 
  28.  
  29. SELECT  GETDATE() 
  30. go 
  31.  
  32. SELECT TOP 100000* 
  33. FROM    OrderSource AS o   
  34. JOIN shippingOrder AS s ON o.OrderId=b.OrderId 
  35.  
  36.  
  37. -- 这里做了一个比较大的join.  
  38.  
  39. -- 应该会有internal objects的申请.  
  40.  
  41. go 
  42.  
  43. SELECT  GETDATE() 
  44.  
  45. -- join 语句做完以后internal objects page数目应该下降  
USE [My_Test_DB]
go

SELECT  GETDATE()
go

SELECT  *
INTO    #MyOrderSource
FROM   OrderSource AS o

-- 创建一个temp table,OrderSource 是一个很大的表这样效果更明显
-- 这个操作应该会申请user objects page
go

WAITFOR DELAY '0:0:2'

SELECT  GETDATE()
go

DROP TABLE #MyOrderSource

-- 删除一个temp table
-- 这个操作后user object page数量应该会下降

go

WAITFOR DELAY '0:0:2'

SELECT  GETDATE()
go

SELECT TOP 100000*
FROM    OrderSource AS o  
JOIN shippingOrder AS s ON o.OrderId=b.OrderId


-- 这里做了一个比较大的join.

-- 应该会有internal objects的申请.

go

SELECT  GETDATE()

-- join 语句做完以后internal objects page数目应该下降

 

那用什么脚本可用监视上面的行为呢？下面的脚本就可以监视和发现当前的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，找到这些连接正在运行的语句。

[sql] view plaincopyprint?
  1. USE tempdb                                                                
  2.  
  3. -- 每隔1秒钟运行一次，直到用户手工终止脚本运行  
  4.  
  5. WHILE 1 = 1  
  6. BEGIN                                                                    
  7.  
  8. -- Query 1  
  9. -- 从文件级看tempdb使用情况  
  10. DBCC showfilestats                                                  
  11.  
  12. -- Query 2  
  13. -- 返回所有做过空间申请的session信息  
  14. SELECT  'tempdb' AS database_name , GETDATE() AS Time , SUM(user_object_reserved_page_count)/128. AS user_objects_mb , 
  15.         SUM(internal_object_reserved_page_count)/128. AS internal_objects_mb , SUM(version_store_reserved_page_count)/128. AS version_store_kb , 
  16.         SUM(unallocated_extent_page_count)/128. AS freespace_mb 
  17. FROM    sys.dm_db_file_space_usage 
  18. WHERE   database_id = 2                                                     
  19.  
  20. -- Query 3  
  21. -- 这个管理视图能够反映当时tempdb空间的总体分配  
  22. SELECT  t1.session_id , t1.internal_objects_alloc_page_count , t1.user_objects_alloc_page_count , t1.internal_objects_dealloc_page_count , 
  23.         t1.user_objects_dealloc_page_count , t3.login_time , t3.login_name , t3.host_name , t3.nt_domain , t3.nt_user_name , t3.program_name , 
  24.         t3.status , t3.client_interface_name , t3.cpu_time , t3.memory_usage , t3.total_scheduled_time , t3.total_elapsed_time , 
  25.         t3.last_request_start_time , t3.last_request_end_time , t3.reads , t3.writes , t3.logical_reads , t3.is_user_process , t3.row_count , 
  26.         t3.prev_error , t3.original_security_id , t3.original_login_name , t3.last_successful_logon , t3.last_unsuccessful_logon , 
  27.         t3.unsuccessful_logons , t3.group_id 
  28. FROM    sys.dm_db_session_space_usage t1 ,                                
  29. -- 反映每个session累计空间申请  
  30.         sys.dm_exec_sessions AS t3 
  31. -- 每个session的信息  
  32. WHERE   t1.session_id = t3.session_id 
  33.         AND ( 
  34.              t1.internal_objects_alloc_page_count > 0 
  35.              OR t1.user_objects_alloc_page_count > 0 
  36.              OR t1.internal_objects_dealloc_page_count > 0 
  37.              OR t1.user_objects_dealloc_page_count > 0 
  38.             ) 
  39.  
  40. -- Query 4  
  41. -- 返回正在运行并且做过空间申请的session正在运行的语句  
  42. SELECT  t1.session_id , st.text , GETDATE() 
  43. FROM    sys.dm_db_session_space_usage AS t1 , 
  44.         sys.dm_exec_requests AS t4 
  45. CROSS APPLY sys.dm_exec_sql_text(t4.sql_handle) AS st 
  46. WHERE   t1.session_id = t4.session_id 
  47.         AND t1.session_id > 50 
  48.         AND ( 
  49.              t1.internal_objects_alloc_page_count > 0 
  50.              OR t1.user_objects_alloc_page_count > 0 
  51.              OR t1.internal_objects_dealloc_page_count > 0 
  52.              OR t1.user_objects_dealloc_page_count > 0 
  53.             )  
  54. -- Query 5  
  55. -- 返回正在运行的活动的空间使用情况以及语句内容和执行计划   
  56. ;WITH task_space_usage AS ( 
  57.     -- SUM alloc/delloc pages  
  58.     SELECT session_id, 
  59.            request_id, 
  60.            SUM(internal_objects_alloc_page_count) AS alloc_pages, 
  61.            SUM(internal_objects_dealloc_page_count) AS dealloc_pages 
  62.     FROM sys.dm_db_task_space_usage WITH (NOLOCK) 
  63.     WHERE session_id <> @@SPID 
  64.     GROUP BY session_id, request_id 
  65. ) 
  66. SELECT TSU.session_id, 
  67.        TSU.alloc_pages * 1.0 / 128 AS [internal object MB space], 
  68.        TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space], 
  69.        EST.text, 
  70.        -- Extract statement from sql text  
  71.        ISNULL( 
  72.            NULLIF( 
  73.                SUBSTRING( 
  74.                    EST.text,  
  75.                    ERQ.statement_start_offset / 2,  
  76.                    CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset THEN 0 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END 
  77.                ), '' 
  78.            ), EST.text 
  79.        ) AS [statement text], 
  80.        EQP.query_plan 
  81. FROM task_space_usage AS TSU 
  82. INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK) 
  83.     ON  TSU.session_id = ERQ.session_id 
  84.     AND TSU.request_id = ERQ.request_id 
  85. OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST 
  86. OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP 
  87. WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL 
  88. ORDER BY 3 DESC, 5 DESC 
  89.  
  90.  
  91. WAITFOR DELAY '0:0:1'                                                     
  92. END        
USE tempdb                                                               

-- 每隔1秒钟运行一次，直到用户手工终止脚本运行

WHILE 1 = 1 
BEGIN                                                                   

-- Query 1
-- 从文件级看tempdb使用情况
DBCC showfilestats                                                 

-- Query 2
-- 返回所有做过空间申请的session信息
SELECT  'tempdb' AS database_name , GETDATE() AS Time , SUM(user_object_reserved_page_count)/128. AS user_objects_mb ,
        SUM(internal_object_reserved_page_count)/128. AS internal_objects_mb , SUM(version_store_reserved_page_count)/128. AS version_store_kb ,
        SUM(unallocated_extent_page_count)/128. AS freespace_mb
FROM    sys.dm_db_file_space_usage
WHERE   database_id = 2                                                    

-- Query 3
-- 这个管理视图能够反映当时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.login_time , t3.login_name , t3.host_name , t3.nt_domain , t3.nt_user_name , t3.program_name ,
        t3.status , t3.client_interface_name , t3.cpu_time , t3.memory_usage , t3.total_scheduled_time , t3.total_elapsed_time ,
        t3.last_request_start_time , t3.last_request_end_time , t3.reads , t3.writes , t3.logical_reads , t3.is_user_process , t3.row_count ,
        t3.prev_error , t3.original_security_id , t3.original_login_name , t3.last_successful_logon , t3.last_unsuccessful_logon ,
        t3.unsuccessful_logons , t3.group_id
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
            )

-- Query 4
-- 返回正在运行并且做过空间申请的session正在运行的语句
SELECT  t1.session_id , st.text , GETDATE()
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
            ) 
-- Query 5
-- 返回正在运行的活动的空间使用情况以及语句内容和执行计划 
;WITH task_space_usage AS (
    -- SUM alloc/delloc pages
    SELECT session_id,
           request_id,
           SUM(internal_objects_alloc_page_count) AS alloc_pages,
           SUM(internal_objects_dealloc_page_count) AS dealloc_pages
    FROM sys.dm_db_task_space_usage WITH (NOLOCK)
    WHERE session_id <> @@SPID
    GROUP BY session_id, request_id
)
SELECT TSU.session_id,
       TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
       TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
       EST.text,
       -- Extract statement from sql text
       ISNULL(
           NULLIF(
               SUBSTRING(
                   EST.text, 
                   ERQ.statement_start_offset / 2, 
                   CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset THEN 0 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
               ), ''
           ), EST.text
       ) AS [statement text],
       EQP.query_plan
FROM task_space_usage AS TSU
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)
    ON  TSU.session_id = ERQ.session_id
    AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 3 DESC, 5 DESC


WAITFOR DELAY '0:0:1'                                                    
END

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

 

连接B生成的是一个文本文件。文本里面可以看出tempdb的使用空间有过增长和下降。结果我在这里不做过多的分析，有兴趣的可以尝试一下

如果没有太好的分析，可以创建几个表，然后把查询结果插入到表里面，然后对表进行分析。从而来确定导致你tempdb 暴增的原因。下面附上建表的脚本，供大家参考使用

 

[sql] view plaincopyprint?
  1. -- 1  
  2. -- 创建表 tb_showfilestats 记录 DBCC showfilestats 返回的信息  
  3. IF OBJECT_ID('tb_showfilestats' , 'U') IS NOT NULL  
  4.    DROP TABLE tb_showfilestats 
  5. GO    
  6. CREATE TABLE tb_showfilestats 
  7.        ( 
  8.         id INT IDENTITY(1,1) PRIMARY KEY, 
  9.         Fileid INT , 
  10.         FileGroup INT , 
  11.         TotalExtents INT , 
  12.         UsedExtents INT , 
  13.         Name VARCHAR(100) , 
  14.         FILENAME VARCHAR(255) 
  15.        ) 
  16. GO   
  17.   
  18. -- 2  
  19. -- 创建表 tb_db_file_space_usage 记录 所有做过空间申请的 session 信息  
  20.      
  21. IF OBJECT_ID('tb_db_file_space_usage' , 'U') IS NOT NULL  
  22.    DROP TABLE tb_db_file_space_usage 
  23. GO 
  24. CREATE TABLE tb_db_file_space_usage 
  25.        ( 
  26.         id INT IDENTITY(1,1) PRIMARY KEY, 
  27.         database_name VARCHAR(50) , 
  28.         InDate DATETIME , 
  29.         user_objects_mb DECIMAL(18,4) , 
  30.         internal_objects_mb DECIMAL(18,4) , 
  31.         version_store_kb DECIMAL(18,4) , 
  32.         freespace_mbFILENAME DECIMAL(18,4) 
  33.        ) 
  34. GO 
  35.  
  36. -- 3  
  37. -- 创建表 tb_db_session_space_usage 记录正在运行的 tempdb 空间的总体分配  
  38.  
  39. IF OBJECT_ID('tb_db_session_space_usage' , 'U') IS NOT NULL  
  40.    DROP TABLE tb_db_session_space_usage 
  41. GO 
  42. CREATE TABLE tb_db_session_space_usage 
  43.        ( 
  44.         id INT IDENTITY(1,1) PRIMARY KEY, 
  45.         session_id INT , 
  46.         internal_objects_alloc_page_count INT , 
  47.         user_objects_alloc_page_count INT , 
  48.         internal_objects_dealloc_page_count INT , 
  49.         user_objects_dealloc_page_count INT , 
  50.         login_time DATETIME , 
  51.         login_name VARCHAR(100) , 
  52.         host_name NVARCHAR(128) , 
  53.         nt_domain NVARCHAR(128) , 
  54.         nt_user_name NVARCHAR(128) , 
  55.         program_name NVARCHAR(128) , 
  56.         status VARCHAR(50) , 
  57.         client_interface_name NVARCHAR(32) , 
  58.         cpu_time INT , 
  59.         memory_usage INT , 
  60.         total_scheduled_time INT , 
  61.         total_elapsed_time INT , 
  62.         last_request_start_time DATETIME , 
  63.         last_request_end_time DATETIME , 
  64.         reads INT , 
  65.         writes INT , 
  66.         logical_reads INT , 
  67.         is_user_process BIT , 
  68.         row_count BIGINT , 
  69.         prev_error INT , 
  70.         original_security_id VARBINARY(85) , 
  71.         original_login_name NVARCHAR(128) , 
  72.         last_successful_logon DATETIME , 
  73.         last_unsuccessful_logon DATETIME , 
  74.         unsuccessful_logons BIGINT , 
  75.         group_id INT 
  76.        ) 
  77. GO 
  78.  
  79. -- 4  
  80. --  记录正在运行并且做过空间申请的session正在运行的语句.  
  81.  
  82.  
  83. IF OBJECT_ID('tb_db_sql_text' , 'U') IS NOT NULL  
  84.    DROP TABLE tb_db_sql_text 
  85. GO 
  86. CREATE TABLE tb_db_sql_text 
  87.        ( 
  88.         id INT IDENTITY(1,1) PRIMARY KEY, 
  89.         session_id VARCHAR(50) , 
  90.         text VARCHAR(MAX), 
  91.         InDate DATETIME 
  92.        ) 
  93. GO 
  94.  
  95. -- 5  
  96. --  记录正在运行的活动的空间使用情况以及语句内容和执行计划.  
  97.  
  98. IF OBJECT_ID('tb_task_space_usage' , 'U') IS NOT NULL  
  99.    DROP TABLE tb_task_space_usage 
  100. GO 
  101. CREATE TABLE tb_task_space_usage 
  102.        ( 
  103.         id INT IDENTITY(1 , 1)PRIMARY KEY , 
  104.         session_id INT , 
  105.         internal_object_space_MB DECIMAL(18 , 4) , 
  106.         internal_object_dealloc_space_MB DECIMAL(18 , 4) , 
  107.         text VARCHAR(MAX) , 
  108.         statement_text VARCHAR(MAX) , 
  109.         query_plan XML 
  110.        ) 

http://blog.csdn.net/beirut/article/details/8266203

http://www.cnblogs.com/changbluesky/archive/2010/04/15/1711733.html

tempdb 容量规划：http://msdn.microsoft.com/zh-cn/library/ms345368(SQL.100).aspx

SQL Server中TempDB管理（version store的逻辑结构）：http://blogs.msdn.com/b/apgcdsd/archive/2012/03/30/sql-server-tempdb-version-store.aspx

Working with tempdb in SQL Server 2005：http://technet.microsoft.com/zh-cn/library/cc966545

Tempdb怎么会成为性能瓶颈？：http://blogs.msdn.com/b/apgcdsd/archive/2011/01/25/tempdb.aspx

 

 

 

posted @ 2014-09-16 17:57  Crazy.Liu  阅读(478)  评论(0)  编辑  收藏  举报