# 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.

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

## Identify long-running queries

Use the `SHOW QUERIES` statement to list details about currently active SQL queries, including each query's `start` timestamp:

    > SHOW QUERIES;

    +----------------------------------+---------+----------+----------------------------------+-------------------------------------------+---------------------+------------------+-------------+-----------+
    |             query_id             | node_id | username |              start               |                   query                   |   client_address    | application_name | distributed |   phase   |
    +----------------------------------+---------+----------+----------------------------------+-------------------------------------------+---------------------+------------------+-------------+-----------+
    | 14db657443230c3e0000000000000001 |       1 | root     | 2017-08-16 18:00:50.675151+00:00 | UPSERT INTO test.kv(k, v) VALUES ($1, $2) | 192.168.12.56:54119 | test_app         | false       | executing |
    | 14db657443b68c7d0000000000000001 |       1 | root     | 2017-08-16 18:00:50.684818+00:00 | UPSERT INTO test.kv(k, v) VALUES ($1, $2) | 192.168.12.56:54123 | test_app         | false       | executing |
    | 14db65744382c2340000000000000001 |       1 | root     | 2017-08-16 18:00:50.681431+00:00 | UPSERT INTO test.kv(k, v) VALUES ($1, $2) | 192.168.12.56:54103 | test_app         | false       | executing |
    | 14db657443c9dc660000000000000001 |       1 | root     | 2017-08-16 18:00:50.686083+00:00 | SHOW CLUSTER QUERIES                      | 192.168.12.56:54108 | cockroach        | NULL        | preparing |
    | 14db657443e30a850000000000000003 |       3 | root     | 2017-08-16 18:00:50.68774+00:00  | UPSERT INTO test.kv(k, v) VALUES ($1, $2) | 192.168.12.58:54118 | test_app         | false       | executing |
    | 14db6574439f477d0000000000000003 |       3 | root     | 2017-08-16 18:00:50.6833+00:00   | UPSERT INTO test.kv(k, v) VALUES ($1, $2) | 192.168.12.58:54122 | test_app         | false       | executing |
    | 14db6574435817d20000000000000002 |       2 | root     | 2017-08-16 18:00:50.678629+00:00 | UPSERT INTO test.kv(k, v) VALUES ($1, $2) | 192.168.12.57:54121 | test_app         | false       | executing |
    | 14db6574433c621f0000000000000002 |       2 | root     | 2017-08-16 18:00:50.676813+00:00 | UPSERT INTO test.kv(k, v) VALUES ($1, $2) | 192.168.12.57:54124 | test_app         | false       | executing |
    | 14db6574436f71d50000000000000002 |       2 | root     | 2017-08-16 18:00:50.680165+00:00 | UPSERT INTO test.kv(k, v) VALUES ($1, $2) | 192.168.12.57:54117 | test_app         | false       | executing |
    +----------------------------------+---------+----------+----------------------------------+-------------------------------------------+---------------------+------------------+-------------+-----------+
    (9 rows)

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:

    > SELECT * FROM [SHOW CLUSTER QUERIES]
          WHERE start < (now() - INTERVAL '3 hours');

## Cancel long-running queries

Once you've identified a long-running query via `SHOW QUERIES`, note the `query_id` and use it with the `CANCEL QUERY` statement:

    > CANCEL QUERY '14dacc1f9a781e3d0000000000000001';

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 transaction, the client should then issue a `ROLLBACK` statement.

## Improve query performance

After cancelling a long-running query, use the `EXPLAIN` 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 adding an index.

(More guidance around query performance optimization forthcoming.)

## See also

- `SHOW QUERIES`
- `CANCEL QUERY`
- `EXPLAIN`
- Query Behavior Troubleshooting