D. I. Y. is more F. U. N.

Question: How do I list the active sessions and the elapsed times they are running?

Answer: The CONNECTION_PROPERTY() function returns a great deal of information about each connection (session). The LoginTime property can be used to calculate the elapsed time; here's the value for the current connection:

SELECT CONNECTION_PROPERTY ( 'LoginTime' );

'2013-05-11 04:49:12.499'

There are many connection properties, and there can be many connections, resulting in hundreds, maybe thousands of values.

The sa_conn_properties() procedure makes it easier to process that "great deal of information":

SELECT Number                                    AS connection_number,
       CONNECTION_PROPERTY ( 'Name',   Number )  AS connection_name,
       CONNECTION_PROPERTY ( 'UserID', Number )  AS user_name,
       Value                                     AS login_time 
  FROM sa_conn_properties() 
 WHERE PropName = 'LoginTime'
 ORDER BY Number;

connection_number,connection_name,user_name,login_time
14,'adhoc-queries','k.delacruz',2013-05-11 04:49:12.499
15,'RRLoadTest','DBA',2013-05-11 04:54:24.016
16,'app','h.barbosa',2013-05-11 04:54:24.232
17,'app','g.mikhailov',2013-05-11 04:54:24.334
18,'app','u.wouters',2013-05-11 04:54:24.448
19,'app','e.reid',2013-05-11 04:54:24.554
20,'app','y.gustavsson',2013-05-11 04:54:24.663
21,'app','n.simpson',2013-05-11 04:54:24.776
22,'app','f.thomson',2013-05-11 04:54:24.890
23,'app','x.wang',2013-05-11 04:54:25.100
24,'app','i.miller',2013-05-11 04:54:25.218
25,'app','c.ryan',2013-05-11 04:54:25.325

You can continue to Do-It-Yourself in ISQL...

• call DATEDIFF() to turn LoginTime to an elapsed time (milliseconds?) value,
  

• study up on other CONNECTION_PROPERTY() values, what they mean, which ones are useful (maybe more useful than LoginTime) and which ones don't work at all, and maybe
  

• look at the DB_PROPERTY() and server-level PROPERTY() functions as well, then
  

• figure out how to gather the values and save them and summarize them and calculate rates and changes and rankings, and finally
  

• build up your query(ies) until you get the information you want.
  

In other words, you can build your own database performance monitor.

Or you can just use Foxhound.

In Foxhound the "Time Connected" colum is based on the LoginTime property, and you can click on the column title to sort the long-lasting connections to the top.

In THIS particular screenshot, however, the list of connections has been sorted on "CPU %" to sort the most active sessions to the top:

DIY "Internal Use Software" is more fun, of course . . .

... just don't tell Accounting about it unless you count your own time as "free" :)

---