> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Manage Long-Running Queries

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

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 `CANCEL JOB` to cancel schema changes that are taking longer than expected.
</Tip>

## Identify long-running queries

Use the <InternalLink path="show-statements">`SHOW STATEMENTS`</InternalLink> statement to list details about currently active SQL queries, including each query's `start` timestamp:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> WITH x AS (SHOW CLUSTER STATEMENTS) SELECT * FROM x
      WHERE start < (now() - INTERVAL '3 hours');
```

## Cancel long-running queries

Once you've identified a long-running query via <InternalLink path="show-statements">`SHOW STATEMENTS`</InternalLink>, note the `query_id` and use it with the <InternalLink path="cancel-query">`CANCEL QUERY`</InternalLink> statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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 <InternalLink path="transactions">transaction</InternalLink>, the client should then issue a <InternalLink path="rollback-transaction">`ROLLBACK`</InternalLink> statement.

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 <InternalLink path="explain">`EXPLAIN`</InternalLink> 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 <InternalLink path="create-index">adding an index</InternalLink>.

<Tip>
  For guidance on optimizing SQL performance, see <InternalLink path="performance-best-practices-overview">SQL Performance Best Practices</InternalLink>.
</Tip>

## See also

* <InternalLink path="show-statements">`SHOW STATEMENTS`</InternalLink>
* <InternalLink path="cancel-query">`CANCEL QUERY`</InternalLink>
* <InternalLink path="explain">`EXPLAIN`</InternalLink>
* <InternalLink path="query-behavior-troubleshooting">Query Behavior Troubleshooting</InternalLink>