Use index advisor with query insights

MySQL   |  PostgreSQL   |  SQL Server

Preview

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

Note: Index advisor requires query insights for Cloud SQL Enterprise Plus edition.

This page describes how you can use the Cloud SQL for SQL Server index advisor with query insights for Cloud SQL Enterprise Plus edition.

You can use the index advisor with query insights for Cloud SQL Enterprise Plus edition to view index advisor recommendations and create recommended indexes to improve query performance. To learn more about the index advisor, see Index advisor overview.

Before you begin

1. Make sure you're using Cloud SQL for SQL Server 2019 or later.
2. Enable query insights for Cloud SQL Enterprise Plus edition.
3. Create a custom role with the following permissions. Then, grant the role to each user account that needs to use query insights.

Tasks	Roles	Permissions
View recommendations	databaseinsights.viewer	databaseinsights.recommendations.query databaseinsights.resourceRecommendations.query
View recommendations	cloudsql.viewer	N/A

View and filter the index advisor recommendations

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 Query insights.
4. Click Enable. The index advisor recommendations are displayed in the Recommendation column of the Queries tab of the Top dimensions by database load section.
5. (Optional) To view only queries with CREATE INDEX recommendations, add a filter for Recommendation: Create Indexes.

View recommendations for a query

To view details of index recommendations for a specific query, follow these steps:

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 Query insights.
4. In the Top queries and tags section, click Queries.
5. To learn recommendation details about a query, do one of the following:
   • Click a query to learn more about recommendations for the selected query, including the following information:
     • Performance impact (high, medium, and low): The estimated query speed after all recommended indexes are created.
     • Recommendations: Create index recommendations.
     • Tables impacted: The number of tables that will be impacted when indexes are created.
     • Additional estimated storage needed: The estimated storage size needed to create all the recommended indexes.
     • Number of impacted queries: The total number of queries in the workload impacted by the index recommendations. An index may benefit multiple queries.
   • Click Create Indexes for a specific query to learn detailed recommendations about creating indexes to improve query performance.

View impacted queries

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 Query insights.
4. In the Top dimensions by database load table, click Queries.
5. Click Create indexes for a specific query.
6. Click Show impacted queries.
7. Click a query to learn details about the impacted query.

Create recommended indexes

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 Query insights.
4. In the Top dimensions by database load table, click Queries.
5. Click Create Indexes for a specific query.
6. Click Copy all index commands. The CREATE INDEX commands are copied to your clipboard.
7. Connect to the primary instance on the command line.

8. To create the recommended indexes, run the commands that were copied to your clipboard, for example:

   CREATE INDEX ON "public"."demo_order" ("customer_id");

What's next

• Learn how to use index advisor to view and query recommendations as a table, or to request an on-demand analysis and report.
• Learn more about index advisor