Server Activity History Report

The Server Activity History report contain resource consumption and server activity data for the server and for the instance of SQL Server. The data provided in this report is collected by the Server Activity collection set, which uses the Generic T-SQL Query collector type and the Performance Counters collector type.

You can access the Server Activity History report from Object Explorer. To view the report, expand the Management folder, right-click Data Collection, point to Reports, point to Management Data Warehouse, and then click Server Activity History. For more information, see How to: View a Collection Set Report.

Selecting Data to Include in the Report

The report includes server activity for the entire data collection period. There are two ways you can navigate through the data collection timeline to select a segment of the data to view.

Timeline Control and Navigation Buttons

Use the timeline control and navigation buttons to move through the timeline or to select a date range. The arrow buttons provide left and right scrolling so you can move backward or forward through the timeline. By default, the arrows move through the timeline in 4-hour increments. Using the magnifier buttons, you can expand or contract this time increment to one of the following values: 15 minutes, 1 hour, 4 hours, 12 hours, or 24 hours. The currently selected time range is indicated by the highlighted portion of the timeline and is displayed in the text below the timeline. These values as well as the data in the report are updated whenever you click the timeline or use the navigation buttons.

Calendar Button

Use the calendar button to specify the start date, start time, and duration of the data that you want to report on.

Server Activity History Report

The Server Activity History report shows the initial view of the server activity for an instance of SQL Server and the host operating system.

The following table describes the graphs that plot SQL Server and system activity in the report and the detailed subreports that can be accessed through the graphs.

Graph

Report description

%CPU

These subreports are accessed by clicking any point on the SQL Server or System graph lines in the %CPU graph.

SQL Server

The Query Statistics History report provides a graph of the most expensive queries in the instance of SQL Server. A table below the graph lists the queries and includes statistical data for each query. You can click on a query to obtain additional details.

System

The System CPU Usage report provides a graph of %CPU time per processor and statistical data for each process in tabular format.

Memory Usage

These subreports are accessed by clicking any point on the SQL Server or System graph lines in the Memory Usage graph.

SQL Server

The SQL Server Memory Usage report provides graphs for SQL Server process memory usage, memory counters, internal memory consumption by type, and a table that provides data about the average memory usage by component type.

System

The System Memory Usage report provides graphs for memory usage, cache and page hit ratios, and a table that provides information about the working set and private bytes for each process.

Disk I/O Usage

These subreports are accessed by clicking any point on the SQL Server or System graph lines in the Disk I/O Usage graph.

SQL Server

The SQL Server Disk I/O Usage report provides graphs for disk response time and disk transfer rate. An additional table provides virtual file statistics by disk, database, and file.

System

The System Disk Usage report provides graphs for disk response time, average disk queue length, disk transfer rate, and a table that provides information about I/O writes and reads for each process.

Network Usage

No additional reports are available.

SQL Server Waits

The SQL Server Waits graph displays waits encountered by threads that executed by wait category. You can access a detailed report by clicking any segment in the graph. In addition to providing graphical SQL Server wait statistics over a narrower time frame, this report provides information about wait categories in tabular format. For each category, such as CPU and its subcategories, the table shows the number of waits, the wait time, and percentage of total wait time.

SQL Server Activity

Different aspects of SQL Server activity can be accessed from the SQL Server Activity graph. The reports that you can obtain by clicking a point on the SQL Compilations/sec graph line are as follows:

  • Connections and sessions

  • Requests

  • Plan cache hit ratio

  • tempdb characteristics