Viewing Query Explorer

The Query Explorer in Foglight is a diagnostic tool that enables database administrators and performance analysts to search, filter, and analyze SQL queries executed within monitored database environments.

It gives you insight and control over SQL query performance so you can maintain a fast, efficient, and healthy database environment.

This feature is applicable only for SQL Server PI and PostgreSQL PI.

The Query Explorer enables you to:

  • View detailed information for each SQL statement.
  • View historical and real-time SQL queries executed on your database.
  • Identify expensive queries in terms of CPU, I/O, wait time, or execution frequency.
  • Identify long-running or resource-intensive queries.
  • Troubleshoot performance issues.
  • Audit query activity by user, application, or client machine.

To access the Query Explorer dashboard, click Query Explorer on the Welcome page, or navigate to Databases > Query Explorer in the left navigation pane.

Analyzing Query Performance

The Query Explorer in Foglight enables users to search and analyze SQL queries executed in monitored database environments. To view the details of a specific query, enter the SQL text in the search field and select the desired domain and instance. Once a query is selected from the results, the Statement Details panel displays information such as SQL text, execution statistics, and performance metrics.

To analyze a query:

  1. From the Domain dropdown list, select the required domain (SQL Server or PostgreSQL).
  2. From the Instance dropdown list, select the required instance.
  3. Under the Search by Query Text tab, in the SQL text field, enter at least 15 characters of SQL text. Apply optional filters (Databases, Users, Client Machines, Programs).
    OR
    Under the Search by Tops tab, select the number of top queries to be displayed (for example, Top 10) from the Show dropdown list.
  4. Click Search to retrieve matching queries. If you have selected Search by Tops, then the system retrieves the top queries that consumed the most resources during the selected time frame.
  5. Click on a query to view its detailed performance metrics in the Statement Details pane.

Filtering Queries

Query Explorer allows users to filter queries using SQL text and contextual attributes. This helps narrow down the scope of analysis and focus on specific queries that may be impacting performance.

You can filter queries according to the following categories:

  • SQL Text - Enter the SQL statement (minimum 15 characters).
  • Databases - Click Databases and select the database name from the dropdown list for which you want to view queries.
  • Users - Click Users and select the user who executed the query.
  • Client Machines - Click Client Machines and select the originating client machine.
  • Programs - Click Programs and select the application or program name.

Click Search to apply filters or Reset to clear all inputs.

You can also view queries according to the time frame you select. To do so, click on the time frame in the top right corner. Select the Start date, End date, and the desired time, and then click Apply. All queries in the selected timeframe are displayed.

Statement Details

This pane provides details about the performance and resource usage of a specific SQL statement. Once a query is selected, the Statement Details pane displays:

  • SQL Text - Displays the actual SQL code executed. You can Copy or Download the SQL text. To view more details about the query, click:
    • Investigate for detailed analysis view for the query.
    • Analyze Plan to generate and review the execution plan for the SQL statement.
    • Compare to compare the current SQL statement or its execution plan with another version.
  • Resource Breakdown - A visual bar chart that shows how the total execution time is distributed across different resource types (for example, CPU, Memory, I/O, and Waits). This helps identify what kind of resource the query is consuming most.
  • Workload Metrics - A table that displays various resource consumption metrics, providing an in-depth view of instance activity. Each resource includes its default metrics. Selecting each dimension in the performance tree along with a specific resource affects the data displayed at each level. For example, selecting the Lock resource will cause the Instance view dimension to show only lock-related data. The SQL Statements dimension will display only the statements that were experiencing locks, and the DB Users dimension will present only the users affected by locks. This filtering applies across all dimensions and resources.
    • Active Time - Displays the sum of all active waits and processing time represents the total activity within the current time interval.
    • Executions - Displays the number of times statements were executed that were active during the current interval.

This information helps identify inefficient queries and optimize database performance.