The feature provides three built-in reports, one for each built-in collection set. To open a report:
- In Object Explorer | Management, right-click Data Collection
- Select Reports and then Management Data Warehouse
- Select one of the reports: Server Activity History, Disk Usage Summary, or Query Statistics History
The recently used reports are also listed under the Custom reports option
If these built-in reports don’t provide enough details, or you need a report for a custom data collection set, you can use the Custom reports option and create a report of your own [1]
The Management Data Warehouse database stores all records necessary to create reports. To make sure the latest captured records are also included in the reports:
- In Object Explorer | Management, right-click Data Collection
- Select System Data Collection Sets
- Right click a specific collection set and select Collect and Upload Now
The Server Activity History report
The Server Activity History report shows the activity of the monitored SQL Server instance in a specified time range. It also shows how much resources were used
The time range bar enables you to select the start and duration of the period (15 minutes, 1, 4, 12, or 24 hours) that will be shown in the report
%CPU – shows how much processor capacity was used by the SQL Server instance and by the operating system
“A continually high rate of CPU usage may indicate the need to upgrade the CPU or add multiple processors. Alternatively, a high CPU usage rate may indicate a poorly tuned or designed application. Optimizing the application can lower CPU utilization.” [2]
Memory usage – shows average physical memory used by the SQL Server instance and by the operating system in KB
“By default, SQL Server changes its memory requirements dynamically, on the basis of available system resources. If SQL Server needs more memory, it queries the operating system to determine whether free physical memory is available and uses the available memory. If SQL Server does not need the memory currently allocated to it, it releases the memory to the operating system.”[3]
Disk I/O Usage – shows input and output calls (I/O) used by the SQL Server instance and by the operating system to perform read and write operations on the hard disk
Network Usage – shows traffic over the network in MB/sec
SQL Server Waits Statistics – show the top wait reasons and their wait time. Clicking the graph provides drill-down information with detailed statistics
Clicking on a wait category in the list shows further details. For example, clicking the CPU shows a list of top queries on the CPU. Besides the CPU, queries can be ranked by duration, total I/O, physical reads, and logical writes
SQL Server Activity – shows the top activities per second or in percentage – batch requests, logouts, connections, compilations, recompilations, and transactions. Clicking on the graph provides more detailed information
The Disk Usage Summary report
The Disk Usage Summary report shows disk space used by each database on the monitored SQL Server instance, growth trend, and average daily growth
Selecting any database enables drilling down into the reports
The Query Statistics History report
The Query Statistics History report shows top 10 most expensive queries. These are the queries that use most of CPU resources in the monitored time range. The same as with the Server Activity History report, the queries can be ranked by CPU used, duration, total I/O, physical reads, and logical writes
Clicking a query in the list opens sub-reports with detailed statistics. Drilling down into query statistics provides query code and execution plan used
As the overall performance can be downgraded by a number frequently executed expensive queries, start the tuning with the execution plan analysis
Data Collector reports can be exported to PDF, Excel, and Word. To do that, right-click the graph and select the Export option
The SQL Server instance performance can be affected by data collecting. To reduce this effect, combine the similar queries into the same collection items and sets. Also, keep in mind that data collections more frequent than 5 seconds add significant overhead
The Data Collector feature has built-in reports for the pre-defined collection sets that provide the most common performance metrics. The reports are comprehensive, easy to read, provide good details and trends for a specified period. The reports for additional collected metrics have to be created as custom. However, the result filtering is limited and the feature is not available in all SQL Server editions
She has been working with SQL Server since 2005 and has experience with SQL 2000 through SQL 2014.
Her favorite SQL Server topics are SQL Server disaster recovery, auditing, and performance monitoring.
View all posts by Milena "Millie" Petrovic
- Using custom reports to improve performance reporting in SQL Server 2014 – running and modifying the reports - September 12, 2014
- Using custom reports to improve performance reporting in SQL Server 2014 – the basics - September 8, 2014
- Performance Dashboard Reports in SQL Server 2014 - July 29, 2014