[翻譯]TempDB剩餘空間監視與錯誤修正

Q：是否有辦法監視TempDB剩餘空間？是否可以控制不同的應用程式被分配多少TempDB空間，或者建立多個TempDB？

A：
不幸的是SQL Server不提供以上的功能。但SQL Server可以使用DMV來檢查當前查詢TempDB空間分配。如果您發現TempDB剩餘的空間相當少了，您可以找出消耗最大的查詢，決定終止一個或幾個查詢來回收TempDB的空間。

您可以使用如下DMV查詢：

 

Code
-- This DMV query shows currently executing tasks and 
-- tempdb space usage
-- Once you have isolated the task(s) that are generating lots 
-- of internal object allocations, 
-- you can even find out which TSQL statement and its query plan 
-- for detailed analysis

select top 10
t1.session_id, 
t1.request_id, 
t1.task_alloc,
     t1.task_dealloc,  
    (SELECT SUBSTRING(text, t2.statement_start_offset/2 + 1,
          (CASE WHEN statement_end_offset = -1 
              THEN LEN(CONVERT(nvarchar(max),text)) * 2 
                   ELSE statement_end_offset 
              END - t2.statement_start_offset)/2)
     FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
 (SELECT query_plan from sys.dm_exec_query_plan(t2.plan_handle)) as query_plan

from      (Select session_id, request_id,
sum(internal_objects_alloc_page_count +   user_objects_alloc_page_count) as task_alloc,
sum (internal_objects_dealloc_page_count + user_objects_dealloc_page_count) as task_dealloc
       from sys.dm_db_task_space_usage 
       group by session_id, request_id) as t1, 
       sys.dm_exec_requests as t2
where t1.session_id = t2.session_id and 
(t1.request_id = t2.request_id) and 
      t1.session_id > 50
order by t1.task_alloc DESC

該查詢可以顯示TempDB中消耗最高的查詢。您也可以進一步查看查詢計劃來看查詢在TempDB中申請的空間所派的用處。