Introduction to Query Insights (4371018)

The Foglight Query Insights feature is a tool for database administrators and developers to get a birds-eye view of the most time-consuming and frequently executed queries of an organization. This feature allows users to identify queries that are causing significant performance issues and prioritize them based on their impact.

Query Insights ranks and sorts "Queries" by "Impact," which is calculated based on the percentage of statement workload in comparison to the total instance workload.

Filtering by impact ensures that 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.

Filtering is a crucial feature for analyzing data, and Foglight Query Insights allows filtering to be applied to any of the collected metrics and the query text. This feature enables users to narrow down the scope of their analysis and pinpoint specific queries that are causing performance issues.

With Foglight Query Insights users are just one click away from gaining more insights at the query level by using Foglight Performance Investigator (PI). PI provides a comprehensive view of query performance by offering additional metrics such as query response time, CPU utilization, disk activity, and more. With PI, users can identify specific areas where query performance can be improved.

To start using it, look for “Query Insights” under “Databases” on the left navigation panel in Foglight

Technical Details

It includes the relevant topology and the new Angular dashboard.

In addition, each supported database cartridge is responsible to collect the data and upload it to the Query Insight topology.

This is done by an internal rule defined at the cartridge level that is scheduled to run every 15 min (non-configurable).

For collecting statements, the rule will look for the statements collected during the last 15 min and report the top n (default 10. configurable) that have the highest impact.

The impact is calculated a bit differently for each cartridge, but in general, it is the sum of resources of the statements divided by the total number of resources of the instance for that specific time. The usual measure for the amount of resources is the active_time metric of the statement or elapsed_time.

With Query Insight you can review statements over time by setting the timeframe selector.

Data is saved in the Profiler topology and is aggregated based on the following retention:

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

Query Insights is supported for the following database cartridges:

• SQL Server
• Azure SQL Database
• Oracle
• DB2
• SAP/ASE (Sybase)
• MySQL
• PostgreSQL
• Redshift
• MongoDB

Foglight version supported is 6.3 and above.

Queries are reported on database instances that are being monitored in Foglight.

For SQL Server and Oracle there also needs to be a minimum workload associated with the database instance. minimum workload is defined as avg of 1.5 sec/s activity in a 15min timeframe which means at least 1-2 statement is actively running throughout the entire 15 min.

This number can be configured using the appropriate registry variables

• Dbwc_Common_Oracle_MinimalWorkloadHighActivityThreshold
• Dbwc_Common_SQL_Server_MinimalWorkloadHighActivityThreshold
   

Messages similar to the following may appear in the FMS log when SQL Server or Oracle instances are in a minimal workload state

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

Users can reduce the minimal work load threshold by reducing the the value of Dbwc_Common_Oracle_MinimalWorkloadHighActivityThreshold or Dbwc_Common_SQL_Server_MinimalWorkloadHighActivityThreshold registry variable from the default of 1.5 to 0.1

How-to

Filter

Filter relation is “and”

Can be used to focus on specific statements by setting filters to the respective metrics (Impact, Elapsed time, Response time, or Executions), Domain, or part of the statement text.

Select all filters you need and then press Apply to get the filtered statements.

Exclude instances from reporting to Query Insights

Set the relevant registry variable as listed here:

Change the Top x

Set the relevant registry variable as listed here:

Updating the value of any of these will affect all agents of that domain. It can be set to a specific database instance (agent) by setting an appropriate scope.

Known Issues

1. The retention period for Query Insight does not necessarily match the retention of the related cartridges. when selecting time in the past, it is possible to see a statement in Query insight that is no longer available in the respective workload dashboard
2. When drilling down from Query Insight to the related agent dashboard using the “Investigate” button, sometime the statement will appear but the tree will not reflect the selected statement (SQL Server with PI and Oracle with PI).
3. When drilling down from Query Insight to the related agent dashboard using the “Investigate” button, sometimes the focus is on the instance level and not on the related statement (SQL Server with no PI, Oracle with no PI)
4. Metric data for a specific statement in Query Insight may be different than those reported on the individual agent when compared over time larger than 15 min. This is because the statement may not be reported at the top of each 15 min and hence not all of its information is available. The more accurate data is that on the agent dashboards.