Waaaay back in December ‘08, after having spoken briefly at SQL teach here in Montreal thanks to MVP Paul Nielsen (SQLserverBible.com), on this subject, the following is a re-introduction to one of our favorite Management Studio features: Activity Monitor.
To use AM, right click on a SQL Server 2005/8/12 instance in the object explorer and take a look, or click on the farthest right icon in the SQL Server Management Studio toolbar (image below). It will perhaps inspire you to take care of what’s bogging down your database engine and motivate you for some good ol’ Spring cleaning.

The latest version of Activity Monitor helps you see the processes, users, and applications that are currently using the database in the sortable/filterable Processes pane, and, of course, whether they are blocking transactions. Even if you do not have SQL 2008/12 instances, it is worth it to install SQL Server Management Studio complete client tools (SSMS, now in 2012 version RTM, is the client tool acronym to manage your SQL Server infrastructure) just to have this critical problem resolution feature known as Activity Monitor.

Activity Monitor has built-in backward compatibility; thus, you can take advantage of the dynamic management views already existing since SQL Server 2005 while connecting from a SQL Server 2008/12 Management Studio Activity Monitor. Previously, to view the equivalent information in the Activity Monitor, was, for example, loading information directly from the dynamic management views, or in SQL Server 2000 exec sp_who2, in Excel sheets to understand what was really going on across the specific servers’ activity. Thankfully, this is now all built into SQL Server Management Studio because Activity Monitor provides sortable columns, which enable exceptionally swift pinpointing of problematic operations by database, through information on execution time/frequency, reads/writes, and CPU usage. SQL 2012's version of Activity Monitor has improved column resizing, but is pretty much the same as the 2008 version.

Since the initial version of SQL Server 2008 was released, you have been able to view real-time critical SQL Server process details and even sort by the worst-performing queries—whether it be by the number of times the offending code is run per minute, which login is running it, which database it is in, which application it is running from, etc. In Activity Monitor, practically everything you need to fix SQL Server issues is available, including the option to right click on a line in Recent Expensive Queries to optimize the problematic code right away.

There are four panes with graphs for each, plus collapsible details, so you can view/filter processes, resource waits, disk activity, and Recent Expensive Queries. By hovering over any of the columns within the respective information panes, one can also see which dynamic management view was used to provide the systems management information; for further investigation and perhaps even set up alerts for when thresholds are met. Even if you cannot update your instances to SQL Server 2008 for a while, then you can use the updated client tools in SQL Server Management Studio to benefit from this updated feature—in my opinion, a critical step in remediation.

Happy spring to all my readers, this summer is going to be a scorcher in North America, I can feel it :)

http://www.sqlservercentral.com/blogs/hugo/2012/03/18/activity-monitor-in-sql-server-2012-an-improved-embedded-optimisation-gizmo-for-the-thrifty-dba/

Activity Monitor in SQL Server 2012: an Improved Embedded Optimisation Gizmo for the Thrifty DBA

By Hugo Shebbeare, 2012/03/18