This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. You can process personal data for this feature as outlined in theCloud Data Processing Addendum, subject to the obligations and restrictions described in the agreement under which you access Google Cloud. Pre-GA features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

This document describes how you can use AI assistance in Cloud SQLto troubleshoot slow queries in Cloud SQL.You can use the AI assistance capabilities of Cloud SQLand Gemini Cloud Assist to investigate, analyze, obtain recommendations,and finally implement those recommendations to optimize your queries inCloud SQL.

Before you begin

To troubleshoot slow queries with AI assistance, do the following:

1. Review limitations with AI-assisted troubleshooting.
2. Enable AI-assisted troubleshooting.

Required roles and permissions

For the roles and permissions required to troubleshoot slow queries with AI assistance,seeObserve and troubleshoot with AI.

Use AI assistance

To use AI assistance with troubleshooting your slow queries,go to theQuery insights dashboardfor your Cloud SQL instance in Google Cloud console.

Top queries table

You can start troubleshooting slow queries with AI assistancein theTop queries table section of theQuery insightsdashboard.

Cloud SQL can help you identify which queries are performingslower than average during a specific detection time period. After you select atime range in theQuery insights dashboard, Cloud SQL checkswhether any queries are performing slower than average by using a detectiontime period of 24 hours before the end of your selected time range.

When you adjust the time range filter of theDatabase load chart, or any otherfilter such as database or user,Cloud SQL refreshes theTop queries table andreruns anomaly detection based on the new list of queries and anupdated detection time period.

When Cloud SQL detects an anomaly,Cloud SQL performs baseline performance analysis for your queryafter you clickAnalyze latency.Cloud SQL maps the metrics during the anomaly to the slow queryand searches for situations that might have causedthe slow performance. If Cloud SQL finds a potentialsituation, then it lets you view the evidence for the situation.Finally, Cloud SQL provides recommendations to fix and optimizeyour query performance.

To troubleshoot slow queries in theTop queries table intheQuery insights dashboard, do the following:

1. In the Google Cloud console, go to theCloud SQL Instances page.

   Go to Cloud SQL Instances

2. To open theOverview page of an instance, click the instance name.
3. In the SQL navigation menu, clickQuery insights.
4. In theExecuted queries chart, use theTime range filter to select either 1 hour, 6 hours, 1 day, 7 days, 30 days or a custom range.
5. In theTop queries table, review the list of queries for your database.
6. IfAnalyze latency appears next to theAvg Execution Time for a query, then Cloud SQL has detected an anomaly in your query performance. Cloud SQL checks for anomalies within the 24-hour time period that occurs before the end of your selected time range.
7. ClickAnalyze latency to start troubleshooting with AI assistance. This generates theAnalyzing query latency page.

If no queries displayAnalyze latency next toAvg Execution Time, then the reason might be one of the following:

• None of the queries listed have experienced an anomaly within the 24-hour detection period of the selected time range.
• Gemini Cloud Assist isn't set up.Ensure that Gemini Cloud Assist is set up for your Google Cloud user account and project.

Query details

You can also troubleshoot a slow query with AI assistancefrom theQuery details page.

1. In the Google Cloud console, go to theCloud SQL Instances page.

   Go to Cloud SQL Instances

2. To open theOverview page of an instance, click the instance name.
3. ClickQuery insights to open theQuery insights dashboard.
4. In theQuery insights dashboard, click the query in theTop queries that you want to view. TheQuery details page appears.
5. If Cloud SQL detects an anomaly for the query, then one or more of the following indicators appears in theQuery details page:
• A message on the details screen that saysThis query is slower than usual and anAnalyze query performance option.

• A message in theQuery latency chart that saysQuery slower than usual. If this message appears, then clickAnalyze to start troubleshooting with AI assistance. This generates theAnalyzing query latency page.

6. Optional: Use theTime range filter to select either 1 hour, 6 hours, 1 day, 7 days, 30 days or a custom range. When you adjust theTime range filter of theQuery details page , or any other filter such asDatabase orUser, Cloud SQL reruns anomaly detection.
7. If Cloud SQL doesn't detect an anomaly for the query, then you can still run an analysis on the query by clickingAnalyze query performance. This generates theAnalyzing query latency page.

Analyze query latency

