Milena Petrovic

SQL Server performance monitoring with Data Collector – Part 3 – reading the reports

February 21, 2014 by
In the first part of this article, we described Data Collector, its features and pre-defined collection sets. In the second part, we showed how to set up data collecting. In this final part, we will show the reports available in Data Collector and how to use them for performance troubleshooting

The feature provides three built-in reports, one for each built-in collection set. To open a report:

  1. In Object Explorer | Management, right-click Data Collection
  2. Select Reports and then Management Data Warehouse
  3. Select one of the reports: Server Activity History, Disk Usage Summary, or Query Statistics History

Selecting one of the available data collection reports

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:

  1. In Object Explorer | Management, right-click Data Collection
  2. Select System Data Collection Sets
  3. 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

Reading Data Collector reports - SQL Server Waits 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

A list of top queries on the CPU

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

The Disk Usage Summary report - Data Collector reports

Selecting any database enables drilling down into the reports

Disk usage report for the selected database

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

The Query Statistics History report shows top 10 most expensive queries

Clicking a query in the list opens sub-reports with detailed statistics. Drilling down into query statistics provides query code and execution plan used

Sub-reports with detailed statistics of a clicked query

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

Milena Petrovic

Milena Petrovic

Milena is a SQL Server professional with more than 20 years of experience in IT. She has started with computer programming in high school and continued at University.

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
Milena Petrovic
20,358 Views