Using Anomaly detection to investigate issues

Tired of comparing the trends chart with previous days to decide what “normal” looks like?  DPA does the work for you. DPA uses a machine learning algorithm to identify which hours have abnormally high wait times, and displays the information contextually on the trends page. When DPA detects an anomaly in the last 60 minutes, it changes the wait time meter status on the home page, letting you quickly identify the database instances your users might be waiting on. If you are relying on "tribal knowledge" to do this now, you might be missing issues that are affecting your end users.

Navigation 

There are 3 ways DPA can show anomalies happening in your database instances:

1. The Anomaly Detection chart:

The database instance Trend Tab now includes an Anomaly Detection chart to help you determine when anomalies occurred historically. In the screenshot below, note the critical anomalies (red) occurred on January 25 and February 1 where wait times were much higher than normal for at least one underlying hour in that day.

2. The DPA homepage: 

The wait meter now represents the status of Anomaly Detection for the last 60 minutes. Clicking on it takes you to the Trend Tab with the last 24-hour custom timeframe pre-selected. The anomaly status also appears within the DPAIM module if DPA is integrated with the Orion Platform.

3. Anomaly alert:

Configure the Database Instance Wait Time Anomaly alert to get notified when an anomaly is detected.

Anomaly Example 1: Anomaly Chart 

In the following example, the wait time chart shows that DPA detected critical anomalies for January 24:

Drilling in to that day shows that the anomalous behavior occurred within the 2-3 AM timeframe.

From here there are a couple ways to determine the cause of the anomaly:

• Drill in to the 2-3 AM timeframe and review the query performance analysis data for each top SQL statement.
• Use a report to compare 2-3 AM on Jan 24th to the 2-3 AM timeframe on other days.

Using query performance analysis to find the root cause of anomalies

Drilling in to the 2-3 AM timeframe gives us a view of the top queries executing within that hour:

In this example, we click the COMMIT TRAN SQL statement to open the Query Details page for that query, where we can see performance details for that hour. Note the predominant wait for COMMIT TRAN during the hour is HADR_SYNC_COMMIT, and this wait indicates the primary AG replica is waiting for secondary replicas to commit their data.

Bingo! The most likely issue is that we have slowdowns within the AG environment. But let’s take a step back and look at what’s normal for COMMIT TRAN over the last 90 days. Click the date control in the top center of the screen and choose Last 90 Days to review the data:

From this chart we can glean some nuggets of performance analysis gold:

• There were also issues with the COMMIT TRAN SQL waiting on HADR_SYNC_COMMIT from November 15 through December 10, but it was not as severe as January 24.
• From December 10 through January 24 there were almost no issues, except for a small blip on January 10.
• On January 24 the problem appeared again and DPA flagged it as an anomaly.
• Even though the issue is not as severe today as it was on January 24, the problem is still happening today (February 13).

Armed with this data, the team investigated the AG status screens within DPA and found there were noticeable delays. The team then turned to SolarWinds SAM, where they identified network issues between the primary and replica databases as the underlying cause for the anomalies.

Using reports to find the root cause of anomalies

Another way to dig deeper into the anomaly is by using reports to compare the 2-3 AM timeframe on January 24 to the same timeframe on other days. From here the Top SQLs or Top Waits reports are the best types of reports for reviewing issues. The following example uses the Top SQLs report, but the same process could be used for Top Waits, Top Plans, Top Database, and other DPA reports.

The key to this analysis is configuring the report to look at specific hour ranges. In the example below, these options were used:

• Top SQL: By default, the DPA trends screens show the top 15 SQL statements, but here they are configured to show 50 for greater visibility.
• Date Range: By default this is set to show the last 30 days, but here it is modified to show the last 90 days to coincide with DPA’s machine learning algorithm.
• Hour Range: By default, the report shows all hours of the day, but here it is customized to show the 2-3 AM timeframe.

When the report is displayed, you see the Top 50 SQL statements for the 2-3 AM timeframe. Similar to the previous section, the COMMIT TRAN SQL is the major contributor to increased wait times on January 24. Other days are also affected, but not as severely.

Configuring the Top Waits report in the same way, we see that the HADR_SYNC_COMMIT is the number 2 wait on January 24, behind Memory/CPU waits and more workload (taller bars) in general. Additional workload can mean more data is required to be synced between the primary and secondary replicas which, in turn, can cause higher than normal wait times. As mentioned above, the extra workload shown here caused one of the replicas to get behind and wait times went up accordingly.

Anomaly Example 2: Homepage Wait Meter Is Red 

In this example, it's 5:25 on a Tuesday and the wait meter on the DPA homepage is red. This indicates that the last completed 60-minute timeframe (4:20-5:20 PM) has much higher than normal wait time. Assuming DPA has enough historical data (at least 3 days), DPA has computed the normal wait time (with standard deviations) for 4:20-5:20 on Tuesdays based on its machine learning algorithm. DPA changes the color of the wait meter based on thresholds of 2 standard deviations (yellow) or 3 standard deviations (red).

To get more information about the anomaly, click on red wait meter bar to drill in to the last 24 hours. The last bar will also be red to match the wait meter. Drill in to the latest timeframe and use methods similar to those described above to determine the root cause.

Anomaly Example 3: Anomaly Alert

Why spend time manually sifting through the wait time charts when DPA can bring the anomaly detection data directly to your inbox? The new Database Instance Wait Time Anomaly alert has been added to DPA to warn you when higher-than-normal wait times are occurring. The alert looks at the most recently completed hour and sends an email if wait times are high. Use this alert to automatically keep tabs on all your monitored instances.