Monitoring SQL Server Connections

Monitoring SQL Server connections is essential for understanding database activity, identifying performance bottlenecks, and ensuring security. In this tutorial, we will explore various methods to view active SQL Server connections, including system stored procedures, dynamic management views (DMVs), and the Activity Monitor.

Using System Stored Procedures

SQL Server provides several system stored procedures that can be used to monitor connections. The most commonly used ones are sp_who and sp_who2.

• sp_who: This stored procedure returns information about current users, sessions, and processes in an instance of the SQL Server Database Engine. It can be filtered to return only non-idle processes or those belonging to a specific user or session.
• sp_who2: This is an undocumented system stored procedure that provides more detailed information than sp_who. However, since it’s not officially supported by Microsoft, its usage is not recommended in production environments.

Using Dynamic Management Views (DMVs)

Dynamic management views provide a more comprehensive and flexible way to monitor SQL Server connections. The following DMVs are commonly used for this purpose:

• sys.dm_exec_sessions: This view returns information about all active user sessions on the server.
• sys.dm_exec_connections: This view returns information about all active connections to the server.
• sys.dm_tran_locks: This view returns information about all locks currently held by SQL Server.

Here’s an example query that uses these DMVs to retrieve detailed connection information:

SELECT 
    sdes.session_id,
    sdes.login_time,
    sdes.last_request_start_time,
    sdes.last_request_end_time,
    sdes.is_user_process,
    sdes.host_name,
    sdes.program_name,
    sdes.login_name,
    sdes.status,

    sdec.num_reads,
    sdec.num_writes,
    sdec.last_read,
    sdec.last_write,
    sdes.reads,
    sdes.logical_reads,
    sdes.writes,

    DatabaseName = COALESCE(db_name(sdes.database_id), N''),
    sdest.ObjName,
    sdes.client_interface_name,
    sdes.nt_domain,
    sdes.nt_user_name,
    sdec.client_net_address,
    sdec.local_net_address,
    sdest.Query,
    KillCommand = 'Kill ' + CAST(sdes.session_id AS VARCHAR)

FROM sys.dm_exec_sessions AS sdes
INNER JOIN sys.dm_exec_connections AS sdec
    ON sdec.session_id = sdes.session_id

CROSS APPLY (
    SELECT DB_NAME(dbid) AS DatabaseName,
        OBJECT_NAME(objectid) AS ObjName,
        COALESCE((
            SELECT TEXT AS [processing-instruction(definition)]
            FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)
            FOR XML PATH(''),
                TYPE
            ), '') AS Query

    FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)
) sdest

WHERE sdes.session_id <> @@SPID;

Using Activity Monitor

Activity Monitor is a graphical tool in SQL Server Management Studio (SSMS) that provides real-time information about server activity. To access Activity Monitor, follow these steps:

1. Open SSMS and connect to your SQL Server instance.
2. Right-click on the server name in the Object Explorer and select "Activity Monitor" from the context menu.
3. Alternatively, you can use the keyboard shortcut Ctrl + Alt + A.

The Activity Monitor window displays several sections, including:

• Overview: Provides a summary of server activity, including CPU usage, memory usage, and disk I/O.
• Resource Waits: Displays information about resource waits, such as locks, latches, and network waits.
• Data File I/O: Shows data file I/O statistics for each database file.
• Recent Expensive Queries: Lists the most expensive queries executed on the server.

By using these methods, you can effectively monitor SQL Server connections and gain insights into database activity, performance, and security.