Sql-server – Why would a long running query remain sleeping and with null wait_info

performancequery-performancesql server

The following query was run on SQL Server 2008 R2 and took over 1 hour to run:

select log_id, type, instance_id from ts_cache_log where log_id > 146400 order by log_id

It produced 0 rows returned. I know this because I restored the database to the point in time that the query started running and re-ran the query. When I did this, the query ran very fast (less than 1 second).

While this query was running for over an hour, sp_WhoIsActive was kicked off every 5 minutes via a SQL Agent job and logged the details to a table. The below image, which you should able to download and view zoomed in, shows what was running during that time.

sp_WhoIsActive Details

The long running query is highlighted in green. The wait_info column is null and the sleep_status is always sleeping.

Any idea why this query took so long to run and/or any thoughts on what I can do to troubleshoot it further?

Best Answer

I'll agree with @Ian for the most part. Let me expand, though.

It is probably IIS keeping the connection open, but it is by design due to connection pooling, which is almost always used by default these days.

The other point is that the query was not running for over an hour. Pay very close attention to the awesome documentation that Adam includes in sp_WhoIsActive. Run it with @help =1. On the time attribute you are using to measure run time, Adam writes this:

For an active request, time the query has been running

For a sleeping session, time since the last batch completed

And for the sql_text attribute, Adam writes:

Shows the SQL text for active requests or the last statement executed for sleeping sessions, if available in either case.

What's actually happening here is the query completes very quickly and the connection is released back to the pool (in 'sleeping' state) to be picked up when it is demanded again. The results in your table reflect that the connection that last ran the query is simply going unused for a long period of time. So, nothing to worry about, everything is functioning as designed.

However, you may want to modify your Agent job to run sp_WhoIsActive with @show_sleeping_spids = 1, which will only show sleeping spids if they have an active transaction, so that you don't see these pooled connections anymore.

UPDATE:

In looking at the screenshot again I see you actually do have an open transaction maintained on that spid. It doesn't really affect my answer, other than the last paragraph, but you do need to find out why that transaction is being held. Do you have implicit transactions turned on? Are you failing to commit an explicit transaction in the same batch?

Related Question