/*-- 0. 快速压缩tempdb为初始值USE tempdbDBCC SHRINKFILE(2,TRUNCATEONLY);*/-- 1. tempdb下面未回收的临时表 ,某些版本可能查不到数据use tempdb;select * from sys.objects o where o.type like %U%;-- Chapter 7 - Knowing Tempdb-- christian@coeo.com
/* -- 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' ;