As promised back in December ‘08, after having spoken briefly at SQL teach here in Montreal thanks to MVP Paul Neilsen (SQLserverBible.com), here is an introduction to one of our favorite new Management Studio Enhancements: Activity Monitor. To use AM, right click on a SQL Server 2005/8 instance in the object explorer and take a look, it’ll inspire you to take care of what’s bogging down your database engine and motivate you for some good ol’ Spring cleaning.
First, let me start off by mentioning that even if you do not have SQL 2008 instances, it is worth it to install SQL 2008 Management Studio (SSMS is the client tool acronym to manage your SQL Server infrastructure) just to have this critical problem resolution feature known as Activity Monitor. SSMS has backwards compatibility built-in; thus you can take advantage of the dynamic management views already existing in SQL 2005 while connecting from SQL 2008 SSMS’ Activity Monitor (AM). To view the equivalent information used in the AM before, for example, I was loading information directly from the dynamic management views in Excel sheets to understand what was really going on across the specific servers’ activity. Thankfully, this is now all built into SSMS as AM and provides sortable columns, which enables exceptionally swift pin-pointing of problematic operations.
Ever since the RTM of SQL 2008 was released last summer (unless perhaps, you had beta versions) you can view real-time critical SQL Server performance 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, the application it is running from, the number of logical reads…you get the picture, practically everything you need to fix SQL Server tribulations – even giving the option to right click on a line in Recent Expensive Queries to get down to optimising the offending code right away! There are four panes with graphs for each, plus collapsable details, so you can even view/filter processes, resource waits and disk activity, as well as my favorite 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.
If you cannot update your instances to SQL 2008 for a while, which would not be surprising considering the economic tsunami hitting the world (and now a Pandemic!), then at least you can use the updated client tools to enjoy this eye-opening and cost-effective updated feature – in my opinion a critical step in remediation.
On that note, Happy Easter / Passover to all my readers J
