sys.dm_exec_sessions - Usage & Examples

The sys.dm_exec_sessions DMV reads directly from the session control structures maintained by the SQL Server scheduler. Each session maintains a Session Control Block (SCB) in memory that tracks connection metadata, authentication context, session-level settings, and resource consumption metrics. The DMV performs a lightweight scan of these control blocks without acquiring significant locks.

Session data originates from multiple internal components. The security manager populates authentication fields during login. The buffer manager tracks reads and writes per session. The lock manager maintains transaction isolation level and deadlock priority. The scheduler components track CPU time, logical reads, and wait statistics accumulated across all requests within the session.

SQL Server 2016 introduced additional columns for row-level security context and Always Encrypted key information. SQL Server 2019 added UTF-8 collation support indicators. SQL Server 2022 enhanced the security context fields for Azure AD authentication scenarios, including managed identity information.

The DMV filters out sessions in pre-login states, showing only authenticated connections. System sessions (session_id <= 50) represent internal SQL Server processes like checkpoint, log writer, and ghost cleanup. User sessions begin at session_id 51.

Memory grants appear in this DMV only after a query requests memory through the memory manager. The memory columns reflect currently allocated memory, not peak usage during query execution.

-- Active user sessions with current activity and resource usage
SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    s.cpu_time,
    s.memory_usage * 8 AS memory_kb,
    s.total_scheduled_time,
    s.total_elapsed_time,
    s.reads,
    s.writes,
    s.logical_reads,
    r.command,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE s.session_id > 50
AND s.status IN ('running', 'sleeping', 'suspended')
ORDER BY s.logical_reads DESC;

-- Sessions consuming excessive memory or CPU resources, including connection details
SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    c.net_transport,
    c.auth_scheme,
    c.encrypt_option,
    s.cpu_time,
    s.memory_usage * 8 AS memory_kb,
    s.reads,
    s.writes,
    s.logical_reads,
    s.login_time,
    DATEDIFF(minute, s.last_request_start_time, GETDATE()) AS idle_minutes
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
WHERE s.session_id > 50
AND (s.memory_usage > 1024 OR s.cpu_time > 10000 OR s.logical_reads > 1000000)
ORDER BY s.memory_usage DESC, s.cpu_time DESC;

-- Long-running idle sessions with open transactions
SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    s.open_transaction_count,
    s.login_time,
    s.last_request_start_time,
    s.last_request_end_time,
    DATEDIFF(minute, s.last_request_end_time, GETDATE()) AS idle_minutes,
    t.transaction_begin_time,
    t.transaction_type,
    t.transaction_state
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_tran_session_transactions st ON s.session_id = st.session_id
INNER JOIN sys.dm_tran_active_transactions t ON st.transaction_id = t.transaction_id
WHERE s.session_id > 50
AND DATEDIFF(minute, s.last_request_end_time, GETDATE()) > 30
ORDER BY idle_minutes DESC;

-- Connection patterns and authentication methods by client
SELECT 
    s.host_name,
    s.program_name,
    s.login_name,
    c.net_transport,
    c.auth_scheme,
    c.encrypt_option,
    COUNT(*) AS connection_count,
    AVG(s.cpu_time) AS avg_cpu_time,
    AVG(s.logical_reads) AS avg_logical_reads,
    MAX(s.login_time) AS most_recent_login
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
WHERE s.session_id > 50
GROUP BY s.host_name, s.program_name, s.login_name, c.net_transport, c.auth_scheme, c.encrypt_option
HAVING COUNT(*) > 1
ORDER BY connection_count DESC;

-- Sessions with blocking or deadlock history
SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.transaction_isolation_level,
    s.deadlock_priority,
    s.row_count,
    s.prev_error,
    r.blocking_session_id,
    r.wait_type,
    r.wait_resource,
    r.command,
    r.percent_complete
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE s.session_id > 50
AND (r.blocking_session_id > 0 OR s.prev_error <> 0)
ORDER BY r.blocking_session_id, s.prev_error DESC;

Kill Idle Sessions with Open Transactions Terminates sessions that have been idle for more than 2 hours while holding open transactions, which can block log truncation and cause performance degradation.

