Finding Active Sessions in SQL Server

642 views 07:09 0 Comments 30 July 2023
Finding Active Sessions in SQL Server

Introduction

In this blog post, we will dive into the world of active sessions and requests in SQL Server. We will learn how to extract valuable information about these sessions using a T-SQL query. Don’t worry if you’re new to SQL Server or T-SQL, as we will break down the query step by step.

Understanding Active Sessions

When users interact with a SQL Server database, they establish sessions. These sessions represent the connection between the user and the database server. Within each session, users can send requests to the server, such as executing queries or performing updates.

The SQL Query to find Active Sessions

Let’s take a look at the T-SQL query we will be discussing:

SELECT
    ss.session_id AS SPID, -- Session ID
    original_login_name, -- Original login name
    sp.status, -- Status
    blocking_session_id AS [Blk by], -- Blocking session ID
    command, -- Command
    db_name(sp.database_id) AS [Database], -- Database name
    [objectid], -- Object ID
    percent_complete AS [% Done], -- Percentage of completion
    CASE
        WHEN DATEDIFF(mi, start_time, GETDATE()) > 60 THEN CONVERT(varchar(4), (DATEDIFF(mi, start_time, GETDATE()) / 60)) + ' hr ' -- Duration: hours
        ELSE ''
    END +
    CASE
        WHEN DATEDIFF(ss, start_time, GETDATE()) > 60 THEN CONVERT(varchar(4), (DATEDIFF(mi, start_time, GETDATE()) % 60)) + ' min ' -- Duration: minutes
        ELSE ''
    END +
    CONVERT(varchar(4), (DATEDIFF(ss, start_time, GETDATE()) % 60)) + ' sec' AS [Duration], -- Duration: seconds
    estimated_completion_time / 60000 AS [ETA (Min)], -- Estimated time of completion in minutes
    [text] AS [input stream/text], -- Input stream or text
    SUBSTRING([text], statement_start_offset / 2 + 1,
              ((CASE
                      WHEN statement_end_offset < 0 THEN (LEN(CONVERT(nvarchar(max), [text])) * 2) -- Executing SQL statement
                      ELSE statement_end_offset
                  END) - statement_start_offset) / 2 + 1) AS [Executing_sql_statement],
    wait_resource, -- Wait resource
    wait_time / 1000 AS [wait_time (sec)], -- Wait time in seconds
    last_wait_type, -- Last wait type
    login_time, -- Login time
    last_request_start_time, -- Last request start time
    last_request_end_time, -- Last request end time
    host_name, -- Host name
    CASE
        WHEN program_name LIKE 'SQLAgent%Job%' THEN (
            SELECT TOP 1 '(SQLAgent Job - ' + name + ' - ' + RIGHT(program_name, LEN(program_name) - CHARINDEX(':', program_name)) -- Program name: SQL Agent job
            FROM msdb.dbo.sysjobs SJ
            WHERE UPPER(master.dbo.fn_varbintohexstr(SJ.job_id)) = UPPER(SUBSTRING([program_name], 30, 34))
        )
        ELSE program_name
    END AS [program_name], -- Program name
    sp.open_transaction_count, -- Open transaction count
    CASE sp.transaction_isolation_level
        WHEN 0 THEN 'Unspecified' -- Transaction isolation level: Unspecified
        WHEN 1 THEN 'ReadUncomitted' -- Transaction isolation level: Read Uncommitted
        WHEN 2 THEN 'ReadCommitted' -- Transaction isolation level: Read Committed
        WHEN 3 THEN 'Repeatable' -- Transaction isolation level: Repeatable
        WHEN 4 THEN 'Serializable' -- Transaction isolation level: Serializable
        WHEN 5 THEN 'Snapshot' -- Transaction isolation level: Snapshot
    END AS [transaction_isolation_level],
    sp.cpu_time, -- CPU time
    sp.reads, -- Reads
    sp.writes, -- Writes
    sp.logical_reads, -- Logical reads
    sp.lock_timeout, -- Lock timeout
    sp.row_count -- Row count
FROM sys.dm_exec_requests AS sp
OUTER APPLY sys.dm_exec_sql_text(sp.sql_handle) AS esql
RIGHT OUTER JOIN sys.dm_exec_sessions ss ON ss.session_id = sp.session_id
WHERE ss.status <> 'sleeping';
SQL

Breaking Down the Query

  1. Retrieving Session Information:
  • SPID: The Session ID representing a unique identifier for each session.
  • original_login_name: The original login name used to establish the session.
  • status: The current status of the session.
  • Blk by: The ID of the blocking session, if any.
  • command: The command being executed in the session.
  • Database: The name of the database associated with the session.
  1. Analyzing Request Details:
  • Object ID: The ID of the object (e.g., table, stored procedure) involved in the current request.
  • % Done: The percentage of completion for the current request.
  • Duration: The duration of the request in hours, minutes, and seconds.
  • ETA (Min): The estimated time of completion for the request in minutes.
  • input stream/text: The input stream or text of the request.
  • Executing_sql_statement: The SQL statement being executed.
  1. Monitoring Wait Times and Resources:
  • wait_resource: The resource causing the request to wait.
  • wait_time (sec): The amount of time the request has been waiting, in seconds.
  • last_wait_type: The type of the most recent wait the request encountered.
  1. Tracking Session and Request Timings:
  • login_time: The time when the session was established.
  • last_request_start_time: The start time of the last request made in the session.
  • last_request_end_time: The end time of the last request made in the session.
  1. Additional Information:
  • host_name: The name of the machine from which the session originates.
  • program_name: The name of the program associated with the session (including SQL Agent job name if applicable).
  • open_transaction_count: The number of open transactions in the session.
  • transaction_isolation_level: The isolation level of the session’s transactions.
  • cpu_time: The amount of CPU time consumed by the request.
  • reads, writes, logical_reads: The number of reads, writes, and logical reads performed by the request.
  • lock_timeout: The timeout setting for locks in the session.
  • row_count: The number of rows affected by the request.

Conclusion

Understanding and monitoring active sessions and requests in SQL Server is crucial for database administrators and developers. By using the provided T-SQL query, we can gather valuable insights into ongoing activities, performance metrics, and potential issues within the server.

More post on Active Sessions in SQL Server

Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *