triaging-live-sql-activity

What this skill does

# Triaging Live SQL Activity

Diagnoses live cluster performance issues by identifying currently active long-running queries, busy sessions, and active transactions. Uses SQL-only interfaces (SHOW statements and `crdb_internal` views) to provide immediate triage without requiring DB Console, HTTP endpoints, or Prometheus access.

## When to Use This Skill

- Users report "the cluster is slow right now"
- High CPU or memory usage on cluster nodes
- Need to identify runaway queries or stuck transactions
- Want to find which applications/users are consuming resources
- Require immediate triage without DB Console access
- Need to generate SQL to cancel problematic sessions/queries

**For historical performance analysis:** Use [profiling-statement-fingerprints](../profiling-statement-fingerprints/SKILL.md) to analyze query patterns over time, identify slow fingerprints, and investigate trends without needing live queries.
**For transaction-level analysis:** Use [profiling-transaction-fingerprints](../profiling-transaction-fingerprints/SKILL.md) to analyze historical transaction retry patterns, commit latency trends, and statement composition.
**For background job monitoring:** Use [monitoring-background-jobs](../monitoring-background-jobs/SKILL.md) to monitor schema changes, backups, and automatic jobs that don't appear in SHOW CLUSTER STATEMENTS.

## Prerequisites

**Required SQL access:**
- Connection to any CockroachDB node
- For cluster-wide visibility: `VIEWACTIVITY` or `VIEWACTIVITYREDACTED` privilege
  - `VIEWACTIVITYREDACTED`: Redacts constants in other users' queries (recommended for privacy)
  - `VIEWACTIVITY`: Shows full query text for all users
  - Without these: Only see your own sessions/queries
- Basic understanding of SQL query execution
- (Optional) `CANCELQUERY` / `CANCELSESSION` privileges for cancellation operations

**Check your privileges:**
```sql
SHOW SYSTEM GRANTS FOR <username>;
```

See [permissions reference](references/permissions.md) for detailed RBAC setup.

## Core Diagnostic Approach

CockroachDB provides SQL-only interfaces for live activity triage:

| Interface | Purpose | Cluster-wide? |
|-----------|---------|---------------|
| `SHOW CLUSTER STATEMENTS` | Currently executing queries | Yes (with VIEWACTIVITY) |
| `SHOW CLUSTER SESSIONS` | Active client sessions | Yes (with VIEWACTIVITY) |
| `crdb_internal.cluster_transactions` | In-progress transactions | Yes (with VIEWACTIVITY) |

**Triage workflow:**
1. Identify long-running queries (> 5-10 minutes)
2. Correlate to sessions and applications
3. Check transaction retry counts (high retries = contention)
4. Drill down by app/user/client
5. (Optional) Cancel runaway work

**Safety:** All diagnostic queries are read-only. Cancellation is opt-in with explicit warnings.

## Core Diagnostic Queries

### Long-Running Queries

Identify queries running longer than a specified threshold:

```sql
-- Queries running longer than 5 minutes
WITH q AS (SHOW CLUSTER STATEMENTS)
SELECT
  query_id,
  node_id,
  session_id,
  user_name,
  client_address,
  application_name,
  start,
  now() - start AS running_for,
  substring(query, 1, 200) AS query_preview,
  distributed,
  phase
FROM q
WHERE start < now() - INTERVAL '5 minutes'
ORDER BY start
LIMIT 50;
```

**Key columns:**
- `running_for`: How long the query has been executing
- `query_preview`: First 200 characters (protects against massive queries)
- `phase`: execution phase (preparing, executing, etc.)
- `distributed`: whether query spans multiple nodes

**Customizable thresholds:**
- Change `INTERVAL '5 minutes'` to `'10 minutes'`, `'30 seconds'`, etc.
- Adjust `LIMIT` based on cluster size and expected load

### Active Sessions

Find sessions with long-running active queries:

```sql
-- Sessions with active queries running > 5 minutes
WITH s AS (SHOW CLUSTER SESSIONS)
SELECT
  node_id,
  session_id,
  user_name,
  client_address,
  application_name,
  status,
  active_query_start,
  now() - active_query_start AS active_query_for,
  substring(active_queries, 1, 200) AS active_queries_preview,
  substring(last_active_query, 1, 200) AS last_query_preview
FROM s
WHERE active_query_start IS NOT NULL
  AND active_query_start < now() - INTERVAL '5 minutes'
ORDER BY active_query_start
LIMIT 50;
```

