Use query insights to improve query performance

Stay organized with collections Save and categorize content based on your preferences.

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

Introduction

Query insights helps you detect, diagnose, and prevent query performance problems for Cloud SQL databases. It supports intuitive monitoring and provides diagnostic information that helps you go beyond detection to identify the root cause of performance problems.

Query insights helps you improve Cloud SQL query performance by guiding you through 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 in query insights to perform advanced query performance diagnostics. In addition to the 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.

The following table compares functional requirements and capabilities of query 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, select Enable Enterprise Plus features when you Enable query insights on your Cloud SQL Enterprise Plus edition instance.

Pricing

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

Storage requirements

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

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 that Enable automatic storage increases is enabled 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 account with the required Identity and Access Management permissions.

For more information about granting roles, see Manage 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, see Manage 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 the Required permissions section:

You might also be able to get these permissions with custom roles or other predefined roles.

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

Enable the Trace API

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

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

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

   Go to APIs and Services

2. Click Enable APIs and Services.
3. In the search bar, enter Trace API.
4. If API enabled is displayed, then this API is enabled and there's nothing for you to do. Otherwise, click Enable.

Enable automatic storage increases

If you're using query insights for Cloud SQL Enterprise Plus edition, then make sure that the instance setting to enable automatic storage increases remains enabled. By default, this option is enabled for Cloud SQL instances.

If you previously disabled this instance setting and want to enable 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

When you enable query insights, all other operations are temporarily suspended. These operations include health checks, logging, monitoring, and other instance operations.

    gcloud sql instances patch INSTANCE_ID \
    --insights-config-query-insights-enabled
  

gcloud sql instances patch INSTANCE_ID \
--insights-config-query-insights-enabled \
--insights-config-query-string-length=INSIGHTS_CONFIG_QUERY_STRING_LENGTH \
--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 query completion.

View the Query insights dashboard

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

To open the Query insights dashboard, follow these steps:

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

The Query insights dashboard opens. Depending on whether you're using query insights for Cloud SQL Enterprise edition or query insights for Cloud SQL Enterprise Plus edition, the Query insights dashboard shows the following information about your 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 and Databases: Filters query load by either a selected query or a selected database. See Filter the database load.

View the database load for all queries

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

The top-level query insights dashboard shows the Database load by execution time graph. Drop-down menus on the dashboard let you filter the graph for all databases or 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

• All queries

• Databases

To customize the database load chart, select a dimension from the Database 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 the Top dimensions by database load table. The Top dimensions by database load table displays the top contributors for the time period and dimension that you select in the Database Load by execution time chart drop-down. You can modify the time period or dimension to view the top contributors for a different dimension or time period.

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

Filter by queries

The Top queries table provides an overview of the queries that cause the most query load. The table shows all the normalized queries for the time range and options selected on the Query insights dashboard. It sorts queries by the total 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 as Create 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

A query plan takes a sample of your query and breaks it down into individual operations. It explains and analyzes each operation in the query.

The Query plan samples graph shows all the query plans running at particular times and the amount of time each plan took to run. You can change the rate at which query plan samples are captured per minute. See Enable Query Insights.

By default, the panel on the right shows the details for the sample query plan that takes the longest time, as visible on the Query plan samples graph. To see the details for another sample query plan, click the relevant circle on the 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 the operation. Use the full screen view or the End-to-end tab to see estimated execution statistics for each node (operation), as well as runtime execution 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 average execution time, the number of times called, and the average number of rows returned. It can also offer recommendations, such as indexes, to help improve query 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 the tempdb 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

gcloud sql instances patch INSTANCE_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 the Cloud SQL Instances page.

   Go to Cloud SQL Instances

2. To open the Overview page of an instance, click the instance name.
3. Click Edit.
4. In the Customize your instance section, expand Query insights.
5. Clear the Enable Enterprise Plus features checkbox.
6. Click Save.

What's next

• 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