tempdb瓶颈

----------------
tempdb瓶颈
----------------

1.与tempdb有关的问题
 空间不足
 查询速度缓慢
 大量的DDL、DML操作引起系统表的瓶颈问题
 
2.tempdb空间分配
 用户对象  用户创建的表格、存储过程
 内部对象  表连接、hash连接
 版本存储区
 可用空间

----------------
诊断tempdb磁盘空间问题
----------------
1.sql server错误日志
 1101、1105  任何会话必须分配tempdb中的空间
 3959   版本存储区已满，此错误在日志中通常出现在错误1101、1105之后
 3967   由于tempdb已满，版本存储区强制收缩
 3958、3966  事务在tempdb中找不到所需要的版本记录

----------------
监视tempdb磁盘空间
----------------
1.获取tempdb中的用户对象、内部对象、版本存储区使用的空间量以及可用空间量
select sum(user_object_reserved_page_count)*8   as user_objects_kb,
  sum(internal_object_reserved_page_count)*8  as internal_objects_kb,
  sum(version_store_reserved_page_count)*8  as version_store_kb,
  sum(unallocated_extent_page_count)*8   as freespace_kb
from sys.dm_db_file_space_usage
where database_id = 2

database_id = 2(可以不写)

查询结果：
user_objects_kb     internal_objects_kb   version_store_kb    freespace_kb
 320                                 1472                           0                           5120

freespace_kb(可用空间)<user_objects_kb(用户对象) ----->注意tempdb可能空间不足

 

如果版本存储区(version_store_kb)使用了tempdb中的大量空间，则需要确定运行时间最长的事务
select transaction_id from sys.dm_tran_active_snapshot_database_transactions
order by elapsed_time_seconds desc

----------------
who block me
----------------
select  blocked_query.session_id as blocked_session_id,
  blocking_query.session_id as blocking_session_id,
  sql_text.text as blocking_test,
  waits.wait_type as blocking_resource
from sys.dm_exec_requests blocked_query
join sys.dm_exec_requests blocking_query
on  blocked_query.blocking_session_id = blocking_query.session_id
cross apply
(
 select *
 from sys.dm_exec_sql_text(blocking_query.sql_handle)
)sql_text
join sys.dm_os_waiting_tasks waits
on  waits.session_id = blocking_query.session_id