In SQL Server, there is a good set of preloaded reports to help you monitor the Instance.  These reports can be found under the Standard Reports and can be reached via context menu.  To find them, right click your Instance in Object Explorer from within SSMS.  Then navigate the context menu – much like the menus shown here.

You can see from the image that there is a decent amount of good reports available to choose.

When you run one of these reports, it is like a report that you would run from SSRS.  However, SSRS is not required to be installed in order to run these reports.

Occasionally, you may encounter an error when trying to run these reports.  The error that you may encounter is as follows:

The file ‘Microsoft.ReportViewer.WinForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’ cannot be opened.

This error message seems to pop up commonly after an upgrade to SQL Server is performed (CU or SP).  Sometimes, it can crop up without an upgrade having been recently performed.

In the event that you encounter this particular error, you should try installing BIDS.  Though it is not necessarily a pre-requisite – it is a good idea to have it installed anyway and it also resolves this error message.

Another method that has worked for me to resolve this error is to install Microsoft Report Viewer 2008 SP1.

Additionally, besides looking into these Standard reports and hopefully helping you circumvent the aforementioned error, there is another recommendation.  In SQL 2005, there is a download for the Performance Dashboard Reports.  And in SQL 2008 there is a new feature called Management Data Warehouse.

You can download the Performance Reports from here.

You can see how to configure the Management Data Warehouse here.

Oh, and if you want to retrofit SQL 2008 with the 2005 Performance Dashboard, you will have to tweak it a bit.  You can read up on that here.