我想知道如何辨識實際填充 TEMPDB 數據庫事務日誌的確切查詢或儲存過程。

來自http://www.sqlservercentral.com/scripts/tempdb/72007/

;WITH task_space_usage AS (
   -- SUM alloc/delloc pages
   SELECT session_id,
          request_id,
          SUM(internal_objects_alloc_page_count) AS alloc_pages,
          SUM(internal_objects_dealloc_page_count) AS dealloc_pages
   FROM sys.dm_db_task_space_usage WITH (NOLOCK)
   WHERE session_id <> @@SPID
   GROUP BY session_id, request_id
)
SELECT TSU.session_id,
      TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
      TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
      EST.text,
      -- Extract statement from sql text
      ISNULL(
          NULLIF(
              SUBSTRING(
                EST.text, 
                ERQ.statement_start_offset / 2, 
                CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset 
                 THEN 0 
                ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
              ), ''
          ), EST.text
      ) AS [statement text],
      EQP.query_plan
FROM task_space_usage AS TSU
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)
   ON  TSU.session_id = ERQ.session_id
   AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 3 DESC;

編輯

正如 Martin 在評論中指出的那樣，這不會找到在 tempdb 中佔用空間的活動事務，它只會找到目前正在使用那裡空間的活動查詢（並且可能是目前日誌使用的罪魁禍首）。所以可能有一個打開的事務，但導致問題的實際查詢不再執行。

您可以將inner joinon更改sys.dm_exec_requests為 a left outer join，然後您將返回目前未主動執行查詢的會話的行。

馬丁發布的查詢…

SELECT database_transaction_log_bytes_reserved,session_id 
 FROM sys.dm_tran_database_transactions AS tdt 
 INNER JOIN sys.dm_tran_session_transactions AS tst 
 ON tdt.transaction_id = tst.transaction_id 
 WHERE database_id = 2;

…將session_ids 標識為佔用日誌空間的活動事務，但您不一定能夠確定導致問題的實際查詢，因為如果它現在沒有執行，則不會在上述查詢中擷取主動請求。您可以使用被動檢查最近的查詢，DBCC INPUTBUFFER但它可能無法告訴您您想听什麼。您可以以類似的方式外部加入來擷取那些正在執行的，例如：

SELECT tdt.database_transaction_log_bytes_reserved,tst.session_id,
      t.[text], [statement] = COALESCE(NULLIF(
        SUBSTRING(
          t.[text],
          r.statement_start_offset / 2,
          CASE WHEN r.statement_end_offset < r.statement_start_offset
            THEN 0
            ELSE( r.statement_end_offset - r.statement_start_offset ) / 2 END
        ), ''
      ), t.[text])
    FROM sys.dm_tran_database_transactions AS tdt
    INNER JOIN sys.dm_tran_session_transactions AS tst
    ON tdt.transaction_id = tst.transaction_id
        LEFT OUTER JOIN sys.dm_exec_requests AS r
        ON tst.session_id = r.session_id
        OUTER APPLY sys.dm_exec_sql_text(r.plan_handle) AS t
    WHERE tdt.database_id = 2;

您還可以使用 DMVsys.dm_db_session_space_usage查看會話的總體空間使用率（但同樣，您可能無法獲得查詢的有效結果；如果查詢不活躍，您獲得的可能不是真正的罪魁禍首）。

;WITH s AS
(
   SELECT 
       s.session_id,
       [pages] = SUM(s.user_objects_alloc_page_count 
         + s.internal_objects_alloc_page_count) 
   FROM sys.dm_db_session_space_usage AS s
   GROUP BY s.session_id
   HAVING SUM(s.user_objects_alloc_page_count 
     + s.internal_objects_alloc_page_count) > 0
)
SELECT s.session_id, s.[pages], t.[text], 
 [statement] = COALESCE(NULLIF(
   SUBSTRING(
       t.[text], 
       r.statement_start_offset / 2, 
       CASE WHEN r.statement_end_offset < r.statement_start_offset 
       THEN 0 
       ELSE( r.statement_end_offset - r.statement_start_offset ) / 2 END
     ), ''
   ), t.[text])
FROM s
LEFT OUTER JOIN 
sys.dm_exec_requests AS r
ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.plan_handle) AS t
ORDER BY s.[pages] DESC;

使用所有這些查詢供您使用，您應該能夠縮小使用 tempdb 的人員以及使用方式的範圍，尤其是當您在行動中發現它們時。

最小化 tempdb 使用率的一些技巧

1. 使用更少的#temp 表和@table 變數
2. 盡量減少並發索引維護，SORT_IN_TEMPDB如果不需要，請避免使用該選項
3. 避免不必要的游標；如果您認為這可能是瓶頸，請避免使用靜態游標，因為靜態游標使用 tempdb 中的工作表
4. 盡量避免線軸（例如，在查詢中多次引用的大型 CTE）
5. 不要使用火星
6. 徹底測試快照/RCSI 隔離級別的使用——不要只為所有數據庫打開它，因為你被告知它比 NOLOCK 更好（它是，但它不是免費的）
7. 在某些情況下，這可能聽起來不直覺，但使用更多的臨時表。例如，將一個龐大的查詢分成幾部分可能效率稍低，但如果它可以避免對 tempdb 造成巨大的記憶體溢出，因為單個更大的查詢需要太大的記憶體授予……
8. 避免為批量操作啟用觸發器
9. 避免過度使用 LOB 類型（最大類型、XML 等）作為局部變數
10. 保持交易簡短而甜蜜
11. 不要將 tempdb 設置為每個人的預設數據庫 -

您可能還認為您的 tempdb 日誌使用可能是由您很少或無法控制的內部程序引起的 - 例如數據庫郵件、事件通知、查詢通知和服務代理都以某種方式使用 tempdb。您可以停止使用這些功能，但如果您正在使用它們，則無法指定它們使用 tempdb 的方式和時間。

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