In SQL Server 2000 Enterprise Manager, the Task Pad view was really the only native-reporting interface available and it was limited to database and object-related information. In SQL Server 2005, Microsoft integrates Management Studio and Reporting Services to provide snapshots of real-time performance and database metrics.
If you installed SQL Server 2005 Reporting Services sometime after the relational engine on the same server, you may have been surprised to see the Reports button transition from gray to color. When this button is enabled, you can select reports from the drop-down list and view native reports directly in Management Studio. These reports are context sensitive to the Management Studio interface selected, and they are generated in real time with snapshots of both real-time and historical data. Much of this data is generated from Dynamic Management Views, another new feature in SQL Server 2005 used to get a real-time view of the performance by querying the relational engine and SQL Server 2005 components (Notification Services, Service Broker, Common Language Runtime, etc.).
Database reports
The screen shot to the right outlines the reports available in context of a database environment, which include:
- Disk usage
- Backup and restore
- All transactions
- All blocking transactions
- Top transactions by age
- Top transactions by blocked transactions count
- Top transactions by locks count
- Resource locking statistics by objects
- Object execution statistics
- Database consistency history
- Index usage statistics
- Schema changes history
- User statistics
These lucky 13, commonly used reports, offer a detail and summary view of the data in both graphical and textual formats, and you can drill down from the summary information to the detailed data. These reports are a good first step when you troubleshoot items like blocking and locking in a real-time manner. They also provide a superb tool set for DBAs who want a snapshot of core operations, such as disk usage, backups and schema changes.
Keep in mind that these reports are only the tip of the iceberg; Management Studio has 45 reports that ship by default with the relational engine. At a high level, reports are based on one of the following seven categories accessible via Management Studio:
- Top-level node of the Management Studio interface
- Individual database
- Login
- Service Broker
- Management
- Notification Services
- SQL Server Agent
Examples of three key reports from SQL Server 2005 Management Studio, below, serve as a general baseline from a content and formatting perspective for all 45 reports.
| SQL Server 2005 Management Studio – Sample Reports |
| ID |
Overview |
Screen Shot |
| 1 |
Server Dashboard report from the top-level node of the Management Studio interface outlining SQL Server configuration details (Edition, Server Name, etc.) and Activity Details (CPU, IO, etc.) |
 |
| 2 |
One of many top queries (CPU, IO, Memory, Queries, etc.) with the average and total figures in addition to the T-SQL syntax. |
 |
| 3 |
Report across all databases outlining the DDL (Data Definition Language) and DML (Data Manipulation Language) changes in the SQL Server instance. |
 |
The native reports in Reporting Services for Management Studio are valuable and offer access to data that's not available via the native tool set. I encourage you to explore Management Studio and these reports to see how they can help answer your questions about everything from locking and blocking to schema changes and backup events. Good luck!
About the author: Jeremy Kadlec is the principal database engineer at Edgewood Solutions, a technology services company delivering professional services and products for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server users groups and nationally at SQL PASS. Kadlec is the SearchSQLServer.com Performance Tuning expert. Ask your performance tuning questions here.
More Information from SearchSQLServer.com
Fast Guide: SQL Server Reporting Services
Book excerpt: Building basic reports in SQL Server 2005
Ask the Experts: Ask our BI guru Eric Brown for help working with SQL Server reports
