Related pages:

The Queries window displays the SQL statements and stored procedures that consume the most time in the database. You can compare the query weights to other metrics such as SQL wait times to determine SQL that requires tuning. 

Access the Database Queries Window

To access the Database Queries window:

  1. To view database queries, click the name of the database.
  2. Click the Queries tab.

Database Queries Window Features

Database agent reports all the queries with execution time > 1 second. It captures queries with execution time <= 1 second only if the query is executed at the sampling time.

From the Database Queries window, you can:

  • View the top N queries. These are the queries that consumed the greatest amount of database time to complete. 
  • Choose to display the top 5, 10, 100 or 200 queries.
  • Click the name of a column to sort the Query list using that key.

  • Click Filter by Wait States to choose wait states to filter the Query list. The filtered list only displays queries that caused the selected wait states. 

    For Couchbase database, click Filter by Phases to choose the phases. This filters all the queries based on the selected phase.

  • Check the Group Similar box to group together queries with the same syntax. Queries that share the same syntax but different 'IN' clause parameters are still grouped together. Query grouping is case-insensitive. Double-click a query group to dive into the Group Details for more information about that query group. Only queries reported by newer agents are grouped; the queries reported by older agents may not appear upon grouping. 
  • Search for a specific query, by entering text in the search bar that may appear in the Query. This is useful if you found a slow query in the Slow Database calls window of AppDynamics Application monitoring.
  • Double-click a query or select a query and click View Query Details to dive into the Query Details for more detailed information about that specific query. If you see a message saying that query details are unavailable, you may need to update your permissions or extend your query retention period.

  • Click the down arrow next to the database Collector name at the top of the page to choose to view the database queries of a different database Collector by either selecting the database Collector from the list or by searching for the database Collector by entering text in the search bar and then clicking the refresh icon to show only database Collectors that meet that search criteria. 

  • Click Actions to:
    • Export the data on this window in a .csv formatted file that is automatically downloaded to your specified downloads directory.
    • Rename the selected query so that it is easier to identify. Note that the query name is associated with the query itself. Thus, when a query’s retention period expires, both the query and the query name disappear.

From the Database Queries window, you can view:

  • Query Id: This is the unique ID assigned to the query internally by the database. 
  • Query: The text of the query, custom query name, or, in the case of a stored procedure invocation, the name of the stored procedure. If the user does not have the ANY permission to view the stored procedure name, the definition of the stored procedure is shown instead of the stored procedure name.

For SQL Server, Azure, Oracle, PostgreSQL and DB2 the metrics Number of Executions, Average Response TIme and Elapsed Time are inferred from the query index. When you are viewing the queries with Group Similar checked, the statistics reflect only the queries that have captured query statistics. Queries that do not have query statistics are excluded from the statistic values.

For databases such as MySQL and MongoDB which do not have a query index, no data will appear in the Number of Executions and Average Response Time columns. However, Elapsed Time can be inferred for MySQL and MongoDB databases using the wait state index. Elapsed Time can also be inferred using the wait state index when query statistics for a query run on an SQL Server, Azure, Oracle, PostgreSQL or DB2 database are otherwise not available. When query statistics are not available for a query run on an SQL Server, Azure, Oracle, PostgreSQL or DB2 database, the Number of Executions and Average Response Time statistics cannot be reported and are marked with a hyphen (-). This means that the database did not provide statistics for this query in the selected time range.

  • Elapsed Time: The total time consumed by all executions of this query. Query statistics such as Number of Executions, Average Response Time and Elapsed time are available for databases supporting the query index, such as SQL Server, Azure, Oracle, PostgreSQL and DB2. For databases where the query index is not available, the elapsed time is inferred from the wait state index or phases.
    By default, the query sampling frequency is once every second. If a query takes less than one second and happens to occur in the time between sampling instances, its elapsed time is reported as 0 seconds.
    You can configure the sampling frequency by adding the -Ddbagent.sampling.interval= <value> property for Database Agent. For information about adding the system properties, see Database Agent Configuration Properties.
  • Number of Sessions: (supported for Oracle, MSSQL, PostgreSQL, MySQL, Sybase, DB2, MongoDB, and Couchbase) The number of sessions that are running the query.
  • Number of Executions: (supported for SQL Server, Azure, Oracle, DB2, PostgreSQL) The number of times the query ran during the specified time period. If you are monitoring Greenplum, the number of executions appears as 0. 
  • Average Response Time (hh:mm:ss): (supported for SQL Server, Azure, Oracle, DB2, PostgreSQL) The average time required during the specified time period to respond to the query.
  • Weight (%): The percentage of the total time consumed by the query.

Queries

Once you have identified the statements that are consuming the most resources, you can dig down deeper for details that can help you tune the SQL statement. For in-depth query details, click a SQL statement and then click View Query Details. See Database Query Details Window.