/* -- 0. 高速压缩tempdb为初始值 USE tempdb DBCC SHRINKFILE(2,TRUNCATEONLY); */ -- 1. tempdb以下未回收的暂时表 ,某些版本号可能查不到数据 use tempdb; select * from sys.objects o where o.type like '%U%'; -- Chapter 7 - Knowing Tempdb -- christian@coeo.com -- Show tempdb usage by type across all files SELECT SUM(user_object_reserved_page_count) AS user_object_pages, SUM(internal_object_reserved_page_count) AS internal_object_pages, SUM(version_store_reserved_page_count) AS version_store_pages, total_in_use_pages = SUM(user_object_reserved_page_count) + SUM(internal_object_reserved_page_count) + SUM(version_store_reserved_page_count), SUM(unallocated_extent_page_count) AS total_free_pages FROM sys.dm_db_file_space_usage ; -- Find the top 5 sessions running tasks that use tempdb SELECT TOP 5 * FROM sys.dm_db_task_space_usage WHERE session_id > 50 ORDER BY user_objects_alloc_page_count + internal_objects_alloc_page_count DESC ; --return currently running T-SQL with Execution Plans SELECT session_id, text, query_plan FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) CROSS APPLY sys.dm_exec_query_plan(plan_handle) ; --view historic tempdb usage by session SELECT * FROM sys.dm_db_session_space_usage WHERE session_id > 50 ORDER BY user_objects_alloc_page_count + internal_objects_alloc_page_count DESC ; -- Temp Tables Creation Rate SELECT * FROM sys.dm_os_performance_counters WHERE counter_name = 'Temp Tables Creation Rate' ;
tempdb 相关总结
转载本文章为转载内容,我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题,欢迎原作者联系我们进行内容更正或删除文章。
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
Kafka相关总结
1)Kafka压测Kafka官方自带压力测试脚本(kafka-consumer-perf-test.sh、kafka-producer-perf-t
Kafka相关总结 kafka 数据 压测 -
Zookeeper相关总结
1)选举机制 半数机制2)常用命令 ls、get、create
Zookeeper相关总结 常用命令 -
Flume相关总结
1)Flume组成,Put事务,Take事务 Taildir Source:断点续传、多目录。Flume1.6以前需要自己自定义Sou
Flume相关总结 hdfs 拦截器 数据 -
redis相关总结
redis!
redis -
查询导致tempdb暴涨的语句 查询的相关
&n
查询导致tempdb暴涨的语句 user hibernate query list