Drilldown on the workload of this SQL Server instance. What work is the database system producing in a given time? What resources are consumed in producing this work?

How to open the Workload Analysis drilldown

From the Spotlight Client

  1. Select the connection from the left Connections pane.
  2. Click Monitor | Workload Analysis from the ribbon.
    Worload Analysis drilldown for SQL Server

1. Select the resource

On the Workload Analysis drilldown, select the Resource to investigate.

Resource Description
CPU This resource measures the rate of CPU time needed to parse, compile, and run each statement in the query. Select this resource to inquire into any strain the SQL Server instance is putting upon the CPU.
Duration Select this resource to analyze the time taken to parse, compile and run a given query.
Reads This resource measures the number of pages read per second: from the data cache and from disk. A high number of page reads may indicate that the SQL Server is running inefficient queries.

SQL Server 2012 and above differentiates physical reads from logical reads.
Writes This resource measures the number of physical disk writes performed per second. A high number of page writes may indicate that the SQL Server is running inefficient queries.

2. Select a Workload item

The basic workload menu can be expanded to select a specific database, application or other workload.

Workload Description
All Workload Summarize all workload of the selected resource.
Database Analyze which database is contributing most to the workload of the selected resource.
Application Analyze which application is contributing most to the workload of the selected resource.
User Analyze which user is contributing most to the workload of the selected resource.
Host Analyze which host is contributing most to the workload of the selected resource.
Statement Analyze which SQL statement is contributing most to the workload of the selected resource. Note that only 200 SQL Statements are shown at a time; click Show More to show the next 1000 statements.

Possible actions you can take once the resource and workload item are selected:

  • Click on the SQL statement to show the Overview, SQL and Plan tabs.
  • Right click on the SQL statement to open the SQL statement in an external editor.
  • Right click on the SQL statement to show it in the SQL Server | Wait Events drilldown.
  • Use Export all the statements to save all SQL statements to a file (default filename SQLStatementList.sql). If the exporting tree list is large, Spotlight will save to multiple files; each output file no greater than 10Mb.
  • Export data from statement nodes to an .xlsx file for further analysis. Click Workload Analysis export. Data includes: STATEMENT, Avg CPU (ms), Avg Duration (ms), CPU (ms), Duration (ms), %Total Duration, Execution Count, % Total execution Count, Max CPU (ms), Min CPU (ms), Min Duration (ms).

3. The grid

Show a breakdown of the selected workload for the selected resource.

The first column of the grid

This first column of the grid gives a breakdown of the selected workload. For example, if the Database workload is selected, then a breakdown of databases is given.

The top consumer of the resource is (by default) top of the list. Click on the column headers to rearrange the sort order of the grid.

CPU / Duration Resource

Where the selected Resource is CPU or Duration, the grid shows the time (ms) spent by the selected workload over the selected time frame on this resource.

Reads / Writes Resource

Where the selected Resource is Reads or Writes, the grid shows the time (ms) spent by the selected workload over the selected time frame on this resource.

The % Total column gives the proportion of time spent on the selected workload compared to other workloads of the same type.

Execution Count

The Execution Count column shows the number of executions run against the selected workload over the selected time frame on this resource.

The % Total column gives the proportion of executions on the selected workload compared to other workloads of the same type.

Charts

Time Series chart

This chart shows the resource usage attributed to the selected workload over the given time frame. Drilldown on the All Workload menu to select a workload item to compare with other workloads of the same type.

For example: Expand the All Workload menu to select a specific database. The chart shows the usage of the selected resource by the selected database over the selected time frame. It also shows (by comparison) the total usage of the selected resource by databases over the selected time frame.

Pie chart

This chart shows the proportion of resource usage by the selected workload compared to other workloads of the same type.

For example: Expand the All Workload menu to select a specific database. The pie represents all database usage of the selected resource. The selected segment of the pie shows the proportion of that usage by the selected database.

Select a SQL Statement from the All Workload menu

Additional information is available when a SQL Statement is selected from the All Workload menu.

Overview

You can show the workload for the selected item:

  • CPU (ms/s)
  • Duration (ms/s)
  • % Total Duration
  • Avg Duration (ms/s)
  • Execution Count
  • % Total Execution Count
  • Logical Reads (reads/s)
  • % Total Logical Reads
  • Physical Reads (reads/s)
  • % Total Physical Reads
  • Writes (writes/s)
  • % Total Writes

Plan

The Optimizer Plan Visualization control provides an interactive and intuitive way to view your plan. See Plan.

SQL

Show the entire SQL Statement.