List Active Connections - Current User Sessions

Introduction
Query Overview
Conclusion

Introduction

In SQL Server database management, understanding and monitoring current requests is essential for maintaining optimal performance and identifying potential issues. This blog post explores a comprehensive SQL query that retrieves real-time information about active sessions and their associated requests, providing valuable insights into database operations.

Query Overview

The provided SQL query is designed to retrieve detailed information about current SQL Server requests, including session details, query text, execution statistics, and more. Let's break down its key components:

• Session Details: The query starts by retrieving session-related information such as session ID, status, host name, login name, and program name.
• Blocking Session ID:It identifies any blocking session IDs, which can help diagnose performance bottlenecks and concurrency issues within the database.
• Query Text:The query extracts the text of the executing SQL query, handling cases where the query spans multiple lines or contains special characters.
• Query Plan:It retrieves the execution plan associated with each query, aiding in performance analysis and optimization.
• Performance Metrics:The query gathers various performance metrics, including total elapsed time, CPU time, reads, writes, and logical reads.
• Transaction Details:Information about transactions, such as transaction ID and isolation level, is included to track transactional activity.
• Filtering and Ordering:The query filters out system sessions and ensures that only active sessions with valid host names are included. The results are then ordered by total elapsed time in descending order to prioritize resource-intensive sessions.

select
    getdate() time_now,
    de.session_id,
    de.status,
    case
    when nl.blocking_session_id <> 0 and blocked.session_id is null then
        nl.blocking_session_id
    when nl.blocking_session_id <> 0 and de.session_id <> blocked.blocking_session_id then
        blocked.blocking_session_id
    when nl.blocking_session_id = 0 and de.session_id = blocked.session_id then
        blocked.blocking_session_id
    when nl.blocking_session_id <> 0 and de.session_id = blocked.blocking_session_id then
        nl.blocking_session_id
    else
        null
    end as blocking_session_id,
    case
    when deco.statement_start_offset is not null and deco.statement_end_offset is not null then
        substring(
            dec.text,
            (deco.statement_start_offset/2)+1,
            ((
                case deco.statement_end_offset
                when -1 then
                    datalength(dec.text)
                else
                    deco.statement_end_offset
                end - deco.statement_start_offset
            )/2) + 1
        )
    else
        dec.text
    end as query_text,
    deqp.query_plan,
    nl.total_elapsed_time total_elapsed_time_ms,
    nl.total_elapsed_time/1000 total_elapsed_time_sec,
    nl.total_elapsed_time/1000/60 total_elapsed_time_min,
    nl.cpu_time,
    nl.reads,
    nl.writes,
    nl.logical_reads,
    de.host_name,
    de.login_name,
    de.program_name,
    db_name(nl.database_id) db_name,
    nl.request_id,
    nl.transaction_id,
    nl.wait_type,
    nl.wait_time,
    nl.last_wait_type,
    nl.wait_resource,
    nl.open_transaction_count,
    nl.open_resultset_count,
    nl.transaction_isolation_level,
    case de.transaction_isolation_level
        when 0 then 'Unspecified'
        when 1 then 'ReadUncommitted'
        when 2 then 'ReadCommitted'
        when 3 then 'Repeatable'
        when 4 then 'Serializable'
        when 5 then 'Snapshot'
    end as transaction_isolation_level_text,
    nl.row_count,
    dec.objectid,
    nl.sql_handle,
    nl.plan_handle
from
    sys.dm_exec_sessions as de
    left join
    sys.dm_exec_requests as nl
        on nl.session_id = de.session_id
    outer apply
    (
        select top 1
            dbid,
            last_batch,
            open_tran,
            sql_handle,
            session_id,
            blocking_session_id,
            lastwaittype,
            waittime
        from
        (
            select
                sys1.dbid,
                sys1.last_batch,
                sys1.open_tran,
                sys1.sql_handle,
                sys2.spid as session_id,
                sys2.blocked as blocking_session_id,
                sys2.lastwaittype,
                sys2.waittime,
                sys2.cpu,
                sys2.physical_io,
                sys2.memusage
            from
                sys.sysprocesses as sys1
                inner join
                sys.sysprocesses as sys2
                    on sys1.spid = sys2.blocked
            ) as blocked
        where
            (de.session_id = blocked.session_id or de.session_id = blocked.blocking_session_id)
    ) as blocked
    outer apply
    sys.dm_exec_sql_text(coalesce(nl.sql_handle, blocked.sql_handle)) as dec
    outer apply
    sys.dm_exec_query_plan(nl.plan_handle) as deqp
    left join
    sys.dm_exec_query_stats as deco
        on nl.sql_handle = deco.sql_handle
        and nl.plan_handle = deco.plan_handle
        and nl.statement_start_offset = deco.statement_start_offset
        and nl.statement_end_offset = deco.statement_end_offset
where
    de.session_id <> @@spid
and
    de.host_name is not null
and
    coalesce(db_name(nl.database_id), db_name(blocked.dbid)) is not null
order by
    nl.total_elapsed_time desc;

Conclusion

By leveraging the provided SQL query, database administrators can gain valuable insights into the current state of SQL Server requests, enabling them to monitor performance, diagnose issues, and optimize database operations effectively. Regular monitoring of active sessions and their associated requests is crucial for maintaining the health and performance of SQL Server databases.

Rate Your Experience

: 89 : 0