Foglight Query Insights Overview (4371018)

Summary

Foglight Query Insights is a powerful feature that provides visibility into the most resource-intensive and frequently executed SQL statements across monitored database instances. Designed for database administrators and developers, Query Insights helps identify and prioritize performance bottlenecks at the query level.

Feature Description

Query Insights ranks SQL statements by Impact, calculated as the proportion of the statement’s resource usage relative to the total workload of its instance. This approach ensures that even infrequently executed queries with a high resource footprint are not overlooked.

Key capabilities include:

• Impact-Based Ranking: Queries are sorted by their overall impact on performance.

• Advanced Filtering: Filter by metrics such as Impact, Elapsed Time, Executions, or by specific keywords in the query text.

• Integrated PI Drill-Down: Access full diagnostics using Performance Investigator (PI) with one click.

• Historical Analysis: View statement trends over time using the timeframe selector.

• Top-N View: Displays up to 250 queries per view with server-side filtering.

Accessing Query Insights

To open Query Insights:

1. Navigate to the Databases section in the left-hand menu.

2. Click on Query Insights.

Technical Details

Architecture

Query Insights leverages a new cartridge called SQL Profiler, which includes:

• New Angular-based dashboards

• A dedicated topology model for profiling data

Each database cartridge collects and sends query data to the SQL Profiler topology every 15 minutes via internal rules (non-configurable).

Top-N Query Collection

• By default, the top 10 highest-impact queries from the last 15 minutes are collected.

• Impact is typically calculated using metrics such as active_time or elapsed_time.

• The number of collected queries can be modified using domain-specific registry variables (see below).

Retention and Filtering

• Data is retained in the Profiler topology and aggregated according to configured retention policies.

• Maximum of 250 statements are displayed at once.

• Filters refresh the dataset from the server with each use.

Filtering logic: All selected filters are applied using an AND condition.

The Query Insight dashboard can show up to 250 statements at once, but you can use filters to show more of what you want to focus on. Filters are server-side. each time you apply a filter, it will query the topology again.

• Pre-Requisites

  • For SQL Server and Oracle, a minimum workload must be detected:

    • Default: 1.5 seconds/second of activity averaged over 15 minutes.

    • Ensures that at least 1–2 statements are consistently active.

  • Workload thresholds can be adjusted using these registry variables:

    • Dbwc_Common_SQL_Server_MinimalWorkloadHighActivityThreshold

    • Dbwc_Common_Oracle_MinimalWorkloadHighActivityThreshold

  Example Log Message:

  VERBOSE [TimeDriven_Worker-3] Oracle Push Top SQL PI HOSTNAME-INSTANCE - Instance [HOSTNAME-INSTANCE] is in Minimal Workload state 

  To reduce the minimum workload requirement, decrease the above values (e.g., from 1.5 to 0.1).

Configuration Options

Exclude Instances from Reporting

Use the following registry variables to exclude specific instances from Query Insights:

Set the scope appropriately to affect a specific agent or all agents under the domain.

Modify Top-N Count

To change the number of top statements collected (default is 10), use the following registry variables:

Known Issues

• Retention Discrepancy: Query Insight retention may differ from agent dashboards, causing queries to appear in Query Insights but not in the workload dashboards.

• Drill-Down Behavior:

  • With PI: Selected queries may not be correctly highlighted.

  • Without PI: Focus may default to the instance level rather than the query.

• Data Accuracy Over Time: Statement metrics may differ between Query Insight and the agent dashboards if the query did not appear in the top list every 15 minutes. Agent dashboards provide the most accurate long-term view.
  Reference ID: FOG-5455