Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

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.

Activity Monitor Gives you a great overview of the SQL Instance, just like Rome from Villa Medicci, Bourhgese Gardens

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

Comments

Posted by Wesley Brown on 11 April 2009

I had a colleague walk by my desk and see the activity monitor up and wanted to know what is was. He assumed it was some third party tool when I showed him it was built into 2008 SSMS he was blown away. He didn't even think about it since we are still on 2005.

Knowing little things like how to leverage the tools you have, can make you a very successful DBA in the long run!

Good post,

Wes

Posted by Hugo Shebbeare on 11 April 2009

Thank you Wesley!  I'll update this soon with more references to persons who've already done full blown AM articles - for now, page 10 of Brad's Sure Guide to SQL Server 2008, should help, since it's a free download www.red-gate.com/.../ToolbeltBradsure.htm

As for the colleagues noticing, indeed, even Oracle DBAs will look at us with envy:)  Very motivating tool.

Posted by Russbbass on 2 July 2009

I think this is a nice tool. I would love to be able to customize  it. All DBAs I know have a little toolbox of stuff that would fit nicely into this tool. Being able to customize it with additional monitors and funtionallity would just add more value.

Posted by Anonymous on 21 April 2010

As mentioned in a previous post , between the months of January and September 2008 I was hired to be

Leave a Comment

Please register or log in to leave a comment.