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.
|SQL Server Management Studio's Activity Monitor Icon, bottom right|
|Activity Monitor's overview, just like looking South over Rome, from the Villa Borghese|
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 :)