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.
.png) |
| SQL Server Management Studio's Activity Monitor Icon, bottom right |
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.
 |
| 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 🙂
