Analyze database services
Dynatrace monitors the response time, failure rate, throughput, and more of every database statement executed by your applications.
To analyze performance of a database
- In the Dynatrace menu, select Databases.
- Select the database you want to analyze. The database service's overview page is then displayed (see example below).
The infographics on the database service overview page provide insight into various aspects of database performance, including SQL queries or procedures, SQL modifications, SQL transactions, any detected problems or availability issues, hotspots, and more. The exact list of included tiles depends on the database technology you're monitoring. Each tile contains Response time, Failure rate, and Throughput metrics.
To analyze the individual statements that contribute to these types of statements, select View database statements.
By default, the length of database statements is limited to 4KB (KiloBytes). This setting isn't configurable. Database statements that exceed this limit are trimmed to 4KB.
All database statements executed during the selected timeframe are listed in the Database statements section at the bottom of the Details page (see example below). By default, database statements are sorted based on median response time. The list can be sorted by total time, response time, or slowest 10%, and you can filter it on statement, database name, vendor, process, or hostname.
Select any statement to view the full details of that statement. For each statement, Dynatrace shows the number of Executions/min, Total executions, Total time, Response time median and slowest 10%** of executions, and Failure rate.
Database service backtrace
To access backtrace analysis for a certain statement
- In the table of database statements, find the statement you want to analyze.
- Select
> Service backtrace.
The Backtrace page provides details on the clicks that triggered the selected permutation of the SQL statement, and it allows you to see which chain of services and which code leads to a particular statement.
By selecting a specific application in the backtrace, you can find all user actions from this application that trigger the database statement you're analyzing.
In the example above, you can see that the ~1.2k database requests originated from only about 668 user actions, and there are exactly 3 different user actions that trigger this statement. You can also see the chain of services that trigger this statement. By selecting it, we can see that the 668 user actions led to ~1.15k requests on easyTravel Customer Frontend
. To investigate further increments of the number of requests in the chain, select any of the subsequent statements in the service call chain.
The lower section of the service backtrace adapts to the new selection (easyTravel Customer Frontend
) and you now see that all these user actions call two requests on easyTravel Customer Frontend
, /orange-booking-payment.jsf
, and /orange-booking-finish.jsf
. If some of these requests have failed, you can analyze them in the corresponding tab. Here we're more interested in the code that leads to the database calls, so we select the Stacktrace tab to view the executed code in context.
Response time distribution
To understand the response time distribution of the command, select Analyze outliers on the Details page. The Response time distribution page shows the number of requests that fell within various response time ranges. Select any column in the chart to analyze the specific requests that fell within that specific response time range. This enables you to see if certain statements are called frequently and contribute relatively much to the response time.
Analyze performance degradation
The real power of Dynatrace database monitoring reveals itself when it comes to analyzing performance degradation.
You can immediately see which statements are responsible for a detected slowdown—they're highlighted in the list of statements at the bottom of the page. In the example below, the detected response time degradation was caused by two statements: Aggregations in BookingCollection
and Aggregations in JourneyCollection
.
To see which service executed a specific statement that led to a database slowdown, select the statement's backtrace ( > Service backtrace).
This example shows that the service that executed the statement that caused the slowdown is called easyTravel-Business
.
Analyze database errors
Each tile on the database service overview page features a Failure rate graph (1).
Select the Failure rate graph (1) to open the failures tab on the Details page.
The failed database statements table at the bottom of the page immediately shows which statements failed along with their failure rates.
Select View details of failures to understand the root cause of the failures.
Dynatrace determines the causes of high failure rate giving you the information to resolve database issues and eliminate future errors. To further analyze the issue, select Distributed traces or Backtrace.
On the database service overview page, the Current hotspots section (2) offers a quick view of the problem, highlighting the statement with the highest failure rate. By selecting a statement in this section (2) you can directly view the failures tab on its Details page.
Service flow for SQL analysis
Service flow visualizes the sequence of service calls that are triggered by each service request in your environment. With Service flow you see the flow of service calls, including database-service calls, from the perspective of a single service, request, or their filtered subset.
To view the service flow triggered by a specific database service
- In the Dynatrace menu, select Services.
- Select the service you want to analyze to open the service’s overview page.
- In the Understand dependencies section, select View Service flow.
While SQL analysis is available at multiple locations in the service-analysis workflow, it's the primary analysis view for databases in Service flow view.
The following example shows a database-service flow that has been filtered to focus on a particular chain of calls. You can see that whenever the Customer Frontend service calls the JourneyService, 0.01%% of the response time contribution can be attributed to the EasyTravelBusiness MongoDB service.
To view the database statements that were executed by the selected flow within the analysis time frame, select View database statements. This can be tremendously valuable because it helps you understand why a database contributes a certain amount of time.
From the Analyze menu , select
- Outliers to understand the spread of response times.
- Statement details to understand the evolution of each individual SQL statement that undergoes over time along with the average Rows returned count.
- Service backtrace to see which user clicks led to the slowest executions of this MongoDB statement.
As you can see, Dynatrace database-statement analysis views provide powerful database usage-focused analysis.