Raees Aamir

5 March 2025

• Redgate Monitor
• Performance monitoring and optimization

Query Executions in Redgate Monitor

Intermittent database problems are much easier to resolve when DBAs can pinpoint exactly when they occur and under what conditions. With Query Executions, Redgate Monitor captures execution details for individual queries, helping DBAs identify the precise circumstances that cause resource contention and performance problems.

What are Query Executions?

The new query executions feature in Redgate Monitor provides granular performance data for individual query executions. It collects this data using SQL Server Extended Events, a built-in event-tracing system that can capture in-depth performance metrics for individual query executions, without significant overhead.

With Query Executions enabled, Redgate Monitor captures the extended event data for any query executions over five seconds in duration. It provides users with detailed metrics including the query text, query start and end times, and logical reads and writes. You can view this data on the “Query executions” tab of the Server Overview page and use the interactive activity graph to correlate individual query executions with spikes in CPU or memory:

Why are Query Executions needed?

Redgate Monitor comes with a host of query tuning and tracking capabilities that help DBAs and developers to identify long running and costly queries, get recommendations and guidance on how to optimize them.

For example, the Top queries table on each Server overview page provides details of any expensive queries that ran over the period you’re investigating. It provides aggregated execution statistics across all executions of semantically identical queries that occurred within the selected time. Over time, this helps DBAs identify and tune queries that are generally ‘high cost’. DBAs can even choose to track significant queries so they can always review execution data for them and track their performance over time.

However, the most difficult performance problems to diagnose are intermittent and only occur under a particular set of conditions. For example, imagine that a query runs 50 times and the average duration reported by Top Queries is 72 seconds. This might mask the fact that on 49/50 of those executions, the query completed in 60 seconds, but on one occasion, coinciding with CPU saturation, the query took 660 seconds to complete.

The Query Executions feature fills this gap by allowing DBAs to correlate spikes in resource usage, or occurrences of blocking, with execution details for the individual queries that ran around at the time these occurred. These details are displayed in the individual query executions table. For rpc_completed events, the query text will reveal the exact parameters used to execute the query, which can be very helpful in cases where the performance of a query changes dramatically with different parameter values. Please note that query parameter values are available only for rpc_completed events and not for sp_statement_completed, sql_statement_completed or sql_batch_completed events.

Enabling Query Executions

On each SQL Server instance where Query Executions are enabled, Redgate Monitor will need to set up the Extended Event sessions to collect the required data to track individual query executions. As a result, the Redgate Monitor team decided to make individual query executions opt-in so that users maintain full control over the servers and instances for which this XE data is collected.

To collect Query Executions data on an instance, go into additional monitoring settings and select the server(s) for which the feature should be enabled.

Conclusions

DBAs often struggle to unravel the exact circumstances or chain of events that lead to performance problems. With the new query executions feature, Redgate Monitor provides more granular performance data for individual query executions, allowing individual queries to be directly correlated with performance spikes.

This deeper visibility helps teams diagnose not just which high-cost queries were running over the selected period, but which instances of a query caused slowdowns at specific times.