Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 2

up vote 2 down vote accepted

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?

share|improve this answer
    
Excellent answer. The application is a vendor app so I do not know exactly what it is doing. I am fairly confident that implicit transactions are not turned on for the session (basing this on watching SQL Profiler activity). I will look into this morning an follow up. –  Elijah W. Gagne Oct 28 '13 at 23:38
add comment

The status of sleeping isn't indicating it is a long running query but rather the connection established to run that query is still open. As long as that connection is open it will be captured and logged in your table. It looks like it is more of an issue or IIS not closing the connection to the database.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.