I isolated the problem to one line in the query. Keeping in mind that the query is 160 lines long, and I'm including the relevant tables either way, if I disable this line from the SELECT clause:
COALESCE(V.Visits, 0) * COALESCE(ACS.AvgClickCost, GAAC.AvgAdCost, 0.00)
...the run time drops from 63 minutes to five seconds (inlining a CTE has made it slightly faster than the original seven-second query). Including either ACS.AvgClickCost or GAAC.AvgAdCost causes the run time to explode. What makes it especially odd is that these fields come from two subqueries which have, respectively, ten rows and three! They each run in zero seconds when run independently, and with the row counts being so short I would expect the join time to be trivial even using nested loops.
Any guesses as to why this seemingly-harmless calculation would throw off a TVF completely, while it runs very quickly as a stand-alone query?
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?
Best Answer
A little dated, but for anyone ending up here with a similar problem...
I had the same problem. For me it turned out to be parameter sniffing, which at first I didn't understand enough to care about. I added a 'set arithabort on' which fixed the problem but then it came back. Then I read:
http://www.sommarskog.se/query-plan-mysteries.html
It cleared -everything- up. Because I was using Linq to SQL and had limited options to fix the issue, I ended up using a query plan guide (see end of link) to force the query plan I wanted.