tempdb 诊断

1. use tempdb  
2. go  
3. SELECT top 10 t1.session_id,                                                      
4. t1.internal_objects_alloc_page_count,  t1.user_objects_alloc_page_count,  
5. t1.internal_objects_dealloc_page_count , t1.user_objects_dealloc_page_count,  
6. t3.login_name,t3.status,t3.total_elapsed_time  
7. from sys.dm_db_session_space_usage  t1   
8. inner join sys.dm_exec_sessions as t3   
9. on t1.session_id = t3.session_id   
10. where (t1.internal_objects_alloc_page_count>0   
11. or t1.user_objects_alloc_page_count >0  
12. or t1.internal_objects_dealloc_page_count>0   
13. or t1.user_objects_dealloc_page_count>0)  
14. order by t1.internal_objects_alloc_page_count desc