如何检查特定架构上是否有长时间运行的会话？ [英] How to check whether any long running session is going on a particular schema?

问题描述

我想检查特定架构中是否有任何长时间运行的会话。

您能否帮我找到如何检查长时间运行的会话以及我将如何杀死它们。

I want to check whether any long running session is going on in a particular schema.
Could you please help to find how I will check the long running session and how I will kill them.

推荐答案

您可以使用以下查询来获取会话特定的详细信息并添加您想要的任何类型的过滤器。

You can use below query to get session specific details and add any kind of filter you want.

SELECT es.session_id
    ,es.program_name
    ,es.login_name
    ,es.nt_user_name
    ,es.login_time
    ,es.host_name
    ,es.cpu_time
    ,es.total_scheduled_time
    ,es.total_elapsed_time
    ,es.memory_usage
    ,es.logical_reads
    ,es.reads
    ,es.writes
    ,st.text
FROM sys.dm_exec_sessions es
    LEFT JOIN sys.dm_exec_connections ec 
        ON es.session_id = ec.session_id
    LEFT JOIN sys.dm_exec_requests er
        ON es.session_id = er.session_id
    OUTER APPLY sys.dm_exec_sql_text (er.sql_handle) st
WHERE es.session_id > 50    -- < 50 system sessions
ORDER BY es.cpu_time DESC

除此之外，您还可以使用以下查询来识别会话中长时间运行的SQL这可能是性能特定的问题。

Further to that you can also use below query to identify the long running SQL in a session that could performance specific problems.

SELECT 
    substring(text,qs.statement_start_offset/2
        ,(CASE    
            WHEN qs.statement_end_offset = -1 THEN len(convert(nvarchar(max), text)) * 2 
            ELSE qs.statement_end_offset 
        END - qs.statement_start_offset)/2) 
    ,qs.plan_generation_num as recompiles
    ,qs.execution_count as execution_count
    ,qs.total_elapsed_time - qs.total_worker_time as total_wait_time
    ,qs.total_worker_time as cpu_time
    ,qs.total_logical_reads as reads
    ,qs.total_logical_writes as writes
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
    LEFT JOIN sys.dm_exec_requests r 
        ON qs.sql_handle = r.sql_handle
ORDER BY 3 DESC

这篇关于如何检查特定架构上是否有长时间运行的会话？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！