This page describes how to use the Query insights dashboard todetect and analyze performance problems with your queries.

Introduction

Query insights helps you detect, diagnose, and prevent query performance problemsfor Cloud SQL databases. It supports intuitive monitoring and providesdiagnostic information that helps you go beyond detection to identify theroot cause of performance problems.

Query insights helps you improve Cloud SQL query performance by guiding youthrough the following steps:

• View the database load for top queries
• Identify a potentially problematic query

Query insights for Cloud SQL Enterprise Plus edition

If you're using Cloud SQL Enterprise Plus edition, then you can access additional capabilities inquery insights to perform advanced query performance diagnostics. In addition tothe standard capabilities of the Query insights dashboard,query insights for Cloud SQL Enterprise Plus edition lets you do the following:

• Capture longer query text up to 20 KB.
• Capture query plans for all executed queries.
• Sample up to 200 query plans per minute.
• Maintain a longer 30-day retention of metrics.
• Obtain index recommendations from index advisor.
• Terminate a session or long-running transaction in active queries.
• Access AI-assisted troubleshooting (Preview).

The following table compares functional requirements and capabilities ofquery insights for Cloud SQL Enterprise edition with query insights for Cloud SQL Enterprise Plus edition.

Enable query insights for Cloud SQL Enterprise Plus edition

To enable query insights for Cloud SQL Enterprise Plus edition, selectEnable Enterprise Plus features when youEnable query insights on your Cloud SQL Enterprise Plus edition instance.

Pricing

Sign up to try Cloud SQL at no charge.

There's no additional cost for query insights on Cloud SQL Enterprise edition orCloud SQL Enterprise Plus edition instances.

Storage requirements

Query insights stores metrics data on the instance and requires that youkeep the automatic storage increases setting enabled.Applicable storage fees apply.

Metrics storage limitations

The following limitations apply to query insights for Cloud SQL Enterprise Plus edition instances:

• If your instance is undergoing heavy system load, then when you querymetrics data in theQuery insights dashboard, your queries might be slow toload or time out.
• If you recreate a read replica, then the recreated read replicadoesn't maintain its previous metrics history.
• If you restore an instance with an old backup, then you can lose themetrics between the time of your backup and the time that you restore theinstance for query insights for Cloud SQL Enterprise Plus edition.For example, if you restoreyour instance on April 30 with a backup taken on April 25, then you mightlose all the metrics between April 25 and April 30.

Before you begin

Before you use query insights, do the following.

1. Add required roles and permissions.
2. Enable the Cloud Trace API.
3. If you're using query insights for Cloud SQL Enterprise edition or query insights for Cloud SQL Enterprise Plus edition, then make sure thatEnable automatic storage increases isenabled for the instance.

Required roles and permissions

To use query insights, you need to grant a predefined role,create a custom role, or provide a user accountwith the required Identity and Access Management permissions.

For more information about granting roles, seeManage access.

To get the permissions that you need to access historical query execution data in the Query insights dashboard, ask your administrator to grant you the following IAM roles on the project that hosts the Cloud SQL instance:

• Database Insights Monitoring Viewer (roles/databaseinsights.monitoringViewer)
• Cloud SQL Viewer (roles/cloudsql.viewer)

For more information about granting roles, seeManage access to projects, folders, and organizations.

These predefined roles contain the permissions required to access historical query execution data in the Query insights dashboard. To see the exact permissions that are required, expand theRequired permissions section:

You might also be able to get these permissions withcustom roles or otherpredefined roles.

• Cloud SQL Editor (roles/cloudsql.editor)
• Cloud SQL Admin (roles/cloudsql.admin)

Enable the Cloud Trace API

To view query plans and their end-to-end views, your Google Cloud projectmust have the Cloud Trace API enabled. This setting lets yourGoogle Cloud project receive trace data from authenticated sources at noadditional cost. This data can help you detect and diagnose performance issuesin your instance.

To confirm that the Cloud Trace API is enabled, follow these steps:

1. From the Google Cloud console, go toAPIs and Services:

   Go to APIs and Services

2. ClickEnable APIs and Services.
3. In the search bar, enterCloud Trace API.
4. IfAPI enabled is displayed, then this API is enabled and there's nothing for you to do. Otherwise, clickEnable.

Enable automatic storage increases

If you're using query insights for Cloud SQL Enterprise Plus edition, then make sure that the instance settingtoenable automatic storage increasesremains enabled. By default, this option is enabled for Cloud SQLinstances.

If you previously disabled this instance setting and want toenable query insights for Cloud SQL Enterprise Plus edition, then re-enable automatic storage increases first.You can't turn off automatic storage increases and enable query insights for Cloud SQL Enterprise Plus edition.

Enable query insights

gcloudsqlinstancespatchINSTANCE_ID\--insights-config-query-insights-enabled

