#temp、##global)、表變數(@table)。ALTER DATABASE tempdb MODIFY FILE (NAME=tempdev, SIZE=300GB)。TRUNCATE TABLE #temp; DROP TABLE #temp; 釋放系統表鎖定。-- 空間使用
SELECT
SUM(unallocated_extent_page_count)*1.0/128 AS [Free Space (MB)],
SUM(version_store_reserved_page_count)*1.0/128 AS [Version Store (MB)],
SUM(internal_object_reserved_page_count)*1.0/128 AS [Internal Objects (MB)],
SUM(user_object_reserved_page_count)*1.0/128 AS [User Objects (MB)],
SUM(mixed_extent_page_count)*1.0/128 AS [Mixed Extents (MB)]
FROM sys.dm_db_file_space_usage WHERE database_id = 2;
-- 爭用熱點 (PFS/GAM/SGAM)
SELECT * FROM sys.dm_os_waiting_tasks
WHERE resource_description LIKE '2:%' AND wait_type LIKE 'PAGE%LATCH%';
@t 不用 tempdb → 錯,表變數也在 tempdb 建立,只是統計資訊較少。#temp 只對當前連線可見 → 正確,但巢狀程序可見父層 #temp。遵循規範並持續監控,tempdb 將不再是效能瓶頸。