Redgate Monitor can collect data on individual executions of long running queries. This information can be found on the server overview's query executions tab. The threshold for a query to be monitored is 5 seconds and over.

The tab contains a copy of the history tab's activity graph allowing you to compare spikes in CPU, memory usage, throughput and waits with the long running queries that were executing at that time.

Redgate Monitor supports query execution monitoring for SQL Server instances on both Windows and Linux environments, as well as Amazon RDS SQL Server Standard and Enterprise editions; we do not support query execution monitoring on the Web or Express editions of Amazon RDS SQL Server, nor on SQL Server 2008 R2. 

Enabling query execution monitoring will require 100MB of disk space on the monitored server. 

Configuring query execution monitoring

Query execution sampling can be resource intensive. Because of this, the feature is disabled by default on all instances; it can be enabled for specific instances using the Additional monitoring settings page located in Configuration > Advanced.

Query executions content

By default, the Query Executions table displays all queries with a duration over five seconds that occurred within the last two hours. Each row includes the following details:

• Captured Event Name
  The name of the Extended Event that captured the query execution.

• Event Result
  Indicates the outcome of the query execution, such as completed, error, or aborted.

• Duration Information
  Includes the start and end timestamps of the query, as well as the total duration in seconds.

• Execution Details
  Shows the full query text, the user who executed the query, and the database context in which it ran.

• Logical Reads and Writes
  The number of logical page reads and writes the query performed.

• CPU Time (seconds)
  The total CPU time consumed by the query, measured in seconds (converted from microseconds).

The data can be filtered on any of these properties, and individual columns can be hidden when not needed.

The raw parameter information captured in the query text column may include sensitive information or personal data. It's possible to redact query plan parameters for all users (incl. administrators), by setting the SQLMONITOR_AlwaysRedactQueryPlanParameters  environment variable on the Website to true.

Activity graph and time controls

The activity graph shows CPU and memory usage, as well as throughput and the amount of time spent on waits. Like on the history tab, the graph contains controls for changing the time-window the page represents.

This time-window is bounded to be between fifteen minutes and two hours and by default will end at the current time. The end time can be altered using the date picker. The time window can be narrowed by manually shrinking or growing the selector, which allows you to focus on specific times.

Altering the window or end time using these controls automatically alters the content of the query executions table.

Sidebar

Clicking on any of the table rows will open a sidebar. This sidebar contains the same content as the table row in a more readable format. This includes a window showing the full query text, which is often not fully visible in the table itself.

Query execution monitoring and extended events 

Enabling query execution monitoring will result in an extended event session being created on the target server. On deleting or suspension of query execution monitoring, Redgate Monitor will automatically delete these extended event sessions.

The extended event files generated during monitoring will remain and will require manual removal.