In some requirements, it may be necessary to know some system information about the front-end programs connected to SQL Server, such as the computer name of the front-end connection, the IP address, when to start requesting the connection, and when to end the connection.
If you are not familiar with SQL Server's system functions or views, this functionality may seem more complex, but in fact, the dynamic management view of SQL Server has given us this information, let's take a look at two dynamic management views.
1, sys.dm_exec_sessions
This view provides some information about all the clients that connect to SQL Server, following the columns returned by sys.dm_exec_sessions:
Column Name |
Data Type |
Description |
session_id |
smallint |
Identifies the session associated with each active primary connection. |
Login_time |
Datetime |
The time the session was established. |
Host_name |
nvarchar (128) |
The host associated with the session. |
Program_name |
nvarchar (128) |
The program associated with the session. |
host_process_id |
Int |
The process ID associated with the session. |
Client_version |
Int |
The version of the interface used by the client to connect to the server. |
Client_interface_name |
nvarchar (32) |
The name of the interface used by the client to connect to the server. |
security_id |
varbinary (85) |
The Microsoft Windows security ID that is associated with the login name. |
Login_name |
nvarchar (128) |
The SQL logon name associated with the session. |
Nt_domain |
nvarchar (128) |
The domain from which the session connection was established. |
Nt_user_name |
nvarchar (128) |
The user name associated with the session. |
Status |
nvarchar (30) |
The state of the session. Possible values: 1, run-one or more requests are currently running 2, sleep-no requests are currently running 3, Hibernate-session is in pre-logon state |
Context_info |
varbinary (128) |
The context_info value of the session. |
Cpu_time |
Int |
The CPU time (in milliseconds) that the session occupies. |
Memory_usage |
Int |
The number of 8 KB memory pages that the session occupies. |
Total_scheduled_time |
Int |
The total amount of time (in milliseconds) that is spent on the execution of the scheduled session that contains the request. |
Total_elapsed_time |
Int |
Elapsed time (in milliseconds) since the session was established. |
endpoint_id |
Int |
ID of the endpoint associated with the session. |
Last_request_start_time |
Datetime |
The start time of the most recent session request. This includes the request that is currently executing. |
Last_request_end_time |
Datetime |
The time when the last session request was completed. |
Reads |
bigint |
The number of reads performed by the request in the session during the session. |
Writes |
bigint |
The number of writes performed by the request in the session during the session. |
Logical_reads |
bigint |
The number of logical reads that have been performed on the session. |
Is_user_process |
Bit |
0 if the session is a system session. Otherwise, it is 1. |
Text_size |
Int |
The TEXTSIZE setting for the session. |
Language |
nvarchar (128) |
The LANGUAGE setting for the session. |
Date_format |
nvarchar (3) |
The DateFormat setting for the session. |
Date_first |
smallint |
The Datefirst setting for the session. |
Quoted_identifier |
Bit |
The QUOTED_IDENTIFIER setting for the session. |
Arithabort |
Bit |
The ARITHABORT setting for the session. |
ansi_null_dflt_on |
Bit |
The ANSI_NULL_DFLT_ON setting for the session. |
Ansi_defaults |
Bit |
The ANSI_DEFAULTS setting for the session. |
Ansi_warnings |
Bit |
The ANSI_WARNINGS setting for the session. |
Ansi_padding |
Bit |
The ANSI_PADDING setting for the session. |
Ansi_nulls |
Bit |
The ANSI_NULLS setting for the session. |
Concat_null_yields_null |
Bit |
The CONCAT_NULL_YIELDS_NULL setting for the session. |
Transaction_isolation_level |
smallint |
The transaction isolation level of the session. 0 = unspecified 1 = uncommitted Read 2 = Read Committed 3 = repeatable 4 = Serializable 5 = Snapshot |
Lock_timeout |
Int |
The LOCK_TIMEOUT setting for the session. The value is measured in milliseconds. |
Deadlock_priority |
Int |
The Deadlock_priority setting for the session. |
Row_count |
bigint |
The number of rows returned by the session so far. |
Prev_error |
Int |
The ID of the most recent error returned by the session. |
For example, we want to see those hosts connected to SQL Server servers, you can use the following SQL statement:
Select distinct host_name from sys.dm_exec_sessions
To see those users connected to SQL Server servers:
Select distinct login_name from sys.dm_exec_sessions
Of course, using the columns above, we can get more client information we want.
2, Sys.dm_exec_connections
This view returns the details of each connection connected to the SQL Server servers, and the following is the column returned by Sys.dm_exec_connections:
Column Name |
Data Type |
Description |
session_id |
Int |
Identifies the session associated with this connection. |
most_recent_session_id |
Int |
Displays the session ID of the most recent request associated with this connection. |
Connect_time |
Datetime |
Timestamp when the connection was established. |
Net_transport |
nvarchar (40) |
Describes the physical transport protocol used by this connection. |
Protocol_type |
nvarchar (40) |
Specifies the protocol type of the payload. This parameter is currently distinguishable from TDS (TSQL) and SOAP. |
Protocol_version |
Int |
The version of the data access protocol associated with this connection. |
endpoint_id |
Int |
Unique identifier of the endpoint associated with this connection. This endpoint_id can be used to query the sys.endpoints view. |
Encrypt_option |
nvarchar (40) |
A Boolean value that indicates whether encryption is enabled for this connection. |
Auth_scheme |
nvarchar (40) |
Specifies the SQL server/nt authentication to use with this connection. |
Node_affinity |
smallint |
Displays the SOS node associated with this connection. |
Num_reads |
Int |
The number of read packets that have occurred in this connection. |
Num_writes |
Int |
The number of write packets that have occurred in this connection. |
Last_read |
Datetime |
Timestamp of the last read operation occurred in this connection. |
Last_write |
Datetime |
Timestamp of the last write operation in this connection. |
Net_packet_size |
Int |
The size of the network packet used for information and data. |
Client_net_address |
varchar (40) |
The host address of the client to which this server is connected. |
Client_tcp_port |
Int |
The port number on the client that is associated with the connection. |
Local_net_address |
varchar (40) |
Displays the IP address of the destination server for this connection. Available only for connections that use the TCP transport provider. |
Local_tcp_port |
Int |
If this connection uses a TCP transport, the TCP port of the destination server for that connection is displayed. |
connection_id |
uniqueidentifier |
Each connection is uniquely identified. |
parent_connection_id |
uniqueidentifier |
Identifies the primary connection that the MARS session is using. |
Most_recent_sql_handle |
varbinary (64) |
The SQL handle of the last request executed on this connection. The Most_recent_sql_handle column is always synchronized with the most_recent_session_id column. |
For example, I want to see the IP of the client IP that is currently connected and the server on which SQL Servers are located, using the following SQL query:
Select client_net_address ' Client IP ', local_net_address ' server IP ' from sys.dm_exec_connections where [email protected] @spid
The function of the @ @spid is to return the session ID of the current process.
How to get the IP address, computer name and other information of the front-end connection in SQL Server