September 4, 2023 in Releases by David Wiseman2 minutes
Running Queries sleeping sessions
A sleeping session refers to one that is currently inactive and awaiting input from the client application. As these sessions are inactive, they are normally not of much interest from a monitoring perspective and are excluded from collection. The exception is when the sleeping session has an open transaction. These sleeping sessions with open transactions can cause blocking. If the session is sleeping for a long time it can also prevent log truncation and cause the transaction log to fill up. DBA Dash only collects sleeping sessions in the running queries snapshots where there is an open transaction.
DBA Dash now captures the last_request_end_time column from the sys.dm_exec_sessions DMV. This can be used to calculate how long the session has been idle (in the sleeping state). Sessions that have been sleeping for a long time with open transactions warrant attention, particularly when they are blocking other queries.
New Idle Time column has been added to Running Queries tab. This shows how long a sleeping session has been inactive. By default this will highlight yellow when the session has been inactive for over 1 second and red if it is inactive for over 10min.
Note: Column is hidden if there are no sleeping sessions with open transactions
Application changes are required to fix problems caused by sleeping sessions with open transactions. The sleeping session can’t be tuned from the DB side as there is no query to tune. DBA Dash reports that last query ran by the session, but tuning this query might not be beneficial if the real problem is the amount of time the session is idle with an open transaction. To fix the issue:
See 2.46.0 release notes for a full list of fixes.