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'tset up.

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.While theTime range filter you choose applies to most results, theAnalysis time period is always set to 48 hours.
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 be identified asChange in query data volume.

In identifying this particular situation, Cloud SQL has detected ananomalous increase in data volume for this query. Cloud SQL alsolists other possible situations where no anomalies are detected, so you caneliminate them as possible root causes.

• Optimize Query Performance by Creating an Index and Updating Statistics:

  Lists specific actions to take to help optimize performance of the affectedquery. In this example, the recommendation suggests creating a B-TREE index onthe country column of thecovid_data table to help improve queryperformance. This will help the query planner use an efficient index scaninstead of a sequential scan for the join operation, reducing the number ofrows processed.

  The recommendation also provides a SQL statement to update table statistics.

  Why this recommendation?: Helps provide valuable context explaining whythis specific recommendation category was given.

  Note: While the recommendation is suggested by Gemini Cloud Assist,the actions provided for troubleshooting are human-defined. If Gemini Cloud Assistreceives too many parallel requests at once and quota is exceeded,Gemini Cloud Assist-generated recommendations aren't available. Insuch cases, the list of suggested actions might be less detailed.Why this recommendation? content is also unavailable.

  Index recommendations: Opens a panel with suggested SQL commands to helpresolve the query performance issue.

  Query insights: Provides a link to theQuery insights dashboard.

  If you want to learn more about suggestions provided in theDetailssection of the recommendation, links to relevant Cloud SQL andopen-source documentation are also available.

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.

Blocked active queries

If a specific active query is blocked or running much longer than expected, itcan block other dependent queries.

Cloud SQL gives you the option to terminate specific long-running orblocked active queries.

For more information, seeBlocked active queries.

What's next

• Optimize queries with high memory usage
• Use index advisor
• Monitor active queries