**Key columns:**
- `active_query_for`: Duration of current active query
- `application_name`: Source application for drill-down
- `client_address`: Client IP/hostname for troubleshooting
- `status`: Session state (Idle, Active, etc.)

### Active Transactions

Identify long-running transactions (potential blockers):

```sql
-- Transactions running > 5 minutes
SELECT
  id AS txn_id,
  node_id,
  session_id,
  application_name,
  start,
  now() - start AS running_for,
  num_stmts,
  num_retries,
  num_auto_retries,
  substring(txn_string, 1, 200) AS txn_string_preview
FROM crdb_internal.cluster_transactions
WHERE start < now() - INTERVAL '5 minutes'
ORDER BY start
LIMIT 50;
```

**Key columns:**
- `num_retries` / `num_auto_retries`: High retry counts indicate contention
- `num_stmts`: Number of statements in transaction (large = potentially problematic)
- `txn_string`: Transaction fingerprint

**Production safety note:** `crdb_internal.cluster_transactions` is production-approved and safe for triage.

## Drill-Down by Application, User, or Client

Once you identify suspicious activity, drill down by filtering:

### Filter by Application

```sql
-- All activity from specific application
WITH q AS (SHOW CLUSTER STATEMENTS)
SELECT query_id, user_name, start, now() - start AS running_for,
       substring(query, 1, 200) AS query_preview
FROM q
WHERE application_name = 'payments-api'
ORDER BY start;
```

### Filter by User

```sql
-- All activity from specific user
WITH s AS (SHOW CLUSTER SESSIONS)
SELECT session_id, application_name, client_address,
       active_query_start, substring(active_queries, 1, 200) AS active_queries_preview
FROM s
WHERE user_name = 'app_user'
  AND active_query_start IS NOT NULL
ORDER BY active_query_start;
```

### Filter by Client Address

```sql
-- All sessions from specific client IP
WITH s AS (SHOW CLUSTER SESSIONS)
SELECT session_id, user_name, application_name,
       status, substring(active_queries, 1, 200) AS active_queries_preview
FROM s
WHERE client_address LIKE '10.0.1.%'
ORDER BY active_query_start;
```

### Combined Filters

```sql
-- Long queries from specific app and user
WITH q AS (SHOW CLUSTER STATEMENTS)
SELECT query_id, node_id, start, now() - start AS running_for,
       substring(query, 1, 200) AS query_preview
FROM q
WHERE application_name = 'payments-api'
  AND user_name = 'app_user'
  AND start < now() - INTERVAL '10 minutes'
ORDER BY start;
```

## Safety Considerations

**Read-only operations:**
All diagnostic queries (`SHOW` statements, `crdb_internal.cluster_transactions`) are read-only and safe to run in production.

**Cancellation operations (opt-in):**

**CAUTION: Canceling queries/sessions terminates user work**

Only proceed if:
- You've confirmed the query/session is runaway or stuck
- You have authorization to interrupt user workloads
- You've notified stakeholders if appropriate
- You have `CANCELQUERY` or `CANCELSESSION` privileges

## Canceling Runaway Work (Opt-In)

### Cancel a Specific Query

```sql
-- 1. Identify the query_id from triage queries above
-- 2. Cancel it
CANCEL QUERY '<query_id>';
```

**Example:**
```sql
CANCEL QUERY '15f9e0e91f072f0f0000000000000001';
```

### Cancel an Entire Session

```sql
-- 1. Identify the session_id from triage queries above
-- 2. Cancel all queries in that session
CANCEL SESSION '<session_id>';
```

**Example:**
```sql
CANCEL SESSION '15f9e0e91f072f0f';
```

**Verification:**
After canceling, re-run the triage queries to confirm the query/session is gone.

**Required privileges:**
- `CANCELQUERY` system privilege to cancel queries
- `CANCELSESSION` system privilege to cancel sessions
- Admin role has both by default

See [permissions refer