Working with the SQL Browser

Owned by Copy Space for Confluence
May 20, 2015
3 min read

The SQL Browser provides information about all SQLs that were executed on the system. SQLs having the same signature are aggregated. The aggregation of the SQLs uses concrete parameters if the SQL parameter capturing is enabled. More information about SQL parameter capturing can be found here. The SQL browser should be used to get a quick overview of the SQL queries that are executed in the system under diagnosis.

Auto-refresh imposes very high system load

Activating auto refresh for the SQL browser view creates a very high system load on the CMR. This is due to the fact that the CMR needs to aggregate each and every SQL in memory and sent them to the client. Thus the analyst should uses manual refreshing.

Adjustable time resolution

For the better overview, all the times in aggregated view like this one are presented in milliseconds, without decimal places. However, you can also display the decimal places if the higher resolution is needed. Use the view menu in top-right corner and select desired value in the "Decimal places" group.

Usage examples

Finding slow queries

Queries having slow average response times often indicate one of the following:

  • (unnecessary) complicated SQL query structure
  • integration of aggregation functions that prevent the database from using meaningful indexes
  • Missing indexes on the database

To find these types of SQL queries simply sort the queries by average time in the SQL browser.

Finding queries that are invoked very often

In most cases it is not necessary for the application to invoke one query multiple times. Often application-side or ORM caching is not activated or configured badly. Often the developer is not even aware that certain SQLs are invoked very often and that caching might be meaningful.

To find these queries simply sort the queries by count in the SQL browser.

Finding queries that take most time

In each application there are queries that take some time. Usually for search dialogs where the number of input is very high, not all queries can be optimized. Usually these queries are no performance problem as they are called only seldom.

A very meaningful information is the total duration of one SQL query. This "duration" can be used to see which query created overall the most load on the system.

Detailed analysis

Usually the SQL browser provides a birds eye view on the SQLs of the system. For a deeper analysis, the analyst usually uses invocation sequences to see which database queries were executed within with call.

Current limitations

  • The SQL browser currently does not implement the search functionality (this also holds true for the predefined searches)

Loading data from a specific time-range

This view enables a user to specify a time-range that the displayed data should be in. By clicking to the icon the time-range selection opens, where you can select the wanted time-range for displayed data. Note that the entered duration will be relative to the selected until date. Selecting duration of one hour to the current time, will display the data that was collected in the last hour.