While working on the just released version of SQL Server 2008, I noticed the new activity monitor that you can access from within SSMS. I then checked the install from Feb. CTP and it was there as well – must have missed it among other wonderful feature sets. Here is a screenshot of the activity monitor – you can access it from the main menu and that is also shown in this image:
This is a much more enhanced activity monitor than what used to exist before. Using this utility, one can quickly diagnose the performance data without much overhead. Permissions wise, you need to have the “View Server State” permission to be able to see this. It has 5 sections:
a) Overview: The graphical presentation.
b) Processes: All processes are listed here – one can filter down based on the values, can right click and get more details about the processes, kill the process if needed and also trace that process in SQL Server Profiler. How nice! No waste of time – everything is integrated.
c) Resource Waits: All the resource waits (CPU, Latch, Buffer I/O etc.) are listed here and one can easily filter down based on the values if one wants.
d) Data File I/O: All the data files for a given instance are listed here and one can again filter down based on the values for different columns.
e) Recent Expensive Queries: This is very handy. All the recent expensive queries are listed – one can again filter down based on the values, sort the data or right click and check the execution plan for that query or can even chose to edit the query text in which case it will open up in another SSMS query window and you can check and tune the query.