-- Generate KILL commands for idle sessions with open transactions
-- Review the output before executing the generated commands
DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql = @sql + 'KILL ' + CAST(s.session_id AS VARCHAR(10)) + '; -- ' + 
              s.login_name + ' from ' + ISNULL(s.host_name, 'Unknown') + 
              ', idle for ' + CAST(DATEDIFF(minute, s.last_request_end_time, GETDATE()) AS VARCHAR(10)) + 
              ' minutes' + CHAR(13) + CHAR(10)
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_tran_session_transactions st ON s.session_id = st.session_id
WHERE s.session_id > 50
AND s.status = 'sleeping'
AND DATEDIFF(minute, s.last_request_end_time, GETDATE()) > 120
AND s.open_transaction_count > 0;

PRINT @sql;
-- Uncomment the next line after reviewing the commands
-- EXEC sp_executesql @sql;

Close Excessive Connections by Application Identifies and terminates connections when a single application has more than 100 concurrent sessions, typically indicating connection pooling issues.

-- Kill excess connections from applications with too many sessions
DECLARE @max_connections_per_app INT = 100;
DECLARE @sql NVARCHAR(MAX) = '';

WITH session_counts AS (
    SELECT 
        program_name,
        COUNT(*) as session_count,
        MIN(session_id) as oldest_session_id
    FROM sys.dm_exec_sessions 
    WHERE session_id > 50
    GROUP BY program_name
    HAVING COUNT(*) > @max_connections_per_app
)
SELECT @sql = @sql + 'KILL ' + CAST(s.session_id AS VARCHAR(10)) + '; -- ' + 
              'Excess connection from ' + s.program_name + CHAR(13) + CHAR(10)
FROM sys.dm_exec_sessions s
INNER JOIN session_counts sc ON s.program_name = sc.program_name
WHERE s.session_id > sc.oldest_session_id + (@max_connections_per_app - 1)
AND s.status = 'sleeping'
AND s.open_transaction_count = 0;

PRINT @sql;
-- Review output carefully before uncommenting
-- EXEC sp_executesql @sql;

Resource Governor Session Classification Updates Resource Governor workload groups based on session characteristics to control resource usage for specific applications or users.

-- Create classifier function for Resource Governor based on session data
-- This requires ALTER SERVER STATE permission and Resource Governor enabled
IF EXISTS (SELECT 1 FROM sys.resource_governor_workload_groups WHERE name = 'ReportingGroup')
BEGIN
    -- Example: Move reporting applications to limited resource group
    ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.rgClassifierFunction);
    ALTER RESOURCE GOVERNOR RECONFIGURE;
    
    PRINT 'Resource Governor updated to classify sessions based on program_name';
END
ELSE
BEGIN
    PRINT 'Resource Governor workload groups must be configured first';
END

Configure connection timeout values appropriately for your application tier. Set a reasonable command timeout (30-120 seconds) and connection timeout (15-30 seconds) to prevent abandoned connections from consuming resources indefinitely.

Implement proper connection pooling in your application layer. Use connection pooling parameters like Min Pool Size, Max Pool Size, and Connection Lifetime to control connection behavior. Monitor pool exhaustion events through application performance monitoring.

Enable the "user connections" advanced server option only when necessary, as the default value of 0 allows SQL Server to automatically manage connection limits based on available resources. Setting this too low causes connection failures under load.

Create custom alerts on session count and resource usage patterns using SQL Server Agent or external monitoring tools. Monitor sessions with high memory_usage values, excessive logical_reads, or long idle times with open transactions.

Deploy session-level Resource Governor policies for applications that tend to consume excessive resources. Create custom workload groups with memory and CPU limits to prevent runaway sessions from impacting server performance.

Audit login events and connection patterns regularly using extended events or SQL Server Audit. Track failed logins, unusual connection patterns, and sessions from unexpected client applications or hosts.

Configure appropriate isolation levels in your applications. Many connection pool issues stem from applications using unnecessarily high isolation levels that create excessive blocking and lock escalation scenarios.