有时会收到tempdb日志文件使用率超过90%的告警,最准确的处理方式是直接登上去看有哪些长时间运行且会大量消耗tempdb的语句在执行,但有时刚好不方便操作,在登录查看时sql已经执行完了,事后难判断。
查询文章发现还可以通过tempdb分配情况去分析,不过要注意这种方法找到的只是问题会话,未必能找到导致问题的sql,感觉配合扩展事件的慢sql监控应该能解决这个问题。
查看tempdb记录的分配情况:如果查询上面的DMV距事件发生时间太久,可能就查不到了,要尽快查看
use tempdb
go
SELECT top 10 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.login_time,t3.last_request_start_time,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
有四个关键信息:
- session_id:可以查询该session的相关信息
- internal_objects_alloc_page_count:分配给session内部对象的数据页
- internal_objects_dealloc_page_count:已经释放的数据页
- login_name:该session的登录名
1. 从internal_objects_alloc_page_count可以看出,给session分配了133200页,计算一下大约是1G,假如和告警时tempdb增长的大小一致,执行时间也接近,基本就可以断定是这个session引起的。
select 133200*8/1024/1024 as [size_GB]
2. 从internal_objects_dealloc_page_count可以看到占用tempdb的数据已经释放了。
3. 从login_name可知道操作人(这就是实名用户的好处之一,可以很快追踪使用者,是内部人员操作)。
4. 从session_id可以查询最后一条执行的语句。
select p.*,s.text
from master.dbo.sysprocesses p
cross apply sys.dm_exec_sql_text(p.sql_handle) s
where spid = 647
最后查出来这是一个select into的语句
另外补充两个tempdb相关sql
tempdb空间使用跟踪(不要用sp_spaceused)
--Tempdb库文件的使用情况
SELECT SUM(user_object_reserved_page_count) * 8 as usr_obj_kb,
SUM(internal_object_reserved_page_count) * 8 as internal_obj_kb,
SUM(version_store_reserved_page_count) * 8 as version_store_kb,
SUM(unallocated_extent_page_count) * 8 as freespace_kb,
SUM(mixed_extent_page_count) * 8 as mixedextent_kb
FROM sys.dm_db_file_space_usage;
获取每个会话对TEMPDB用户对象和内部对象的使用空间
--排查用户对象和内部对象使用空间异常的问题
select
t1.session_id ,
(t1. internal_objects_alloc_page_count + task_internal_alloc+t1. user_objects_alloc_page_count + task_user_alloc)*8 as [SPACE Allocated FOR ALL Objects (in KB)] ,
(t1. internal_objects_alloc_page_count + task_internal_alloc )*8 AS [SPACE Allocated FOR Internal Objects (in KB)],
(t1. internal_objects_dealloc_page_count + task_internal_dealloc )*8 as [SPACE Deallocated FOR Internal Objects (in KB)],
(t1. user_objects_alloc_page_count + task_user_alloc )*8 as [SPACE Allocated FOR USER Objects (in KB)],
(t1. user_objects_dealloc_page_count + task_user_dealloc )*8 as [SPACE Deallocated FOR USER Objects (in KB)],
DB_NAME( t1.database_id ) AS [ DATABASE NAME ],
t3.HOST_NAME AS [ System NAME ] ,
t3.program_name AS [ Program NAME ] ,
t3.login_name AS [ USER NAME ],
t3.STATUS ,
t3.cpu_time AS [ CPU TIME(IN milisec) ],
t3.total_scheduled_time AS [ Total Scheduled TIME(IN milisec) ],
t3.total_elapsed_time AS [ Elapsed TIME(IN milisec) ],
(t3. memory_usage * 8 ) AS [ Memory USAGE (IN KB) ]
from sys .dm_db_session_space_usage as t1,
( select session_id,
sum(internal_objects_alloc_page_count ) as task_internal_alloc,
sum(internal_objects_dealloc_page_count ) as task_internal_dealloc,
sum(user_objects_alloc_page_count ) as task_user_alloc,
sum(user_objects_dealloc_page_count ) as task_user_dealloc
from sys .dm_db_task_space_usage group by session_id) as t2,
sys.dm_exec_sessions as t3
where t1. session_id = t2 .session_id
and t2. session_id = t3 .session_id
and t1. session_id > 50
order by [SPACE Allocated FOR ALL Objects (in KB)] desc;
--会话使用tempdb情况
SELECT sys.dm_exec_sessions.session_id AS [ SESSION ID ],
DB_NAME(database_id) AS [ DATABASE Name ],
HOST_NAME AS [ System Name ],
program_name AS [ Program Name ],
login_name AS [ USER Name ],
status,
cpu_time AS [ CPU TIME(in milisec) ],
total_scheduled_time AS [ Total Scheduled TIME(in milisec) ],
total_elapsed_time AS [ Elapsed TIME(in milisec) ],
(memory_usage * 8) AS [ Memory USAGE(in KB) ],
(user_objects_alloc_page_count * 8) AS [ SPACE Allocated FOR USER Objects(in KB) ],
(user_objects_dealloc_page_count * 8) AS [ SPACE Deallocated FOR USER Objects(in KB) ],
(internal_objects_alloc_page_count * 8) AS [ SPACE Allocated FOR Internal Objects(in KB) ],
(internal_objects_dealloc_page_count * 8) AS [ SPACE Deallocated FOR Internal Objects(in KB) ],
CASE is_user_process
WHEN 1 THEN
'user session'
WHEN 0 THEN
'system session'
END AS [ SESSION Type ],
row_count AS [ ROW COUNT ]
FROM sys.dm_db_session_space_usage
INNER join sys.dm_exec_sessions
ON sys.dm_db_session_space_usage.session_id =
sys.dm_exec_sessions.session_id;
查占用 temp db的sql
SELECT es.host_name , es.login_name , es.program_name,
st.dbid as QueryExecContextDBID, DB_NAME(st.dbid) as QueryExecContextDBNAME, st.objectid as ModuleObjectId,
SUBSTRING(st.text, er.statement_start_offset/2 + 1,(CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),st.text)) * 2 ELSE er.statement_end_offset
END - er.statement_start_offset)/2) as Query_Text,
tsu.session_id ,tsu.request_id, tsu.exec_context_id,
(tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) as OutStanding_user_objects_page_counts,
(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) as OutStanding_internal_objects_page_counts,
er.start_time, er.command, er.open_transaction_count, er.percent_complete, er.estimated_completion_time, er.cpu_time, er.total_elapsed_time, er.reads,er.writes,
er.logical_reads, er.granted_query_memory
FROM sys.dm_db_task_space_usage tsu inner join sys.dm_exec_requests er
ON ( tsu.session_id = er.session_id and tsu.request_id = er.request_id)
inner join sys.dm_exec_sessions es ON ( tsu.session_id = es.session_id )
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
WHERE (tsu.internal_objects_alloc_page_count+tsu.user_objects_alloc_page_count) > 0
ORDER BY (tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count)+(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count)
DESC
参考