Monitoring Application Sessions in SQL Server with T-SQL

Introduction
T-SQL to monitor application sessions
T-SQL description
Conclusion

Introduction

In the world of database management, keeping tabs on how your applications interact with your SQL Server is key to maintaining performance and stability. If you've ever wondered how to monitor the sessions created by your applications and track their performance, you're in the right place.

T-SQL to monitor application sessions

This SQL query retrieves information from the sys.dm_exec_sessions and sys.dm_exec_connections dynamic management views, providing details about each session created by your applications.

select
    des.program_name,
    des.login_name,
    des.host_name,
    dec.client_net_address,
    des.session_id,
    des.database_id, 
    des.status,
    des.cpu_time,
    des.memory_usage,
    des.reads,
    des.writes,
    des.logical_reads,
    des.row_count,
    des.total_scheduled_time,
    des.total_elapsed_time,
    des.last_request_start_time,
    des.last_request_end_time,
    des.transaction_isolation_level,
    case des.transaction_isolation_level
        when 0 then 'Unspecified'
        when 1 then 'ReadUncommitted'
        when 2 then 'ReadCommitted'
        when 3 then 'Repeatable'
        when 4 then 'Serializable'
        when 5 then 'Snapshot'
    end as transaction_isolation_level_text,
    des.open_transaction_count,
	 des.program_name
from
    sys.dm_exec_sessions as des
    inner join
    sys.dm_exec_connections as dec
        on des.session_id = dec.session_id

T-SQL description

Now, let's break down each component and understand its significance

Column                           | Description
-------------------------------- | -------------------------------------------------
program_name                     | Client program that initiated the request.
login_name                       | SQL Server login used for this execution.
host_name                        | Name of the client workstation.
client_net_address               | IP address of the client workstation.
session_id                       | Unique session identifier.
database_id                      | ID of the current database for this session (SQL Server 2012).
status                           | Session status, Running, Sleeping, Dormant, Preconnect.
cpu_time                         | CPU time in ms used by this session.
memory_usage                     | Number of 8KB pages of memory used by this session.
reads                            | Number of disk reads performed by this session.
writes                           | Number of disk writes performed by this session.
logical_reads                    | Number of logical reads performed by this session.
row_count                        | Number of rows returned by this session.
total_scheduled_time             | Total time in ms this session's requests were scheduled for execution.
total_elapsed_time               | Time in ms since this session was established.
last_request_start_time          | Time at which the last request on this session began.
last_request_end_time            | Time of the last completion of a request on this session.
transaction_isolation_level      | Transaction level of the session.
transaction_isolation_level_text | Transaction level of the session in text form.
open_transaction_count           | Number of open transactions (SQL Server 2012).

By running this query, you can gain valuable insights into how your applications are performing and identify any potential issues that may arise. Whether you're a database administrator, developer, or simply curious about your SQL Server environment, this query provides a comprehensive overview of your application sessions and their performance metrics.

Conclusion

In conclusion, monitoring your application sessions in SQL Server is crucial for maintaining optimal performance and ensuring the smooth operation of your database environment. With the insights provided by this T-SQL query, you can stay on top of your application's performance and address any issues that may arise swiftly and effectively.

Rate Your Experience

: 89 : 0