Russ Thomas – SQL Judo

The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded

SSMS Day 16: Monitoring / Troubleshooting with SSMS

31 Days of SSMS: Table of Contents

What does SSMS offer in the way of keeping an eye on the health of your server?  This is one area where 3rd party tools are without question superior – but SSMS isn’t as much a black box as it used to be.

SSMS offers a few basic ways to monitor your servers.  Frankly you don’t really NEED much more than a good understanding of the TSQL language, DMVs, and the query window – but that’s a topic for another post.  From an SSMS only standpoint you can leverage reports, access log information, and you can keep an eye on the activity monitor.

SSMS Standard Reports

There are actually an impressive list of reports built into SSMS.  Right click a database, select ‘Reports’, then ‘Standard Reports’ and take a look.  There are server specific reports, database specific reports, and a variety of reports at other levels such as management and security objects as well.


Probably one of the more popular ones is the database level report that identifies fragmentation levels of indexes.


Looking for a rundown on each report individually?  Unfortunately these reports aren’t documented very well on MSDN, or if they are they are very well hidden.  A few years ago Buck Woody did about as good as job as can still be found on the web.  Googling around reveals a few other blogs with various information but you’ll probably end up getting more mileage from just exploring on your own.  They really are pretty self explanatory.

SSMS Custom Reports

If you find that none of these meet a specific need, Microsoft does do an OK job of documenting how you go about adding your own custom reports.

The link to the custom report page I just gave you includes a section indicating that there are custom samples you can download and also that the standard reports can be downloaded and modified but as of this writing the link is broken.  I’ve asked about it on MSDN but haven’t heard anything yet.  If you can still download the standard reports and modify them yourself I’d like to know how and where.

Any custom reports you write are simple RDLCs based on SSRS.  If you have experience in report writing or SSRS you should find that building your own custom reports is a fairly trivial matter.

Error Logs

You can access SQL Server error logs as well as the Agent Log and underlying OS system logs within the SSMS UI.  Look for it under the SQL Server Agent node within the Object Explorer.  You can also find the exact same UI under the Error Log node under Management in Object Explorer.  (one opens with agent log as default, the other with sql error log as default).

These logs contain a whole host of notes, warnings, and errors related to each of the log types.  You also have a variety of options with regards to log retention, when logs are rolled over etc.  Let books online be your guide for configuring these how you like.

The Activity Monitor

SQL Server 2008 introduced the Activity Monitor (as we see it today) – you could also access an Activity Monitor in 2005 under the management node in the object explorer.  Bring up the monitor by hitting Ctrt+Alt+A (or via the toolbar or menus).

The Activity Monitor is a lightweight graphical activity monitor.  While it may not be good for deep dive analysis and correlation.  It actually isn’t that bad of a tool for getting a quick glance at the status of your server.



The Overview portion provides a typical CPU Time window.  Knowing how busy your CPUs are is great first triage step in any initial troubleshooting effort.

The level of waiting tasks may or may not be helpful – you need to know what is normal before using this to jump to any other conclusions.

Database IO can be a useful window.  How much disk activity is your server generating.  Again however it’s really only truly useful if you already have a good idea of what is normal.

Finally – batch requests per second.  This last window is an interesting one.  Batch requests per second don’t tell you anything about how healthy or non healthy your server currently is.  It just lets you know how “busy” it is.  But, since not all batches are created equal – of the four windows this one especially DEPENDS on what is normal for your box.

One time I was asked to monitor a server that was “acting funny”.  I knew that typical batch requests per second were in the couple hundred range.  On this particular day it had leapt to 3500.  Digging I discovered an infinite loop had found it’s way into production.  This was only possible however because I knew that 3500 was NOT normal.


This window is the one I use most when just looking for a quick glance at what is happening on a server (at least on a server where I haven’t gotten a chance to install sp_whoisactive yet).

This window shows all currently connected hosts, their status, any waits they are experiencing, and a variety of other useful metrics and points of information.  It’s one of the few windows other than CPU where a quick glance can cause me to immediately have concerns.

Resource Waits, Data File I/O

These two areas are more statistical in nature.  They don’t tell you much unless – like I keep saying – you already know what is normal.

Recent Expensive Queries

This window simply gives an idea of what queries issued against your server are consistently ranked as more “expensive” than other queries.  What identifies a query as expensive?  Well, that is in the eye of the beholder.  You can sort by writes or number of executions, the total cost in terms of CPU or a variety of other columns.  The real Easter Egg of this window is shown below.


This is probably my favorite feature of the Activity Monitor.  You can right click on any of the “expensive” queries and get a query plan.  You can also open the query in another window and edit it.  This is a great feature if you identify an “expensive” query that you’d like to try to improve.

The thing to keep in mind with expensive queries is that in any server some queries are always going to be more expensive than others.  So, don’t find yourself trying to get this list down to zero.  The only way to do that is turn off the server.

Most of us are no longer lucky enough to be monitoring a single server.  So, tomorrow we’ll take a look at what SSMS offers you in the way of monitoring multiple servers at once.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: