笔记211 监视tempdb的脚本

笔记211 监视tempdb的脚本

 1 --监视tempdb的脚本
 2 USE tempdb
 3 --每隔一秒运行一次,直到用户手工终止脚本运行
 4 WHILE 1 = 1
 5     BEGIN
 6         SELECT  GETDATE()
 7 --从文件级别看tempdb使用情况
 8         DBCC showfilestats
 9 
10 --query1
11 --返回所有做过空间申请的会话信息
12         SELECT  'tempdb' AS db ,
13                 GETDATE() AS TIME ,
14                 SUM(USER_object_reserved_page_count) * 8 AS user_object_kb ,
15                 SUM(internal_object_reserved_page_count) * 8 AS internal_object_kb ,
16                 SUM(version_store_reserved_page_count) * 8 AS version_store_kb ,
17                 SUM(unallocated_extent_page_count) * 8 AS freespace_kb
18         FROM    sys.dm_db_file_space_usage
19         WHERE   database_id = 2
20 
21 --query2
22 --这个管理视图能够反映当时tempdb空间的总体分配
23         SELECT  t1.session_id ,
24                 t1.internal_objects_alloc_page_count ,
25                 t1.user_objects_alloc_page_count ,
26                 t1.internal_objects_dealloc_page_count ,
27                 t1.user_objects_dealloc_page_count ,
28                 t3.*
29         FROM    sys.dm_db_session_space_usage AS t1 ,
30 --反映每个会话累计空间的申请
31                 sys.dm_exec_sessions AS t3
32 --每个会话的信息
33         WHERE   t1.session_id = t3.session_id
34                 AND ( 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 
41 --query3
42 --返回正在运行并且做过空间申请的会话正在运行的语句
43 --cross apply的应用http://hi.baidu.com/ken_site/item/fc99e9d016bba8e2b3f77791
44         SELECT  t1.session_id ,
45                 st.text
46         FROM    sys.dm_db_session_space_usage AS t1 ,
47                 sys.dm_exec_requests AS t4
48                 CROSS APPLY sys.dm_exec_sql_text(t4.sql_handle) AS st
49         WHERE   t1.session_id = t4.session_id
50                 AND t1.session_id > 50
51                 AND ( t1.internal_objects_alloc_page_count > 0
52                       OR t1.user_objects_alloc_page_count > 0
53                       OR t1.internal_objects_dealloc_page_count > 0
54                       OR t1.user_objects_dealloc_page_count > 0
55                     )
56         WAITFOR DELAY '0:0:1'
57     END

 

posted @ 2013-08-04 13:34 桦仔 阅读(...) 评论(...)  编辑 收藏