tempdb相关总结

/*
-- 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' ;