Manage Long-Running Queries

This page shows you how to identify and, if necessary, cancel SQL queries that are taking longer than expected to process.

Schema changes are treated differently than other SQL queries. You can use SHOW JOBS to monitor the progress of schema changes and CANCEL JOB to cancel schema changes that are taking longer than expected.

Identify long-running queries

Use the statement to list details about currently active SQL queries, including each query’s start timestamp:

> WITH x AS (SHOW CLUSTER STATEMENTS) SELECT * FROM x
      WHERE application_name != '$ cockroach sql';

              query_id             | node_id |            session_id            | user_name |              start               |                                 query                                 | client_address  | application_name | distributed |   phase
+----------------------------------+---------+----------------------------------+-----------+----------------------------------+-----------------------------------------------------------------------+-----------------+------------------+-------------+-----------+
  15f92c0dd24bec200000000000000003 |       3 | 15f92b0e4ea399680000000000000003 | root      | 2020-03-04 18:06:21.871708+00:00 | SELECT city, id FROM vehicles WHERE city = $1                         | 127.0.0.1:65088 |                  |    false    | executing
  15f92c0dd26655d80000000000000001 |       1 | 15f92be36964ac800000000000000001 | root      | 2020-03-04 18:06:21.873515+00:00 | UPSERT INTO vehicle_location_histories VALUES ($1, $2, now(), $3, $4) | 127.0.0.1:65240 |                  |    false    | executing
  15f92c0dd25882c80000000000000001 |       1 | 15f92aefb240d2980000000000000001 | root      | 2020-03-04 18:06:21.872608+00:00 | UPSERT INTO vehicle_location_histories VALUES ($1, $2, now(), $3, $4) | 127.0.0.1:65044 |                  |    false    | executing
  15f92c0dd262cb980000000000000002 |       2 | 15f92b7dc85b7ba80000000000000002 | maxroach  | 2020-03-04 18:06:21.873286+00:00 | SELECT city, id FROM vehicles WHERE city = $1                         | 127.0.0.1:65196 |                  |    false    | executing

You can also filter for queries that have been running for a certain amount of time. For example, to find queries that have been running for more than 3 hours, you would run the following:

> WITH x AS (SHOW CLUSTER STATEMENTS) SELECT * FROM x
      WHERE start < (now() - INTERVAL '3 hours');

If a query’s text ends with a trailing ellipsis (…), it has been truncated. To view more of the query text, increase the value of the cluster setting.

Cancel long-running queries

Once you’ve identified a long-running query via , note the query_id and use it with the statement:

> CANCEL QUERY '15f92c0dd24bec200000000000000003';

When a query is successfully cancelled, CockroachDB sends a query execution canceled error to the client that issued the query.

• If the canceled query was a single, stand-alone statement, no further action is required by the client.
• If the canceled query was part of a larger, multi-statement , the client should then issue a statement.

You can cancel all queries from a particular application by using a subquery.

CANCEL QUERIES (WITH x AS (SHOW CLUSTER QUERIES) SELECT query_id FROM x
      WHERE application_name = 'test_app');

Improve query performance

After cancelling a long-running query, use the statement to examine it. It’s possible that the query was slow because it performs a full-table scan. In these cases, you can likely improve the query’s performance by .

For guidance on optimizing SQL performance, see .

See also