Monitor active queries

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

On the Query insights dashboard, you can view a summary of active transactions and a list of transactions with associated query and statistics. The transactions are displayed by the transaction start time. If there is a large number of queries running, then the results might be limited to a subset 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 about Cloud SQL editions, see Introduction 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, see Manage 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 the Required permissionssection:

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

Enable active queries

When you enable query insights , you enable active queries automatically. To be able to terminate a session or long-running transaction in active queries, you must use query 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, see Disable query insights .

View active queries

To view your active queries, complete the following steps:

1. In the Google Cloud console, go to the Cloud SQL Instancespage.

   Go to Cloud SQL Instances

2. To open the Overviewpage of an instance, click the instance name.

3. Do one of the following to display the Query insightsdashboard:

   • Select the Query insightstab.
   • Click Go to Query insights for more in-depth info on queries and performance. The Query insightsdashboard opens. It shows details about the instance at the top.

4. Click the Active queriestab.

   From here, you can modify the query information that appears in the Longest running transactionstable:

   • 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 active queries on the Query insightsdashboard. A normalized active query removes sensitive data and returns a digest. The digest is the same for different values used 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 the dashboard.

View top longest running transactions

The Longest running transactionstable on the Query insightsdashboard contains the following columns:

The display is refreshed every 60 seconds automatically.

You can use the view_week Content 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 enable query 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 the Longest running transactiontable, select a query.
2. In the Actioncolumn, click Terminate session.
3. In the Terminate sessionwindow, click Confirm.

Blocked active queries

If a specific active query is blocked or running much longer than expected, then that active query can block other dependent queries.

Cloud SQL gives you the option to terminate specific long-running or blocked active queries.

Enable the analysis of blocked active queries

By default, Google Cloud console doesn't display which active queries are blocked.

In order to analyze blocked active queries, the following requirements apply:

• You must use an Cloud SQL Enterprise Plus edition instance.
• You must enable Enterprise Plus features .

To enable blocked active query analysis, do the following:

Identify and terminate a blocked active query

The following steps show you how to identify and terminate a specific active query that might be blocking other queries.

View details of blocking sleeping sessions

The Active queriestab displays sessions that are currently processing a request. Sessions in the SLEEPING state are excluded from this view.

To view the last command executed for a sleeping session, use the following script:

  SELECT 
  
 c 
 . 
 session_id 
 , 
  
 st 
 . 
 text 
 , 
 s 
 . 
 login_name 
 , 
  
 s 
 . 
 open_transaction_count 
 , 
  
 s 
 . 
 host_name 
 , 
  
 s 
 . 
 program_name 
 FROM 
  
 sys 
 . 
 dm_exec_connections 
  
 AS 
  
 c 
  
 JOIN 
  
 sys 
 . 
 dm_exec_sessions 
  
 AS 
  
 S 
  
 ON 
  
 c 
 . 
 session_id 
 = 
 s 
 . 
 session_id 
 CROSS 
  
 APPLY 
  
 sys 
 . 
 dm_exec_sql_text 
 ( 
 c 
 . 
 most_recent_sql_handle 
 ) 
  
 AS 
  
 st 
 WHERE 
  
 s 
 . 
 session_id 
 = 
  BLOCKING_SESSION_ID 
 
 

Replace BLOCKING_SESSION_ID with the ID of the session causing the block.

For more information to help troubleshoot and resolve blocking, see the following resources:

• Blocking caused by a sleeping SPID that has an uncommitted transaction
• Understand and resolve SQL Server blocking problems .

What's next

• Use query insights
• Use system insights