How To Check Active Connections In SQL Server
Introduction
Query Overview
T-SQL description
Conclusion
Introduction
In SQL Server database management, understanding and monitoring current requests is essential for maintaining optimal performance and identifying potential issues.
This blog post explores a comprehensive SQL query that retrieves real-time information about active sessions and their associated requests, providing valuable
insights into database operations.
Query Overview
The provided SQL query is designed to retrieve detailed information about current SQL Server requests, including session details, query text, execution statistics, and more. Let's break down its key components:
- Session Details: The query starts by retrieving session-related information such as session ID, status, host name, login name, and program name.
- Blocking Session ID:It identifies any blocking session IDs, which can help diagnose performance bottlenecks and concurrency issues within the database.
- Query Text:The query extracts the text of the executing SQL query, handling cases where the query spans multiple lines or contains special characters.
- Query Plan:It retrieves the execution plan associated with each query, aiding in performance analysis and optimization.
- Performance Metrics:The query gathers various performance metrics, including total elapsed time, CPU time, reads, writes, and logical reads.
- Transaction Details:Information about transactions, such as transaction ID and isolation level, is included to track transactional activity.
- Filtering and Ordering:The query filters out system sessions and ensures that only active sessions with valid host names are included. The results are then ordered by total elapsed time in descending order to prioritize
resource-intensive sessions.
select
getdate() time_now,
de.session_id,
de.status,
case
when nl.blocking_session_id <> 0 and blocked.session_id is null then
nl.blocking_session_id
when nl.blocking_session_id <> 0 and de.session_id <> blocked.blocking_session_id then
blocked.blocking_session_id
when nl.blocking_session_id = 0 and de.session_id = blocked.session_id then
blocked.blocking_session_id
when nl.blocking_session_id <> 0 and de.session_id = blocked.blocking_session_id then
nl.blocking_session_id
else
null
end as blocking_session_id,
case
when deco.statement_start_offset is not null and deco.statement_end_offset is not null then
substring(
dec.text,
(deco.statement_start_offset/2)+1,
((
case deco.statement_end_offset
when -1 then
datalength(dec.text)
else
deco.statement_end_offset
end - deco.statement_start_offset
)/2) + 1
)
else
dec.text
end as query_text,
deqp.query_plan,
nl.total_elapsed_time total_elapsed_time_ms,
nl.total_elapsed_time/1000 total_elapsed_time_sec,
nl.total_elapsed_time/1000/60 total_elapsed_time_min,
nl.cpu_time,
nl.reads,
nl.writes,
nl.logical_reads,
de.host_name,
de.login_name,
de.program_name,
db_name(nl.database_id) db_name,
nl.request_id,
nl.transaction_id,
nl.wait_type,
nl.wait_time,
nl.last_wait_type,
nl.wait_resource,
nl.open_transaction_count,
nl.open_resultset_count,
nl.transaction_isolation_level,
case de.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,
nl.row_count,
dec.objectid,
nl.sql_handle,
nl.plan_handle
from
sys.dm_exec_sessions as de
left join
sys.dm_exec_requests as nl
on nl.session_id = de.session_id
outer apply
(
select top 1
dbid,
last_batch,
open_tran,
sql_handle,
session_id,
blocking_session_id,
lastwaittype,
waittime
from
(
select
sys1.dbid,
sys1.last_batch,
sys1.open_tran,
sys1.sql_handle,
sys2.spid as session_id,
sys2.blocked as blocking_session_id,
sys2.lastwaittype,
sys2.waittime,
sys2.cpu,
sys2.physical_io,
sys2.memusage
from
sys.sysprocesses as sys1
inner join
sys.sysprocesses as sys2
on sys1.spid = sys2.blocked
) as blocked
where
(de.session_id = blocked.session_id or de.session_id = blocked.blocking_session_id)
) as blocked
outer apply
sys.dm_exec_sql_text(coalesce(nl.sql_handle, blocked.sql_handle)) as dec
outer apply
sys.dm_exec_query_plan(nl.plan_handle) as deqp
left join
sys.dm_exec_query_stats as deco
on nl.sql_handle = deco.sql_handle
and nl.plan_handle = deco.plan_handle
and nl.statement_start_offset = deco.statement_start_offset
and nl.statement_end_offset = deco.statement_end_offset
where
de.session_id <> @@spid
and
de.host_name is not null
and
coalesce(db_name(nl.database_id), db_name(blocked.dbid)) is not null
order by
nl.total_elapsed_time desc;
T-SQL description
Now, let's break down each component and understand its significance
Column | Description
-------------------------------- | --------------------------------------------
time_now | Time this report is generated.
session_id | ID of the session which this request is related.
status | Request status, Background, Running, Runnable, Sleeping, Suspended.
blocking_session_id | ID of the session that is blocking this request.
query_text | Text of the SQL query.
query_plan | Query plan in XML format.
total_elapsed_time_ms | Total elapsed time in ms since the request arrived.
total_elapsed_time_sec | Total elapsed time in seconds since the request arrived.
total_elapsed_time_min | Total elapsed time in minutes since the request arrived.
cpu_time | CPU time used by this request.
reads | Number of disk reads performed by this request.
writes | Number of disk writes performed by this request.
logical_reads | Number of logical reads performed by this request.
host_name | Name of the client workstation.
login_name | SQL Server login used for this execution.
program_name | Client program that initiated the request.
db_name | Name of database the request is executing against.
request_id | Unique request ID.
transaction_id | ID of the transaction in which this request executes.
wait_type | Type of wait if this request is blocked.
wait_time | Wait time in ms if this request is blocked.
last_wait_type | Type of wait if this request has previously been blocked.
wait_resource | Resource the request is waiting for if blocked.
open_transaction_count | Number of transaction open for this request.
open_resultset_count | Number of result sets open for this request.
transaction_isolation_level | Isolation level of the transaction for this request.
transaction_isolation_level_text | Isolation level of the transaction for this request in text form.
row_count | Number of rows returned to the client for this request.
objectid | ID of the object.
sql_handle | SQL handle for this request's SQL.
plan_handle | Plan handle for this requests's SQL execution plan.
Conclusion
By leveraging the provided SQL query, database administrators can gain valuable insights into the current state of SQL Server requests, enabling them to monitor
performance, diagnose issues, and optimize database operations effectively. Regular monitoring of active sessions and their associated requests is crucial for
maintaining the health and performance of SQL Server databases.
Related content