“top” style activity monitor for SQL Server?

Question

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.

Answer 1

Yeah, Jason Massie (Twitter - Blog) wrote an excellent one that you can customize here:

Current query activity with one-second refresh (Archive.org link)

It uses the dynamic management views in SQL Server 2005 & newer to show what's going on in your server.

Answer 2

The 'Rolls Royce' answer would be install the SQL Server 2005 Performance Dashboard, it's pretty comprehensive and works a treat:

Making the Most Out of the SQL Server 2005 Performance Dashboard

Answer 3

Probably not helpful to your situation right now, but the Activity Monitor in SQL 2008 Management Console is miles ahead of the SQL 2005 console.

Great if you have access to a the 2008 installation media (it will connect to the 2005 server just fine), not so much if you don't.

Answer 4

SP_WHO2 active

displays current active connections.

Answer 5

If your DB is a 2005 instance, then try from 2008 Activity Monitor... I agree that 2008 AM is miles ahead and much more flexible for your posible needs.

Answer 6

Kyle,

I am NOT clear of what you are asking here? Do you want to setup something that runs every 2-3 seconds and give info like CPU, IO etc...?

Try this. Adam Machanic has nifty script that does more than what you have asked and its been of great help for me.

http://sqlblog.com/files/folders/beta/entry14380.aspx

Answer 7

Idera SQL Check (Free) or Idera SQL Doctor ($500) are decent performance monitoring tools.