sql-server – 如何识别哪个查询正在填充tempdb事务日志？

解决方法

;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空间的活动事务,它只会找到当前正在那里使用空间的活动查询(并且可能是当前日志使用的罪魁祸首).因此可能存在打开的事务,但导致问题的实际查询不再运行.

您可以将sys.dm_exec_requests上的内部联接更改为左外部联接,然后返回当前未正在运行查询的会话的行.

马丁发布的查询……

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;

您还可以使用DMV sys.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],[statement] = COALESCE(NULLIF(
    SUBSTRING(
        t.[text],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利用率的技巧

>使用更少的#temp表和@table变量
>最小化并发索引维护,如果不需要,则避免使用SORT_IN_TEMPDB选项
>避免不必要的游标;如果您认为这可能是一个瓶颈,请避免使用静态游标,因为静态游标在tempdb中使用工作表 – 尽管如果tempdb不是瓶颈,我总是推荐这种游标类型
>尽量避免使用线轴(例如多次引用的大型CTE)
查询)
>不要使用MARS
>彻底测试快照/ RCSI隔离级别的使用 – 不要只为所有数据库打开它,因为你被告知它比NOLOCK更好(它是,但它不是免费的)
>在某些情况下,它可能听起来不直观,但使用更多临时表.例如将大量查询分解为部分可能效率稍低,但如果它可以避免巨大的内存溢出到tempdb,因为单个较大的查询需要内存授予太大……
>避免为批量操作启用触发器
>避免过度使用LOB类型(最大类型,XML等)作为局部变量
>保持交易简短而甜蜜
>不要将tempdb设置为每个人的默认数据库 –

您可能还认为您的tempdb日志使用情况可能是由您很少或无法控制的内部进程引起的 – 例如,数据库邮件,事件通知,查询通知和服务代理都以某种方式使用tempdb.您可以停止使用这些功能,但如果您正在使用它们,则无法决定使用tempdb的方式和时间.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!