In SQL Server environments, managing session states and transactions is key to ensuring optimal database performance. A particular challenge arises with sessions in a 'sleeping' state holding open transactions for extended periods. These sessions, while seemingly inactive, can hold locks on resources, leading to potential deadlocks or performance degradation.
Our focus is on a SQL query designed to pinpoint such sessions. The query utilizes SQL Server's dynamic management views: sys.dm_exec_sessions, sys.dm_exec_requests, and sys.dm_tran_session_transactions. These views provide real-time data about active sessions, their current requests, and associated transaction details.
The heart of the query lies in its ability to filter sessions based on specific criteria: sessions must be in a 'sleeping' state, have an open transaction, and be inactive for over 5 minutes. This precise filtering allows database administrators to quickly identify and address sessions that might contribute to resource locking and overall performance issues..."
SELECT
ses.session_id,
ses.login_name,
req.start_time,
req.total_elapsed_time,
req.command,
req.status,
trans.transaction_id,
ses.status,
ses.total_elapsed_time,
ses.last_request_start_time,
ses.last_request_end_time,
ses.login_time,
DATEDIFF(minute, ses.last_request_end_time, GETDATE()) AS InactiveTime
FROM sys.dm_exec_sessions ses
LEFT JOIN sys.dm_exec_requests req ON req.session_id = ses.session_id
LEFT JOIN sys.dm_tran_session_transactions trans ON ses.session_id = trans.session_id
WHERE trans.transaction_id IS NOT NULL
AND DATEDIFF(minute, ses.last_request_end_time, GETDATE()) > 5
and ses.status = 'sleeping'
Enjoy!
Microsoft
