我想知道如何识别实际填充 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 中占用空间的活动事务,它只会找到当前正在使用那里空间的活动查询(并且可能是当前日志使用的罪魁祸首)。所以可能有一个打开的事务,但导致问题的实际查询不再运行。
您可以将inner joinon更改sys.dm_exec_requests为 a left outer join,然后您将返回当前未主动运行查询的会话的行。
inner join
sys.dm_exec_requests
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但它可能无法告诉您您想听什么。您可以以类似的方式外部加入来捕获那些正在运行的,例如:
session_id
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查看会话的总体空间利用率(但同样,您可能无法获得查询的有效结果;如果查询不活跃,您获得的可能不是真正的罪魁祸首)。
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], 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 的人员以及使用方式的范围,尤其是当您在行动中发现它们时。
SORT_IN_TEMPDB
您可能还认为您的 tempdb 日志使用可能是由您很少或无法控制的内部进程引起的 - 例如数据库邮件、事件通知、查询通知和服务代理都以某种方式使用 tempdb。您可以停止使用这些功能,但如果您正在使用它们,则无法指定它们使用 tempdb 的方式和时间。