mssql sqlserver tempdb暴涨了,如何查询出暴涨所对应的sql脚本呢?


摘要:
sqlserver中tempdb暴涨了,常见的原因是查询未做优化,下文将讲述获取未优化的sql脚本的方法分享,如下所示:
实验环境:sql server 2008 R2


具体的查询步骤如下所示:

   
use tempdb  
go  

----检索出占用空间最大的sessioId 
SELECT top 50 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_name,t3.status,t3.total_elapsed_time  
from sys.dm_db_session_space_usage  t1   
inner join sys.dm_exec_sessions as t3   
on t1.session_id = t3.session_id   
where (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)  
order by t1.internal_objects_alloc_page_count desc  


---2.将第一个步骤中查询出的SessionId放入 替换下面的sessionId值,
---得到未优化的脚本信息,然后针对性优化处理

select s.text,p.*
from master.dbo.sysprocesses p   
cross apply sys.dm_exec_sql_text(p.sql_handle) s  
where spid = sessionId  ---第一步骤中的sessionId替换此处的sessionId值