Using AI assistance, you can analyze and troubleshoot the details of your query latency.

In theAnalyzing query latency page, you can view following details foryour query and the text of your query:

• Analysis time period
• Detected anomaly period
• Database
• Avg. execution time
• Time called
• Avg rows returned

The page also provides a latency chart that shows P50, P95,and P99 latency values over the selected time period.P50 shows you medianlatency where 50% of query users are experiencing high latency and50% are experiencing lower latency.Similarly, the P95 and P99 lines show youthat 95% and 99% of query users experience the indicated querylatencies. For P95, 5% are experiencing ahigher latency while for P99, only 1% are experiencinga higher latency.

You can see the specific time period when a significant increase in querylatency occurred.

Analysis time period

The analysis time period consists of the 24 hours that occur before the endof the time range that you select in theDatabase load chart of theQuery insights dashboard or theQuery details page.Cloud SQL uses this time period to compare baseline metrics withthe metrics retrieved during the time period ofthe anomaly.

On theQuery details page, if Cloud SQL has detectedan anomaly with the query, then after you select the query from theQuery insights dashboard, Cloud SQL performs a baselineperformance analysis for the query using the last 24 hours from the end of theanomaly. If Cloud SQL hasn't detected an anomaly with the queryand runs anomaly detection on the query again, thenCloud SQL uses 48 hours before the end of the selectedtime range as the performance baseline for the analysis time period.

Detected anomaly period

The detected anomaly period represents a time period when Cloud SQLfinds an anomalous change in query performance.Cloud SQL uses the baseline performance measured forthe query during the analysis time period.

If Cloud SQL detects multiple anomalies for a query within a selectedtime period, then Cloud SQL uses the last detected anomaly.

Situation

After you start your investigation, Cloud SQLanalyzes your query, any historical data, and tries to identify anunderlying situation that might explain the slowerperformance of your query over the selected time period.

For example, one situation for why your query is slow might beidentified asChange in data volume.

In identifying this particular situation,Cloud SQL has detected an anomalous increasein data volume for this query. Cloud SQL alsolists other possible situations where no anomalies aredetected, so you can eliminate them as possible root causes.

Evidence

For each situation, Cloud SQL provides a list ofevidence to support the finding. Cloud SQL basesevidence on metrics gathered from the instance, the database,and historical runs of the query.

The evidence presented for each situation reflects any anomalythat Cloud SQL detects for the querywithin the detection time period. Cloud SQL definesan anomaly as when a metric surpasses certain thresholds or meets specific criteria.

In this example, to support the situation of aChange in data volume, youmight see the following pieces of evidence:

• Data processed by the query: Up to 5989.97% increase in average dataprocessed by the query.
• Query execution time: Up to 1506.59% increase in average execution time.
• Shared block hits: Average shared block hits increased by 2397.83%

You might also see evidence that supports typical or non-anomalous queryperformance, such as:

• Rows returned: No significant changes detected.
• Data processing time: Insufficient data to evaluate change.

Evidence of standard query execution can help you decide which recommendationmight be better for you to implement.

Recommendations

Based on all of the situations analyzed, Cloud SQL provides youwith one or more actionable recommendations to help remediate the problemswith your slow query performance. Cloud SQL presents therecommendations with a cost-benefit analysis so you can make an informeddecision on whether to implement the recommendation.

For example, you might receive the following recommendation:

• Create recommended indexes: Create a new index oncountryto improve the performance of this query.

To implement this recommendation, do the following:

1. In theRecommendations table, clickView details next to the recommendation that you want to implement. TheInsight and recommendation window appears.

2. In theInsight and recommendation window, review theRecommended action andTables impacted.
3. Optional: If you want to learn more about index recommendations, then clickHow this helps to openGemini Cloud Assist.
4. ClickCopy all index commands.
5. In the SQL navigation menu, clickCloud SQL Studio.
6. Authenticate to Cloud SQL Studio.
7. Paste theCREATE INDEX command into Cloud SQL Studio to create the index. For more information about running SQL commands in Cloud SQL Studio, seeManage your data using Cloud SQL Studio.

If you want to continue troubleshooting or get more assistance with query performance,then you can also openGemini Cloud Assist.For more information, seeObserve and troubleshoot with AI assistance.

What's next

• Optimize queries with high memory usage