Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

7 Answers 7

up vote 5 down vote accepted

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.

share|improve this answer
    
OK yes, this is it. Thanks! –  Kyle Hodgson Jun 5 '09 at 13:47

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

share|improve this answer

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.

share|improve this answer
    
Hmm, I may have to try that. –  Kyle Hodgson Jun 7 '09 at 16:37

SP_WHO2 active

displays current active connections.

share|improve this answer

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.

share|improve this answer

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

share|improve this answer
    
Sorry Sankar. top is a UNIX program that is quite handy in that it shows you what programs are running, how much CPU, memory and IO they are consuming in real time. Its very handy. Also, I will check the tool you reference. –  Kyle Hodgson Jun 5 '09 at 13:44

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.