Viewing Query Insights

Query Insights is an innovative analytical feature of Foglight for Databases.

You can use it to identify queries that are causing significant performance issues and prioritize their tuning based on their impact on the database.

With the ability to view all time-consuming and frequently executed queries across all monitored database agents in one place, you can gain a deeper understanding of the performance of your application and be able to help make data-driven decisions based on the information collected.

Query Insights ranks and sorts queries by impact, which is calculated based on the percentage of statement workload compared to the total instance workload.

It also filters queries by impact, ensuring statements are not ignored because they are run on a less powerful machine or executed fewer times. If they have a significant impact on the database instance they run on, they will be considered.

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

Analyzing Query Performance

To view the details of any query, click on the required query. Details such as name, query ID, SQL text, executions, and elapsed time are displayed.

In the list, each query’s impact on the target is highlighted in blue. To view more details of any target, click on the icon, and then click Target Overview. The Overview page appears and displays a database overview, memory, process, storage, resource utilization, and average I/O activity.

To investigate the performance of any query, click on the icon, and then click Investigate. This will direct you to view more details about the individual query.

Filtering Queries

Query Insights allows filtering to collected metrics or query text. This allows you to obtain a much more granular view of the data. You can then narrow down the scope of the analysis and pinpoint specific queries causing performance problems from the results that have been obtained.

This page displays the Top 50 Queries. To view more queries on the page, click the dropdown next to the Display field. Select Top 100 Queries or Top 250 Queries as required. 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.

You can filter queries according to the following categories:

  • Domain- Click Domain and select the domain from the dropdown list for which you want to view queries. Query Insights supports all Foglight database platforms except Cassandra. For more details about the list of supported platforms, refer to Supported Databases.

  • Impact- Click Impact and enter a minimum, maximum, or range limit, and then click Apply. This indicates the percent of statement workload from the total instance workload within the selected time frame.

  • Response Time- Click Response Time and enter a minimum, maximum, or range limit, and then click Apply.

  • Elapsed Time- It indicates the total time consumed for executing the Statement within the chosen time frame. Click More and select Elapsed Time. Enter a minimum, maximum, or range limit, and then click Apply.

  • Executions- It indicates the number of statement executions within the selected time frame. Click More and select Executions. Enter a minimum, maximum, or range limit, and then click Apply.

Click Apply to apply the selected filters or Reset to clear the filters.