In a previous post, I detailed how to determine if you have connection leaks. In this post I will show you how to determine which query executions are likely the source of the connection leaks.

The approach we will use for finding the connection leaks is to look for sessions that have been in a sleeping status for the longest period of time. We will then check what the last statement executed on the connection was.  This will tell us the query that was likely the cause of the connection leak.

In the query below you will need to replace the @host_name and @host_process_id with appropriate parameters.  We will use a value for @host_name which is the name of the server we identified generating the leaks.  We will use the @host_process_id from the process id that we identified associated with the leak in perfmon.

DECLARE @host_name nvarchar(128)
DECLARE @host_process_id int
SET @host_name = 'WIN-7S1S3U3QOBV'
SET @host_process_id = 6056

SELECT top 100 s.last_request_end_time,
datediff(ss, s.last_request_end_time, getdate()) seconds_since_last_request_ended,
s.session_id, c.most_recent_sql_handle,
COALESCE(st.text, N'Statement no longer in cache') statement_text
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c ON
s.session_id = c.session_id
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) st
WHERE s.is_user_process=1
AND s.host_name = @host_name AND s.status = 'sleeping'
ORDER BY last_request_end_time

Here is a screenshot showing the perfmon chart.  In my earlier post I showed how you get to this chart.  You can see the host_process_id and host_name here:

Plugging these values in gives us a pretty clear picture of what is likely leaking:

You can see that there were two sessions idle for 189 seconds.  These were both related to a call to SELECT @@VERSION.  In the real world you would probably see a lot more sessions listed here and the leaked sessions would probably have higher values for seconds_since_last_request_ended.  There is a chance the sql text for the sql_handle that leaked is no longer available; however, you can try running this query throughout different times of day and it might surface the query.

Now that we have the query we can look through our code and see if we can locate where we aren’t cleaning up connections properly.

 

SQLGrease and query timeouts

See how SQLGrease can quickly get you to the bottom of a query timeout error.

See how you can get query level wait events without Query Store:

Free you say?  How can that be?