Lesson Learned #476:Identifying Sleeping Sessions with Open Transactions in Azure SQL Database

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

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!

Leave a Reply Cancel reply