一尘不染

如何确定哪个查询正在填满 tempdb 事务日志?

sql

我想知道如何识别实际填充 TEMPDB 数据库事务日志的确切查询或存储过程。


阅读 140

收藏
2022-10-26

共1个答案

一尘不染

;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 的方式和时间。

2022-10-26