gcloudsqlinstancespatchINSTANCE_ID\--insights-config-query-insights-enabled\--insights-config-query-string-length=INSIGHTS_CONFIG_QUERY_STRING_LENGTH\--insights-config-query-plans-per-minute=QUERY_PLANS_PER_MINUTE\--tier=API_TIER_STRING\--region=REGION

PATCH https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_ID

{  "settings" : {     "insightsConfig" : {       "queryInsightsEnabled" : true,       "recordClientAddress" : true,       "queryStringLength" : 1024,       "queryPlansPerMinute" : 20,   }  }}

curl -X PATCH \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_ID"

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method PATCH `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_ID" | Select-Object -Expand Content

{  "kind": "sql#operation",  "targetLink": "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_ID",  "status": "PENDING",  "user": "user@example.com",  "insertTime": "2025-03-28T22:43:40.009Z",  "operationType": "UPDATE",  "name": "OPERATION_ID",  "targetId": "INSTANCE_ID",  "selfLink": "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/operations/OPERATION_ID",  "targetProject": "PROJECT_ID"}

Metrics are expected to be available in query insights within minutes of querycompletion.

View the Query insights dashboard

The Query insights dashboard shows thequery load based on factorsthat you select. Query load is a measurement of the total work for all thequeries in the instance in the selected time range. The dashboard provides aseries of filters that help you view query load.

To open theQuery insights dashboard, follow these steps:

1. To open theOverview page of an instance, click the instance name.
2. In the Cloud SQL navigation menu, clickQuery insights, or clickGo to Query insights for more in-depth info on queries and performance on theInstance overview page.

TheQuery insights dashboard opens. Depending on whether you're usingquery insights for Cloud SQL Enterprise edition or query insights for Cloud SQL Enterprise Plus edition, the Query insights dashboardshows the following information aboutyour instance:

• Databases: Filters query load on a specific database or all databases.
• Time range: Filters query load by time ranges, such as 1 hour, 6 hours, 1 day, 7 days, 30 days, or a custom range.
• Database load graph: Displays the query load graph, based on filtered data.
• Queries andDatabases: Filters query load by either a selected query or a selected database. SeeFilter the database load.

Note: The data retention policy for query metrics is7 days for query insights for Cloud SQL Enterprise editionand 30 days for query insights for Cloud SQL Enterprise Plus edition.

View the database load for all queries

Database query load is a measure of the work (in CPU seconds) that the executedqueries in your selected database perform over time. Each runningquery is either using or waiting for CPU resources, IO resources, or lockresources. Database query load is the ratio of the amount of time taken by allthe queries that are completed in a given time window to the wall-clock time.

The top-level query insights dashboard shows theDatabase load by execution timegraph. Drop-down menus on the dashboard let you filter the graph for all databasesor a specific database.

Colored lines in the graph show per database load by execution time.Review the graph and use the filtering options to explore these questions:

• Is the query load high? Is the graph spiking or elevated over time? If you don't see a high load, then the problem isn't with your query.
• How long has the load been high? Is it high only now or has it been high for a long time? Use the range selector to select various time periods to find out how long the problem has lasted. Zoom in to view a time window where query load spikes are observed. Zoom out to view up to one week of the timeline.
• Which database is experiencing the load? Select different databases from the Databases drop-down menu to find the databases with the highest loads.

Filter the database load

You can filter the database load by queries.If you're using query insights for Cloud SQL Enterprise Plus edition, then you can customize the database load chartto break down the displayed data usingany of the following dimensions:

• All queries

• Databases

To customize the database load chart, select a dimensionfrom theDatabase Load by execution time drop-down.

View the top contributors to database load

To view the top contributors to the database load,you can use theTop dimensions by database load table.TheTop dimensions by database load table displays the top contributors forthe time period and dimension that you select in theDatabase Load by execution time chart drop-down. You can modify thetime period or dimension to view the top contributors for a different dimensionor time period.

In theTop dimensions by data load table, you can select the following tabs.

Tab	Description
Queries	The table displays the top normalized queries by total execution time. For each query, the data shown in columns are listed as follows: Avg execution time (ms): average time for the query to execute. Total execution time (ms): the total execution time taken by the specific query. Avg rows returned: the average number of rows fetched for the query. Times called: the number of times the query was called by the application.
Databases	The table shows the list of top databases that contributed to the load during the chosen time period across all executed queries. Avg time spent in database (ms): average time the queries spent in the specific database. Total time spent in database (ms): the total execution time the queries spent in the specific database.

Filter by queries

TheTop queries table provides an overview of the queries that cause themost query load. The table shows all the normalized queries for the time rangeand options selected on the Query insights dashboard. It sorts queries by thetotal execution time during the time range that you selected.

To sort the table, select a column heading.

The table shows the following properties:

• Query: the normalized query string. By default, query insights shows only 1024 characters in the query string.
• Database: the database against which the query was run.
• Recommendations: the suggested recommendations, such asCreate indexes, to improve query performance.
• Avg execution time (ms): the average time for the query to execute.
• Total execution time (ms): the total time for the query to execute.
• Times called: the number of times the application called the query.
• Avg rows returned: the average number of rows returned for the query.

Query insights stores and displays only normalized queries.

Examine operations in a sampled query plan

Aquery plan takes asample of your query and breaks it down into individual operations. It explainsand analyzes each operation in the query.

TheQuery plan samples graph shows all the query plans running atparticular times and the amount of time each plan took to run. You can changethe rate at which query plan samples are captured per minute. SeeEnable Query Insights.

By default, the panel on the right shows the details for the sample query planthat takes the longest time, as visible on theQuery plan samples graph.To see the details for another sample query plan, click the relevant circle onthe graph. Expanded details show a model of all the operations in the query plan.

Each operation shows the rows returned and, if available, the cost of theoperation. Use the full screen view or theEnd-to-end tab to seeestimated execution statistics for each node (operation), as well as runtimeexecution statistics associated with the root node.

Try to narrow down the problem by looking into the following questions:

1. What's the resource consumption?
2. How does it relate to other queries?
3. Does consumption change over time?

Query details feature

Query details can provide further details about a specific query, including averageexecution time, the number of times called, and the average number of rowsreturned. It can also offer recommendations, such as indexes, to help improvequery performance.

Query details also provide the following metrics:

• Query plan samples: These samples help explain and analyze your query by providing a breakdown of the different operations in a sample of the query.

  

• Query execution count over time: Shows the number of times the query has completed over a specified period. The chart helps you better understand when a query is usually executed and can help you estimate the impact of the query on instance performance.

  

• Average rows returned over time: Shows the average number of rows returned by the query over time. This data can help you correlate slower than usual queries with an increased amount of data returned by those queries.

  

• I/O statistics: Shows the input and output statistics for the query, including the following filters:
  • Average logical reads
  • Average physical reads
  • Average logical writes

  You can use I/O metrics to help you correlate slower than usual queries with more intensive I/O usage or changes in I/O patterns.

  For example, if the number of logical reads increased, then that could be an indication that the query started reading more data than usual. If the number of physical reads increased, then that could indicate that Cloud SQL started reading more data from disk rather than from the buffer cache. For example, perhaps there wasn't enough memory to cache all data or the instance started reading too much data. If the number of logical writes increased, then that could indicate that the query started writing more data to the data disk.

  This is one example of how this metric could help measure the impact of the query on instance I/O.

  

• Memory statistics: Shows the memory usage statistics for the query, including the following filters:
  • Average ideal memory
  • Average reserved memory
  • Average used memory

  This metric can help you detect changes in memory consumption related to the query, including the following examples:

  • Use this metric to detect when Cloud SQL mispredicted the amount of memory required for executing the query and might have reserved more memory than used by the query.
  • Use this metric to detect when Cloud SQL couldn't allocate enough memory and reserved a smaller memory grant. When this happens, Cloud SQL uses thetempdb database, which is slower than using memory.
  • Use the metric together with the number of query executions to measure impact of the query on memory consumption of the instance.
  • Use the metric when you see high memory usage or memory pressure.

  

• CPU and execution time statistics: Shows a combined view of CPU time and execution time statistics for the query. You can use the CPU time metric to investigate high CPU usage on the instance, including the following examples:
  • You can use a combination of CPU time and execution count metrics to measure the impact of the query on total CPU consumption in the instance.
  • You can use a combination of CPU time, execution time, and parallelism metrics to measure the percentage of time the query was waiting for resources.
  • You can use the execution time metric to detect when a query took more time to execute than usual.

  

• Additional query statistics: Shows additional statistics related to the query, including the following filters:
  • Common language runtime (CLR) statistics
  • Columnstore statistics
  • Memory spills
  • Parallelism statistics

  

Disable query insights

gcloudsqlinstancespatchINSTANCE_ID\--no-insights-config-query-insights-enabled

PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id

{  "settings" : { "insightsConfig" : { "queryInsightsEnabled" : false } }}

curl -X PATCH \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id"

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method PATCH `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id" | Select-Object -Expand Content

{  "kind": "sql#operation",  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id",  "status": "PENDING",  "user": "user@example.com",  "insertTime": "2021-01-28T22:43:40.009Z",  "operationType": "UPDATE",  "name": "operation-id",  "targetId": "instance-id",  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id",  "targetProject": "project-id"}

Disable query insights for Cloud SQL Enterprise Plus edition

To disable query insights for Cloud SQL Enterprise Plus edition, 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. ClickEdit.
4. In theCustomize your instance section, expandQuery insights.
5. Clear theEnable Enterprise Plus features checkbox.
6. ClickSave.

• Launch blog:Solve database bottlenecks faster with the latest query insights for Cloud SQL Enterprise Plus edition
• Blog:Boost your query performance troubleshooting skills with Cloud SQL Insights
• Video:Introducing Cloud SQL Insights
• Podcast:Cloud SQL Insights
• Insights Codelab
• Optimize high CPU usage
• Optimize high memory usage