This page describes how to monitor and troubleshoot the queries that are activein your database. Monitoring these queries can help identify causes of systemlatency and high CPU usage.

On theQuery insights dashboard, you can view a summary of activetransactions and a list of transactions with associated query and statistics.The transactions are displayed by the transaction start time. If there is alarge number of queries running, then the results might be limited to asubset of total queries.

Before you begin

To monitor active queries, you can use either Cloud SQL Enterprise edition or Cloud SQL Enterprise Plus edition.

To terminate a session or a long-running transaction in active queries,you must use Cloud SQL Enterprise Plus edition for your Cloud SQL instance.

For more information aboutCloud SQL editions, seeIntroduction to Cloud SQL editions.

Required roles and permissions

To get the permissions that you need to view active queries, ask your administrator to grant you the following IAM roles on the project that hosts the Cloud SQL instance:

• View instance activity summary:Cloud SQL Viewer (roles/cloudsql.viewer)
• View database activity summary and long-running transactions:Database Insights Viewer (roles/databaseinsights.viewer)
• Terminate a session or long running transaction:
  • Cloud SQL Editor (roles/cloudsql.editor)
  • Database Insights Operations Admin (roles/databaseinsights.operationsAdmin)

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

These predefined roles contain the permissions required to view active queries. To see the exact permissions that are required, expand theRequired permissions section:

Required permissions

The following permissions are required to view active queries:

• View database activity summary and long-running transactions:
  • databaseinsights.activeQueries.fetch
  • databaseinsights.activitySummary.fetch

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

Enable active queries

When youenable query insights,you enable active queries automatically. To be able to terminate a session orlong-running transaction in active queries, you must usequery insights for Cloud SQL Enterprise Plus edition.

Disable active queries

You can't disable active queries without disabling query insights. To disable active queries and query insights, seeDisable query insights.

View active queries

To view your active queries, complete the following steps:

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. Do one of the following to display theQuery insights dashboard:

   • Select theQuery insights tab.
   • ClickGo to Query insights for more in-depth info on queries and performance.TheQuery insights dashboard opens. It shows details about the instanceat the top.

4. Click theActive queries tab.

   From here, you can modify the query information that appears in theLongest running transactions table:

   • Database: filters query load on a specific database or all databases.
   • User: filters query load from a specific user account.

View normalized active queries

You can view a list of top long-running transactions with normalized activequeries on theQuery insights dashboard. A normalized active query removessensitive data and returns a digest. The digest is the same for different valuesused in the following example:

• Regular queries

  • select * from test_table where id=1;
  • select * from test_table_where id=2;

• Digest or normalized query

  • select * from test_table where id=?;

A query that runs in different sessions is displayed as different entries on thedashboard.

View top longest running transactions

TheLongest running transactions table on theQuery insights dashboardcontains the following columns:

Column name	Description
Session ID	Unique identifier of the session.
Query	SQL query text.
State of session	The state of the session.
Query duration	How long the query has been running.
Blocking session ID	The id of the session that is blocking the session.
Wait event type	The type of wait event, if the request is blocked for query execution.
Wait resource	The resource for which the request is currently waiting, if the request is blocked for query execution.
Transaction wait duration	The duration of the current wait, if the session is blocked.
Logical reads	The number of logical reads that have been performed by the request.
Reads	The number of reads that have been performed by the request.
Writes	The number of writes that have been performed by the request.
Database	Database where the session is running.
User	Name of the user connected to the database.
Client address	The specific IP address of the client that sent a query.
CPU time (ms)	The CPU time in milliseconds that is used by the request.
Application name	Name of application that initiated the session.
Transaction rows modified	Number of rows returned to client by this query.
% complete	Percent of work completed, if the query reports progress.
Granted query memory	The number of pages allocated to the execution of the request.
Action	Contains a link for terminating a session.

The display is refreshed every 60 seconds automatically.

You can use theview_weekContent display options...vertical menu to filter the columns you want to display.

Terminate a session

To terminate a session or long-running transaction,you must use Cloud SQL Enterprise Plus edition and enablequery insights for Cloud SQL Enterprise Plus edition.

Long running operations can take longer to terminate.

To terminate a query or transaction, complete the following steps:

1. In theLongest running transaction table, select a query.
2. In theAction column, clickTerminate session.
3. In theTerminate session window, clickConfirm.

Note: IfTerminate isn't available, then you might have insufficientIAM permissions. For more information, seeBefore you begin.

What's next

• Use query insights
• Use system insights