Query Performance Analysis
Beacon’s Query Performance Analysis module identifies, ranks, and explains how SQL statements behave across monitored database instances. It transforms raw execution statistics into prioritized, understandable insights — showing which queries are slow, which run most often, which consume the most resources, and where optimization effort will yield the greatest return.
This module answers the question: “Which queries are hurting my database, and why?”
Capabilities
Section titled “Capabilities”1. Slow Query Detection
Section titled “1. Slow Query Detection”Beacon continuously scans execution activity to surface queries that exceed expected performance boundaries.
| Signal | Description |
|---|---|
| Execution Time | Total and average time spent executing a query. |
| Threshold Breaches | Queries exceeding configurable duration limits. |
| Frequency of Slowness | How often a query is slow, not just whether it was slow once. |
| Worst-Case Duration | The slowest single recorded execution. |
| Slow Query Ranking | Ordering by impact, not just raw duration. |
Operational value: Highlights the queries most responsible for degraded responsiveness, prioritizing by real-world impact.
2. Query Execution Statistics
Section titled “2. Query Execution Statistics”For every tracked query, Beacon records a detailed statistical profile.
| Signal | Description |
|---|---|
| Total Executions | How many times the query has run. |
| Total Time Consumed | Cumulative time spent executing the query. |
| Average Execution Time | Mean duration across all executions. |
| Rows Returned / Affected | Volume of data processed per execution. |
| Cache Hit Ratio | Proportion of reads served from memory. |
| Disk Reads | Data read from storage rather than cache. |
| Temporary File Usage | Spill-to-disk activity from sorts or joins. |
Operational value: Distinguishes between a query that is slow occasionally and one that is consistently expensive at scale.
3. Query Normalization & Grouping
Section titled “3. Query Normalization & Grouping”Beacon groups similar queries together by normalizing literal values, allowing analysis by query pattern rather than individual statements.
| Signal | Description |
|---|---|
| Normalized Query | The query template with literals abstracted. |
| Execution Variants | All distinct concrete forms of the same pattern. |
| Aggregated Metrics | Combined statistics across all variants. |
| Pattern Frequency | How often the normalized pattern appears. |
Operational value: Prevents thousands of near-identical queries from fragmenting the analysis, revealing the true cost of a query pattern.
4. Query Ranking & Prioritization
Section titled “4. Query Ranking & Prioritization”Beacon ranks queries by multiple dimensions so teams can focus on what matters most.
| Ranking Dimension | Description |
|---|---|
| By Total Time | Queries consuming the most cumulative database time. |
| By Average Duration | Queries that are slow on every execution. |
| By Execution Frequency | Queries that run most often, even if individually fast. |
| By Resource Intensity | Queries consuming the most CPU, memory, or I/O. |
| By Row Volume | Queries processing the largest data volumes. |
Operational value: A fast query run a million times can cost more than a slow query run occasionally. Beacon makes that visible.
5. Query Performance History
Section titled “5. Query Performance History”Beacon stores query performance over time, enabling longitudinal analysis.
| Signal | Description |
|---|---|
| Performance Timeline | How a query’s duration has changed over days or weeks. |
| Regression Detection | Sudden or gradual degradation in query performance. |
| Improvement Tracking | Measurable impact of optimization changes. |
| Execution Frequency Trends | Whether a query is being called more or less over time. |
| Historical Baselines | Expected performance ranges for each query. |
Operational value: Detects regressions caused by data growth, schema changes, or application updates — and confirms whether optimizations actually helped.
6. Query Detail View
Section titled “6. Query Detail View”Each tracked query has a dedicated detail view combining all available context.
| Section | Contents |
|---|---|
| Summary | Normalized query text and key aggregate metrics. |
| Performance Chart | Execution duration and frequency over time. |
| Execution Breakdown | Per-execution statistics and outliers. |
| Plan Access | Direct link to the execution plan visualization. |
| Related Objects | Tables, indexes, and schemas referenced. |
| Recommendations | Suggested optimizations based on observed behavior. |
Operational value: Consolidates everything known about a query in one place for efficient diagnosis.
7. Workload Analysis
Section titled “7. Workload Analysis”Beyond individual queries, Beacon analyzes the database workload as a whole.
| Signal | Description |
|---|---|
| Read vs. Write Mix | Balance of query types across the workload. |
| Workload Distribution | Which query patterns dominate total time. |
| Peak Activity Windows | When workload intensity is highest. |
| Application Attribution | Which sources generate the most load. |
| Latency Distribution | Spread of query response times across the workload. |
Operational value: Reveals systemic patterns — such as an application feature driving disproportionate load — that individual query views can miss.
8. Optimization Recommendations
Section titled “8. Optimization Recommendations”Beacon generates contextual recommendations based on observed query behavior.
| Recommendation Type | Trigger |
|---|---|
| Index Suggestion | Queries repeatedly scanning large tables without index support. |
| Query Rewrite Hint | Patterns suggesting inefficient joins, filters, or ordering. |
| Schema Consideration | Recurring issues pointing to structural design concerns. |
| Caching Opportunity | Frequently executed, stable queries suitable for caching. |
| Batch Opportunity | Many similar small queries that could be consolidated. |
Operational value: Converts analysis into direction, reducing the expertise required to act on findings.
Analysis Views
Section titled “Analysis Views”Beacon presents query analysis through several lenses:
Top Queries View
Section titled “Top Queries View”Ranked lists of queries by impact dimension — the primary entry point for performance triage.
Query Timeline View
Section titled “Query Timeline View”Historical performance of a selected query, with regression markers.
Workload Overview
Section titled “Workload Overview”Aggregate view of the entire query workload and its composition.
Comparison View
Section titled “Comparison View”Compare query performance across instances or time periods.
Configuration Options
Section titled “Configuration Options”| Option | Purpose |
|---|---|
| Slow Query Threshold | Duration above which a query is flagged as slow. |
| Ranking Weights | Adjust emphasis on duration, frequency, or resource use. |
| Retention Period | How long query history is retained. |
| Tracked Databases | Which instances contribute to query analysis. |
| Sampling Rate | Balance between detail and collection overhead. |
Operational Scenarios
Section titled “Operational Scenarios”Scenario 1 — Identifying the top offender An application becomes sluggish. Beacon’s Top Queries view shows a single normalized query consuming 40% of total database time, despite running only a few hundred times per hour. The detail view reveals repeated full-table scans, and the recommendation suggests a missing index.
Scenario 2 — Detecting a regression A routine deployment introduces a subtle query change. Beacon’s regression detection flags a steady increase in average duration for an affected query pattern, allowing the team to catch the issue before users report it.
Scenario 3 — Validating an optimization After adding an index, the team checks the query timeline. Average duration drops sharply and remains stable, confirming the fix worked and quantifying the improvement.
Summary
Section titled “Summary”Beacon’s Query Performance Analysis module turns raw execution data into prioritized, contextual understanding. By normalizing, ranking, and tracking queries over time — and pairing analysis with recommendations — it enables teams to find and fix the queries that matter most.