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. 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

Query executions table

By default, the query executions table displays information on all queries with a duration over five seconds that occurred within the last two hours. Each row of the table includes:

• The captured event name
• Duration information (start and end times, the duration in seconds)
• Execution information (the text of the query, as well as the user that executed it and which database it was executed against)
• The number of logical reads and writes that occurred.

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.