笔记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
