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

#1 楼

摘自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;

EDIT
正如Martin在评论中指出的那样，这不会发现正在占用空间的活动事务。 tempdb，它将仅查找当前正在那里使用空间的活动查询（并且可能是当前日志使用情况的罪魁祸首）。因此可能存在未结事务，但是导致问题的实际查询不再运行。
您可以将inner join上的sys.dm_exec_requests更改为left outer join，然后将返回当前未在积极运行查询的会话的行。
查询Martin发布了...造成问题的原因在于，如果现在不运行它，则不会在上面的活动请求查询中捕获它。您也许可以使用session_id反应性地检查最近的查询，但可能无法告诉您您想听的内容。您可以通过类似的方式进行外部联接以捕获那些正在运行的对象，例如：
查询；如果查询未处于活动状态，则返回的结果可能不是真正的罪魁祸首。 tempdb以及如何进行修改，尤其是在实际操作中。
一些有关最小化tempdb利用率的提示

使用更少的#temp表和@table变量
最小化并发索引维护，如果不需要，请避免使用DBCC INPUTBUFFER选项
避免不必要的游标；如果您认为这可能是瓶颈，请避免使用静态游标，因为静态游标会在tempdb中使用工作表
尽量避免假脱机（例如，在查询中多次引用的大型CTE）
不使用火星
彻底测试快照/ RCSI隔离级别的使用-不要告诉所有数据库都打开它，因为被告知它比NOLOCK更好（它是免费的），但在某些情况下，
听起来似乎不太直观，但是要使用更多的临时表。例如将庞大的查询分解成几个部分可能会效率略低，但是如果它可以避免对tempdb的大量内存溢出，因为单个较大的查询需要太大的内存授予...
避免为批量操作启用触发器
避免过度使用LOB类型（最大类型，XML等）作为局部变量
保持事务简短而甜蜜
不要将tempdb设置为每个人的默认数据库-

您可能还认为，tempdb日志的使用可能是由您几乎无法控制或无法控制的内部进程引起的，例如数据库邮件，事件通知，查询通知和服务代理都以某种方式使用了tempdb。您可以停止使用这些功能，但是，如果要使用它们，则无法确定它们如何以及何时使用tempdb。

#2 楼

https://social.msdn.microsoft.com/Forums/sqlserver/zh-CN/17d9f862-b9ae-42de-ada0-4229f56712dc/tempdb-log-filling-cannot-find-how-or-what?forum=sqldatabaseengine

 SELECT tst.[session_id],
            s.[login_name] AS [Login Name],
            DB_NAME (tdt.database_id) AS [Database],
            tdt.[database_transaction_begin_time] AS [Begin Time],
            tdt.[database_transaction_log_record_count] AS [Log Records],
            tdt.[database_transaction_log_bytes_used] AS [Log Bytes Used],
            tdt.[database_transaction_log_bytes_reserved] AS [Log Bytes Rsvd],
            SUBSTRING(st.text, (r.statement_start_offset/2)+1,
            ((CASE r.statement_end_offset
                    WHEN -1 THEN DATALENGTH(st.text)
                    ELSE r.statement_end_offset
            END - r.statement_start_offset)/2) + 1) AS statement_text,
            st.[text] AS [Last T-SQL Text],
            qp.[query_plan] AS [Last Plan]
    FROM    sys.dm_tran_database_transactions tdt
            JOIN sys.dm_tran_session_transactions tst
                ON tst.[transaction_id] = tdt.[transaction_id]
            JOIN sys.[dm_exec_sessions] s
                ON s.[session_id] = tst.[session_id]
            JOIN sys.dm_exec_connections c
                ON c.[session_id] = tst.[session_id]
            LEFT OUTER JOIN sys.dm_exec_requests r
                ON r.[session_id] = tst.[session_id]
            CROSS APPLY sys.dm_exec_sql_text (c.[most_recent_sql_handle]) AS st
            OUTER APPLY sys.dm_exec_query_plan (r.[plan_handle]) AS qp
    WHERE   DB_NAME (tdt.database_id) = 'tempdb'
    ORDER BY [Log Bytes Used] DESC
GO

#3 楼

谢谢你的这篇文章，可能是同类文章中唯一的一篇。我的测试很简单，创建一个临时表，并确保在我运行本文中的任何查询时显示该表...只有一个或两个真正成功。我已将其更正以加入T-SQL，对其进行了优化以延长运行时间，并使其非常有用。让我知道我是否错过任何事情，但到目前为止，您已经获得了自动/循环脚本。通过下面的标准偏差（STDEV）查询，它提供了一种方法来评估一段时间内哪个查询/ SPID是犯罪者。

此方法每3分钟运行40次，因此需要2个小时。根据需要修改参数。

下面有一个WHERE> 50页过滤器，人们可能想清除一下，以防万一您有很多小桌子。否则，您将无法在下面看到它的细微差别...

享受！

#4 楼

不幸的是，无法通过查看正在运行的进程将tempDB日志直接追溯到sessionID。

将tempDB日志文件缩小到可以再次显着增长的程度。然后创建一个扩展事件以捕获日志增长。一旦它再次增长，您可以扩展扩展事件并查看包事件文件。打开文件，添加时间过滤器，文件类型过滤器（您不希望包含数据文件结果），然后在SSMS中按会话ID对其进行分组。这将帮助您在查找“分组依据”最多的会话ID时找到罪魁祸首。当然，您需要通过另一个进程或工具来收集会话ID中正在运行的内容。也许有人知道如何从query_hash列获取查询，并且会足够友善地发布解决方案。

扩展事件的结果：



用于创建扩展事件的脚本：

CREATE EVENT SESSION [tempdb_file_size_changed] ON SERVER ADD EVENT 
sqlserver.database_file_size_change(SET collect_database_name=(1)ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.is_system,sqlserver.query_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username) WHERE ([database_id]=(2))) ADD TARGETpackage0.event_file(SET filename=N'C:\ExtendedEvents\TempDBGrowth.xel',max_file_size=(100),max_rollover_files=(25)) WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=1 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)