This page describes the Cloud SQL for SQL Server index advisor and how you can viewand apply its index recommendations.

Cloud SQL for SQL Server offers a fully managed index advisor that tracks thequeries your database handles regularly. Periodically, the index advisoranalyzes these queries to recommend new indexes that can improve queryperformance. The index advisor lets you detect and fix performanceissues with systems and queries.

How does the index advisor work?

The index advisor helps you improve query processing bydoing the following:

• Recommend a set of indexes with SQL commands to create indexes.
• Provide data to help you evaluate recommended indexes, for example,estimated storage size and impact of indexes on a query.

The index advisor stores and displays theCREATE INDEX command containing thedatabase name, schema name, table name, and column names. The tracked queriesare all normalized queries with all literals removed.

Index recommendations are encrypted at rest.

Limitations

Cloud SQL for SQL Server index advisor has the following limitations:

• The index advisor providesCREATE INDEX recommendations only.
• The index advisor doesn't support Cloud SQL Enterprise edition instances.

Before you begin

To obtain index advisor recommendations,you must use Cloud SQL Enterprise Plus edition and enablequery insights for Cloud SQL Enterprise Plus editionfor your Cloud SQL instance.

Required roles and permissions

To get the permissions that you need to obtain index advisor recommendations, ask your administrator to grant you theCloud SQL Viewer (roles/cloudsql.viewer) IAM role on the project that hosts the Cloud SQL instance. For more information about granting roles, seeManage access to projects, folders, and organizations.

This predefined role contains the permissions required to obtain index advisor recommendations. To see the exact permissions that are required, expand theRequired permissions section:

Required permissions

The following permissions are required to obtain index advisor recommendations:

• databaseinsights.recommendations.query
• databaseinsights.resourceRecommendations.query

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

Enable index advisor recommendations

When youenable query insights for Cloud SQL Enterprise Plus edition,you enable index advisor recommendations automatically.

Disable index advisor recommendations

You can't disable index advisor recommendations without disabling query insights for Cloud SQL Enterprise Plus edition.

To disable index advisor recommendations and query insights for Cloud SQL Enterprise Plus edition, seeDisable query insights for Cloud SQL Enterprise Plus edition.

View index advisor recommendations

Cloud SQL automatically runs the index advisor analysisperiodically. To view index advisor recommendations, use the Query insights dashboard.

View and filter recommendations in the Query insights dashboard

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.
4. ClickEnable. The index advisor recommendations are displayed intheRecommendation column of theQueries tab of theTop dimensions by database load section.
5. Optional: To view only the queries withCREATE INDEX recommendations, add afilter forRecommendation: Create Indexes.

View recommendations for a query

To view index recommendations for a specific query, follow thesesteps:

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.
4. In theTop queries and tags section, clickQueries.
5. To get recommendation details for a query, do one of the following:
   • Click a query to learn more about the recommendations for the selectedquery, including the following information:
     • Performance impact (high, medium, and low): Theestimated query speed after all recommended indexes are created.
     • Recommendations: Create index recommendations.
     • Tables impacted: The number of tables that will beimpacted when indexes are created.
     • Additional estimated storage needed: The estimatedstorage size needed to create all the recommended indexes.
     • Number of impacted queries: The total number ofqueries in the workload impacted by the index recommendations. Anindex can benefit multiple queries.
   • ClickCreate Indexes for a specific query to learn detailedrecommendations about creating indexes to improve query performance.

View impacted queries

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.
4. In theTop dimensions by database load table, clickQueries.
5. ClickCreate indexes for a specific query.
6. ClickShow impacted queries.
7. Click a query to learn details about the impacted query.

What's next

• Use query insights