Monitoring and Analyzing SQL Server Sessions with T-SQL

Introduction

SQL Server is a powerful relational database management system used by organizations to store and manage their data. As an administrator or developer, it’s crucial to have visibility into the active sessions and their execution details for effective monitoring and troubleshooting. In this blog post, we will explore a T-SQL query that allows us to monitor and analyze SQL Server sessions, providing valuable insights into the ongoing activities within the database.

Query Overview

The T-SQL query provided in this blog post leverages system dynamic management views (DMVs) to gather information about active sessions, their execution details, resource usage, and more. Let’s delve into each section of the query to understand its purpose:

1. Gathering Session Information:
   The query starts by joining the sys.dm_exec_requests and sys.dm_exec_sessions DMVs to retrieve information about active sessions. It selects relevant columns such as session ID, original login name, status, blocking session ID, command, and database name.
2. Calculating Duration and Completion Percentage:
   To provide insights into the duration and progress of each session, the query utilizes the start_time column from sys.dm_exec_requests and calculates the elapsed time since the session started. It also calculates the percentage of completion for long-running tasks.
3. Extracting Executing SQL Statement:
   By using the sys.dm_exec_sql_text DMV and applying the SUBSTRING function, the query extracts the executing SQL statement for each session. This allows administrators and developers to identify the specific queries being executed.
4. Monitoring Wait Statistics:
   To monitor potential resource contention, the query captures information related to wait resources, wait time, and the last wait type for each session. This data helps identify sessions that might be waiting for resources and causing delays.
5. Capturing Additional Session Details:
   The query includes various other details such as login time, last request start and end times, host name, program name (including SQL Agent job information), open transaction count, transaction isolation level, and I/O statistics (reads, writes, and logical reads).
6. Displaying Total Rows:
   To provide insights into the number of rows affected by a session’s execution, the query includes a column for row count. However, as “RowCount” is a reserved keyword, it is renamed as “TotalRows” in the query.

Query

SELECT
    ss.session_id AS SessionID, -- Session ID
    original_login_name AS OriginalLoginName, -- Original login name
    sp.status AS Status, -- Session status
    blocking_session_id AS BlockedBySessionID, -- Blocking session ID
    command AS Command, -- Command being executed
    DB_NAME(sp.database_id) AS DatabaseName, -- Database name
    [objectid] AS ObjectID, -- Object ID
    sp.cpu_time AS CPUTime, -- CPU time consumed
    percent_complete AS PercentageComplete, -- Percentage of completion
    CASE
        WHEN DATEDIFF(mi, start_time, GETDATE()) > 60 THEN CONVERT(VARCHAR(4), (DATEDIFF(mi, start_time, GETDATE()) / 60)) + ' hr ' -- Calculating duration in hours if more than 60 minutes
        ELSE ''
    END +
    CASE
        WHEN DATEDIFF(ss, start_time, GETDATE()) > 60 THEN CONVERT(VARCHAR(4), (DATEDIFF(mi, start_time, GETDATE()) % 60)) + ' min ' -- Calculating duration in minutes if more than 60 seconds
        ELSE ''
    END +
    CONVERT(VARCHAR(4), (DATEDIFF(ss, start_time, GETDATE()) % 60)) + ' sec' AS Duration, -- Duration of the session
    estimated_completion_time / 60000 AS EstimatedCompletionTimeMin, -- Estimated completion time in minutes
    [text] AS InputStreamText, -- Input stream or text
    (SUBSTRING(
        [text],
        statement_start_offset / 2 + 1,
        (
            (CASE WHEN statement_end_offset < 0 THEN (LEN(CONVERT(nvarchar(max), [text])) * 2) ELSE statement_end_offset END) - statement_start_offset
        ) / 2 + 1
    )) AS ExecutingSQLStatement, -- Executing SQL statement
    wait_resource AS WaitResource, -- Resource being waited upon
    wait_time / 1000 AS WaitTimeSec, -- Wait time in seconds
    last_wait_type AS LastWaitType, -- Last wait type
    login_time AS LoginTime, -- Login time
    last_request_start_time AS LastRequestStartTime, -- Start time of the last request
    last_request_end_time AS LastRequestEndTime, -- End time of the last request
    host_name AS HostName, -- 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))
            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 ProgramName, -- Program name, including SQL Agent job information
    sp.open_transaction_count AS OpenTransactionCount, -- Open transaction count
    CASE sp.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 TransactionIsolationLevel, -- Transaction isolation level
    sp.reads AS Reads, -- Number of reads
    sp.writes AS Writes, -- Number of writes
    sp.logical_reads AS LogicalReads, -- Number of logical reads
    sp.lock_timeout AS LockTimeout, -- Lock timeout
    sp.row_count AS TotalRows -- Total number of rows affected
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'

Conclusion

By utilizing the provided T-SQL query, administrators and developers gain a comprehensive understanding of the active sessions within a SQL Server instance. They can monitor the progress of ongoing tasks, identify blocking sessions, analyze resource usage, and gain insights into executing SQL statements. This information enables efficient troubleshooting, performance optimization, and better overall management of SQL Server environments.

Note: It’s essential to exercise caution when executing queries against production environments and ensure appropriate permissions are granted to access the required DMVs.

Remember, monitoring and analyzing SQL Server sessions is a crucial aspect of maintaining a healthy and performant database environment. By leveraging the power of T-SQL and system DMVs, administrators can effectively manage and optimize their SQL Server instances, ensuring optimal performance and user experience.

Stay tuned for more tips and insights on SQL Server administration and development!

We hope you find this blog post informative and useful in your SQL Server journey. Happy querying!