How do I see active SQL Server connections?

sql

database-administration

sql-server-performance

connection-management

byNikita Barsukov·Feb 13, 2025

Instantly view active SQL Server connections using this direct T-SQL query:

SELECT 
    spid,          -- You shall not pass... without ID
    status,        -- Someone got status
    loginame,      -- Who's there?
    hostname,      -- Hi, I'm Mr. Server
    db_name(dbid) as 'Database', -- Lovely database you got there
    cmd            -- Exec-commando
FROM 
    sys.sysprocesses
WHERE 
    spid > 50;

Crucial details revealed include the session's ID (spid), its status, the user (loginame), the server (hostname), the database being accessed, and the command in action.

Tool Belt: options available

SQL Server provides a toolkit to oversee active connections:

Activity Monitor: Part of the SQL Server Management Studio suite, showcasing connection stats like client IPs and databases visually.
Stored Procedures: sp_who and sp_who2 are utilities producing comprehensive details on active sessions and connections.
Dynamic Management Views (DMVs): sys.dm_exec_sessions and sys.dm_exec_connections provide more nuanced inspection into the active SQL connections.

Deep Dive: advanced analysis

For lower-level connection analysis, you can utilize:

sp_who with filters to isolate information based on user sessions or databases.
sys.dm_exec_sql_text combined with COALESCE to discover the SQL batch text under execution.
@@SPID to exclude the current session from results, keeping the output relevant to your investigation.
sys.dm_tran_locks to identify connections responsible for lock contention.

Health Check: monitoring & performance

Regular connection health checks can significantly improve SQL Server's performance:

I/O Utilization: Monitor num_reads, num_writes, and last_read to understand I/O load per connection.
Lock Requests: Regular checks can help identify sessions with granted vs. pending lock requests, crucial to deadlock situations.

Admin Stuff: permissions and precautions

Accessing some of this information requires appropriate permissions. Take care to:

Have suitable permissions for DMV access.
Use the KILL command responsibly to terminate sessions, preferably as the last resort.

Problem-Solving: common challenges

You might stumble upon some standard challenges:

Blocked Processes: Regular checks for blocked sessions facilitate smooth database operations.
Long-running Queries: Invoke sys.dm_exec_requests with sys.dm_exec_sql_text to identify and terminate runaway queries.
High Connection Counts: Scripts counting connections can diagnose bottlenecks caused by uncommitted transactions or lingering sessions.