在SQL Server 2014中，能否通过DMV查找溢出到tempdb的查询？

当然可以！在SQL Server 2014中，我们完全可以借助动态管理视图（DMV）来定位那些执行时会把数据溢出到tempdb的查询。下面我会给你具体的方法和实用的查询语句：

这个DMV能帮你直观看到每个会话在tempdb里的空间分配情况，包括用户自定义对象、系统内部对象和版本存储的空间消耗。先从排查高占用会话开始：

SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    su.user_objects_alloc_page_count,
    su.user_objects_dealloc_page_count,
    su.internal_objects_alloc_page_count,
    su.internal_objects_dealloc_page_count,
    (su.user_objects_alloc_page_count + su.internal_objects_alloc_page_count) AS total_alloc_pages
FROM 
    sys.dm_db_session_space_usage su
JOIN 
    sys.dm_exec_sessions s ON su.session_id = s.session_id
WHERE 
    (su.user_objects_alloc_page_count > 0 OR su.internal_objects_alloc_page_count > 0)
ORDER BY 
    total_alloc_pages DESC;

这里要重点关注internal_objects_alloc_page_count——它通常对应查询执行时产生的临时工作区（比如排序、哈希连接内存不足时，就会把数据溢出到tempdb）；而user_objects_alloc_page_count则是用户创建的临时表、表变量占用的空间。

如果想实时定位正在执行的、导致tempdb溢出的查询，可以把上面的视图和sys.dm_exec_requests、sys.dm_exec_sql_text关联起来，直接拿到具体的查询语句：

SELECT 
    r.session_id,
    s.login_name,
    r.status,
    r.command,
    st.text AS query_text,
    su.user_objects_alloc_page_count,
    su.internal_objects_alloc_page_count,
    (su.user_objects_alloc_page_count + su.internal_objects_alloc_page_count) AS total_tempdb_pages
FROM 
    sys.dm_db_session_space_usage su
JOIN 
    sys.dm_exec_sessions s ON su.session_id = s.session_id
JOIN 
    sys.dm_exec_requests r ON su.session_id = r.session_id
CROSS APPLY 
    sys.dm_exec_sql_text(r.sql_handle) st
WHERE 
    (su.user_objects_alloc_page_count > 0 OR su.internal_objects_alloc_page_count > 0)
ORDER BY 
    total_tempdb_pages DESC;

这个查询能直接返回当前正在消耗tempdb空间的语句，方便你快速定位问题源头。

如果要找过去执行过的、曾导致tempdb溢出的查询，sys.dm_exec_query_stats会是好帮手——它缓存了查询的执行统计信息，包括tempdb相关的累计指标：

SELECT 
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset 
            WHEN -1 THEN DATALENGTH(st.text) 
            ELSE qs.statement_end_offset 
         END - qs.statement_start_offset)/2)+1) AS individual_query,
    qs.total_worker_time,
    qs.total_elapsed_time,
    qs.total_logical_reads,
    qs.total_physical_reads,
    qs.total_logical_writes,
    qs.total_tempdb_allocations, -- 该查询累计分配的tempdb页数
    qs.total_tempdb_deallocations,
    qs.execution_count
FROM 
    sys.dm_exec_query_stats qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.sql_handle) st
WHERE 
    qs.total_tempdb_allocations > 0
ORDER BY 
    qs.total_tempdb_allocations DESC;

注意total_tempdb_allocations字段，它统计了查询执行过程中累计在tempdb分配的页数，数值越大说明该查询越可能是tempdb溢出的“常客”。

• 查询这些DMV需要你拥有VIEW SERVER STATE权限；
• 内部对象的tempdb分配大多和查询执行计划有关——比如排序操作所需空间超过内存授予、哈希连接内存不足时，都会把中间结果写到tempdb。定位到问题查询后，建议查看它的执行计划来优化内存使用，从根源减少溢出。

内容的提问来源于stack exchange，提问作者jshikha