If you are a Managed Hosting customer, this topic doesn't apply to you.
Microsoft provides an open source diagnostic tool called SQL Server Performance Dashboard. The Performance Dashboard reports enable SQL Server Administrators and other users to monitor the health of the SQL Server and diagnose issues.
Although it relies upon Reporting Services definition files (.rdl), Reporting Services does not need to be installed to use the Performance Dashboard Reports. This custom report set relies on SQL Server's dynamic management views (DMVs) as a data source, which provides the wealth of data that the dynamic management views contain but insulates the user from the views and underlying structures.
Accessing and using this storehouse of performance information does not require additional sources, data capture, or tracing. Other obvious benefits of using these prefabricated views include the constant availability of the information that they contain and their inexpensive nature (from the tandem perspective of collection and querying) as a source of server monitoring.
The Performance Dashboard provides roughly one hour of system history. It is an effective tool for performing immediate forensics when a problem is reported, but it cannot be used for long-term monitoring.
Download the tool from Microsoft
To learn more about getting the most out of the Performance Dashboard, see Related Resources.
The Performance Dashboard is no longer available in SQL 2008. Microsoft has released a new tool called the Data Collector.