如何找到導致 tempdb 增長的 SQL 語句？

伺服器的 tempdb (SQL Server 2008) 每月數次增加到 500GB+。是否可以找出導致此問題的 SQL 語句？問題通常不是由複雜連接引起的，create table #temp...; insert into #temp...而是由select ... into #temp...複雜連接引起的。

一些 tempdb 文件的初始大小也每次都會自動設置為更大的值。如何預防？

有時記憶體的計劃會阻止調整文件大小/縮小文件。如何找到哪個持有 tempdb？

您可以使用三個 DMV 來跟踪 tempdb 的使用情況：

• sys.dm_db_task_space_usage
• sys.dm_db_session_space_usage
• sys.dm_db_file_space_usage

前兩個將允許您在查詢和會話級別跟踪分配。第三個跟踪跨版本儲存、使用者和內部對象的分配。

以下範例查詢將為您提供每個會話的分配：

SELECT
 sys.dm_exec_sessions.session_id AS [SESSION ID]
 ,DB_NAME(database_id) AS [DATABASE Name]
 ,HOST_NAME AS [System Name]
 ,program_name AS [Program Name]
 ,login_name AS [USER Name]
 ,status
 ,cpu_time AS [CPU TIME (in milisec)]
 ,total_scheduled_time AS [Total Scheduled TIME (in milisec)]
 ,total_elapsed_time AS    [Elapsed TIME (in milisec)]
 ,(memory_usage * 8)      AS [Memory USAGE (in KB)]
 ,(user_objects_alloc_page_count * 8) AS [SPACE Allocated FOR USER Objects (in KB)]
 ,(user_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR USER Objects (in KB)]
 ,(internal_objects_alloc_page_count * 8) AS [SPACE Allocated FOR Internal Objects (in KB)]
 ,(internal_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR Internal Objects (in KB)]
 ,CASE is_user_process
            WHEN 1      THEN 'user session'
            WHEN 0      THEN 'system session'
 END         AS [SESSION Type], row_count AS [ROW COUNT]
FROM 
 sys.dm_db_session_space_usage
INNER join
 sys.dm_exec_sessions
ON  sys.dm_db_session_space_usage.session_id = sys.dm_exec_sessions.session_id

如果您想跟踪一段時間內的使用情況，請考慮使用sp_whoisactive收集數據，如Kendra Little所示。

引用自：https://dba.stackexchange.com/questions/13911