I am not thrilled with the Activity Monitor that ships with SQL Server 2005, though I suppose it is trying to be the same thing.
A snippet of the query, username, database name, from table name, CPU time, IO time, memory allocated, maybe locks obtained...
Set the refresh speed for 2-3 seconds and let it run?
I love the profiler - very useful for performance tuning; but something that worked like top would be awesome for troubleshooting sometimes.
Edit: command line tools that didn't require sql studio might be nice.