SQL Server 2005 Performance Dashboard Reports

Performing in-depth diagnostic work using Microsoft SQL Server can be a difficult process, especially for novices. For many years, the only tools available to DBAs and developers were the system tables in SQL Server and the Windows Performance Monitor. Performance Monitor is a useful tool, but its interface is arcane and hard to understand. You must have many years of experience to understand which Performance Monitor objects and counters to use, how to read them, and what values represent trouble.

Consequently, it has always been rather difficult and intimidating for new DBAs to try to diagnose memory problems or quickly deduce why a SQL Server instance has slowed down dramatically. However, Microsoft has made significant headway in SQL Server 2005 to ease these challenges through the new SQL Server 2005 Performance Dashboard Reports. The Performance Dashboard Reports were developed by Keith Elmore, a Product Support Services (PSS) escalation engineer. This easy-to-deploy set of SQL Server Reporting Services (SSRS) reports reveals the most critical performance bottlenecks on your network.

The Performance Dashboard Reports leverage SQL Server Management Studio SP2's new Custom Reports functionality. The reports pull data exclusively from Dynamic Management Views (DMVs) and Catalog Views, without touching your OS's performance counters. One interesting aspect of the reports is that although they're written using SSRS, you don't actually need to have SSRS installed on your system for the reports to work properly. Because the Performance Dashboard Reports rely on DMVs, they work only with SQL Server 2005 SP2.

After you've connected to a SQL Server instance, you can open the Performance Dashboard Reports by right-clicking the server name in the Object Explorer pane, choosing the Reports option, and selecting Performance_Dashboard_Main from the list of options. Your troubleshooting methodology should lead you to first check for the general problem, and then search for details on the specific nature of the problem. For example, you might want to drill down into the I/O details of a problem by clicking IO Statistics on the Performance Dashboard page, as Web Figure 1 (http://www.sqlmag.com, InstantDoc ID 96169) shows. The Performance Dashboard page can help you quickly identify whether a problem is a CPU or memory problem. From the Performance Dashboard page, you can also explore more detailed performance problems, such as logical and physical reads and writes, wait states, and I/O problems.

Note that the Performance Dashboard Reports are point-in-time only, meaning they don't store historical data about your server's performance over time. The reports are also lightweight and incur only a small amount of overhead while diagnosing performance problems. The reports don't incur any overhead when they aren't running.

One of the great things about the Performance Dashboard Reports is that you can add reports to it. Aaron Bertrand, a SQL Server MVP, posted a report on SQLblog.com that shows blocking (http://sqlblog.com/blogs/aaron_bertrand/archive/2006/12/19/448.aspx). And of course, we want to hear your feedback on the Tool Time discussion forum at http://sqlforums.windowsitpro.com/web/forum/categories.aspx?catid=169&entercat=y.