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?
The non-clustered index you have tested is not the best for this query. It can be used for the WHERE clause and for doing an index scan instead of a full table scan but it cannot be used for the GROUP BY.
The best possible index would have to be a partial index (to filter the unwanted rows from the WHERE clause), then have all the columns used in the GROUP BY and then INCLUDE all the other columns used in the SELECT:
CREATE INDEX special_ix
ON dbo.Commissions_Output
( company, location, account,
salesroute, employee, producttype,
item, loadjdate, commissionrate )
INCLUDE
( [Extended Sales Price], [Delivered Qty] )
WHERE
( [Extended Sales Price] <> 0 ) ;
Best Answer
I am not sure if your case is similar, but you can see similar behavior with Distribution Agent SPIDs on subscriber.
When its not applying any changes it looks something similar.
So in this case its normal behavior.
Hundreds of idle sessions, as you can imagine, would not impact CPU. But those sessions might be waking up and doing things. The real question is where are those spids coming from? Are all of them from the same application? If so, it might be time to ask the application support guys why it has so many connections hanging around. Maybe they are not closing connections properly.