sys.dm_exec_connections - Usage & Examples

SQL Server's connection manager maintains an internal connection pool where each connection consumes memory for context switching and security token caching. The sys.dm_exec_connections DMV exposes the CONNECTION_ID structure from the SQL Server Network Interface (SNI) layer, which tracks protocol-specific connection metadata including TCP socket information, named pipe handles, and shared memory connections.

Connection lifecycle begins when SNI receives a connection request, assigns a connection ID, and performs authentication through SSPI or SQL Server authentication. The connection manager then associates the connection with a worker thread from the thread pool. Idle connections remain in the connection cache until explicitly closed by the client or application connection pool.

SQL Server 2016 introduced Always Encrypted connection metadata tracking in this DMV. SQL Server 2019 added UTF-8 collation support indicators. SQL Server 2022 enhanced the connection context with Azure AD authentication details and parameter sensitive plan optimization flags. The connection_id field is a GUID that uniquely identifies each connection. For Extended Events tracing, use the session_id (integer) column to correlate with other DMVs.

Memory pressure occurs when connection counts exceed the calculated connection limit based on available virtual address space. Each connection reserves virtual memory even when idle, contributing to the 32-bit VAS exhaustion problems in older SQL Server versions. Modern 64-bit instances rarely hit memory limits from connections alone but can experience scheduler contention with excessive connection churn.

-- Active connections by login and application
SELECT 
    login_name,
    program_name,
    COUNT(*) as connection_count,
    AVG(DATEDIFF(minute, connect_time, GETDATE())) as avg_duration_minutes
FROM sys.dm_exec_connections c
INNER JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id
GROUP BY login_name, program_name
ORDER BY connection_count DESC;

-- Connection protocol distribution and encryption status
SELECT 
    net_transport,
    encrypt_option,
    auth_scheme,
    COUNT(*) as connection_count,
    MIN(connect_time) as oldest_connection,
    MAX(connect_time) as newest_connection
FROM sys.dm_exec_connections
GROUP BY net_transport, encrypt_option, auth_scheme
ORDER BY connection_count DESC;

-- Idle connections consuming resources
SELECT 
    c.session_id,
    s.login_name,
    s.program_name,
    s.host_name,
    DATEDIFF(minute, c.connect_time, GETDATE()) as connection_age_minutes,
    DATEDIFF(minute, s.last_request_end_time, GETDATE()) as idle_minutes,
    c.net_transport,
    c.client_net_address
FROM sys.dm_exec_connections c
INNER JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id
WHERE s.last_request_end_time < DATEADD(minute, -30, GETDATE())
    AND s.session_id > 50
ORDER BY idle_minutes DESC;

-- Connection endpoint analysis for troubleshooting
SELECT 
    c.connection_id,
    c.session_id,
    c.client_net_address,
    c.local_net_address,
    c.local_tcp_port,
    e.name as endpoint_name,
    e.type_desc as endpoint_type,
    s.login_name,
    r.command,
    r.wait_type
FROM sys.dm_exec_connections c
LEFT JOIN sys.endpoints e ON c.endpoint_id = e.endpoint_id
LEFT JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE c.session_id IS NOT NULL;

-- Connection memory usage estimation
SELECT 
    COUNT(*) as total_connections,
    COUNT(*) as total_connection_count,
    SUM(CASE WHEN s.last_request_end_time < DATEADD(hour, -1, GETDATE()) THEN 1 ELSE 0 END) as idle_over_1hour,
    AVG(DATEDIFF(minute, c.connect_time, GETDATE())) as avg_connection_age_minutes
FROM sys.dm_exec_connections c
INNER JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id
WHERE s.session_id > 50;

Kill idle connections consuming resources

-- Terminate connections idle for more than 2 hours
-- TEST IN DEVELOPMENT FIRST - This will drop active user connections
DECLARE @sql NVARCHAR(MAX) = '';
DECLARE @session_id INT;

DECLARE kill_cursor CURSOR FOR
SELECT DISTINCT s.session_id
FROM sys.dm_exec_connections c
INNER JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id
WHERE s.last_request_end_time < DATEADD(hour, -2, GETDATE())
    AND s.session_id > 50  -- Exclude system sessions
    AND s.login_name NOT LIKE '%SQLSERVERAGENT%'  -- Preserve SQL Agent connections
    AND s.program_name NOT LIKE 'Microsoft SQL Server Management Studio%';

OPEN kill_cursor;
FETCH NEXT FROM kill_cursor INTO @session_id;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'KILL ' + CAST(@session_id AS VARCHAR(10));
    PRINT 'Executing: ' + @sql;
    -- Uncomment the next line after testing
    -- EXEC sp_executesql @sql;
    FETCH NEXT FROM kill_cursor INTO @session_id;
END;

CLOSE kill_cursor;
DEALLOCATE kill_cursor;

Configure connection timeout at server level

-- Set server-wide connection timeout to 15 minutes for SQL authentication
-- This affects new connections only, existing connections retain their timeout
EXEC sp_configure 'remote login timeout', 15;
RECONFIGURE;

-- For immediate effect on idle connections, use connection pooling timeout
-- This requires application-side connection string modification
SELECT 
    'Consider setting Connection Timeout=900 in connection strings' as recommendation,
    'Current remote login timeout: ' + CAST(value AS VARCHAR(10)) + ' seconds' as current_setting
FROM sys.configurations 
WHERE name = 'remote login timeout';

Create connection monitoring alert

-- Create alert for excessive connection counts
-- Adjust threshold based on your server capacity
USE msdb;

EXEC msdb.dbo.sp_add_alert
    @name = N'High Connection Count Alert',
    @message_id = 0,
    @severity = 0,
    @notification_message = N'Connection count exceeded threshold',
    @category_name = N'[Uncategorized]',
    @performance_condition = N'SQLServer:General Statistics|User Connections||>|200',
    @job_name = N'';  -- Add job name if you want automated response

-- Create operator and notification (replace email)
EXEC msdb.dbo.sp_add_operator
    @name = N'DBA_Team',
    @email_address = N'dba@yourcompany.com';

EXEC msdb.dbo.sp_add_notification
    @alert_name = N'High Connection Count Alert',
    @operator_name = N'DBA_Team',
    @notification_method = 1;

Implement connection pooling at the application layer using connection pool size limits appropriate for your workload, typically 100-200 connections per application server. Configure connection string parameters: Connection Timeout=30, Command Timeout=60, and Pooling=true with appropriate Min Pool Size and Max Pool Size values.

Monitor connection patterns using SQL Server Agent jobs that query sys.dm_exec_connections every 15 minutes, alerting when connection counts exceed 80% of your determined baseline. Create custom performance counters for connection age and idle time tracking.

Establish connection lifecycle management through application architecture patterns: implement proper connection disposal in try-finally blocks, use dependency injection containers that manage connection lifetimes, and avoid long-running connections in web applications. Configure load balancers with proper connection draining during deployments.

Set up automated connection cleanup jobs that identify and terminate connections idle for more than your business requirement threshold. Configure server-level settings: enable connection pooling at the protocol level, set appropriate values for max worker threads based on CPU core count, and monitor memory grants per connection through Resource Governor when necessary.

Review application connection string configurations quarterly, ensuring consistent timeout values across all applications connecting to the same SQL Server instance. Implement application-level connection health checks that validate connections before use and properly handle connection failures with exponential backoff retry logic.