Analyze the top SQL & NoSQL statements across all your databases

When it comes to monitoring your database services, often times you’re more interested in understanding the overall database activity across your entire environment, rather than the activity of a specific database. Dynatrace now provides a database view that enables you to do just this. The new Top database statements view also enables you to better analyze end-to-end database activity during specific analysis timeframes.

To access the Top database statements page

1. Select Diagnostic tools from the navigation menu.
2. Click the Top database statements tile.
   

   
   

   Alternatively, you can access the Top database statements page by clicking View top database statements on the Databases page.

   

Analyze database statements

The Top database statements page (see example below) provides a chart that shows the top SQL and NoSQL statements in your environment over time. The chart visualizes a few of the top statements based on execution count. The table beneath the chart shows the Top 100 database statements. You have control over how statements are sorted in this list—whether based on time consumption per statement, statement execution frequency, or error rate. For these reasons, the Top database statements list can be configured to sort based on which statements are executed most frequently or on which statements are most expensive from a resource standpoint.

Use the Add filter control to narrow the list of database statements. Note the Request filter in the example below. Dynatrace will further extend filtering options for database statements over the coming weeks.

Analyze individual SQL statements

To analyze an individual SQL statement

1. Select Diagnostic tools from the navigation menu.
2. Click the Top database statements tile.
3. Select an analysis time frame from the Analyze statements drop list.
4. Select a sorting approach from the Sort by drop list.
5. Optionally, click Add filter to filter the list of statements.
6. Scroll down and select the statement you want to analyze.
   

   
   

   Here’s you’ll find additional detail about the specific SQL statement, including the average Rows returned count and the average Fetches count. Dynatrace also aggregates the inserts, updates, and deletes per table. It does similar aggregation for MongoDB on a per-collection basis. This Details page shows all seen permutations of the selected query (see example below).

   

7. Once you find the SQL statement you want to analyze, select the statement and click the Service Backtrace button. This opens the Service-level backtrace page, which provides detail on which clicks triggered this particular permutation of the SQL statement.
   

   

8. Return to the Top databases statements page and click the Analyze Outliers button to understand the response time spread of the command.

   

9. Return to the Top database statements page and click the View single executions to view the single executions of each command.

   

Understand SQL in context of Service flow

While SQL analysis is available in multiple locations throughout our service-analysis workflow, most significantly, we’ve added it as the primary analysis view for databases in Service flow view. The example below shows a service flow that is already filtered to focus on a particular chain of calls. You can see that whenever the Customer Frontend service calls the JourneyService, 25% of the response time contribution can be attributed to the EasyTravel-Business MongoDB service.

Notice that the default action has changed and now shows View database statements.

To further analyze a database statement

1. Click the  View database statements button (see image above) to see all the database statements that were executed by the selected flow within the analysis time frame. This can be tremendously valuable because you can now understand why a database contributes the amount of time it does.
   

   

2. As a next step, select Sort by total time from the Sort drop list to reveal which statements have the highest overall response time.

   

3. From here, you can continue your analysis in a few different ways:
   • Click the Analyze outliers button (see image above) to understand the spread of response times.

     

   • Click the Details button to understand the evolution of that each individual SQL statement undergoes over time, along with the average Rows returned count.

     

4. As a final step, click the Service backtrace button to see which user clicks led to the slowest executions of this particular MongoDB statement.

   

Using these new database-statement analysis features in conjunction with our existing analysis capabilities provides powerful database-usage focused analysis. We hope you agree and we’d love to hear your feedback on these enhancements.