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.
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?