Update to CockroachDB

{
  "description": "Diagnoses live CockroachDB cluster performance issues by identifying long-running queries, busy sessions, and active transactions using SQL-only interfaces. Use when users report cluster slowness, high CPU, or need to find runaway queries and their source applications without DB Console access.",
  "included_files": [
    {
      "relative_path": "references/permissions.md",
      "size_in_bytes": 10225
    },
    {
      "relative_path": "references/sql-queries.md",
      "size_in_bytes": 10455
    }
  ],
  "name": "triaging-live-sql-activity",
  "skill_md_contents": "---\nname: triaging-live-sql-activity\ndescription: Diagnoses live CockroachDB cluster performance issues by identifying long-running queries, busy sessions, and active transactions using SQL-only interfaces. Use when users report cluster slowness, high CPU, or need to find runaway queries and their source applications without DB Console access.\ncompatibility: Requires SQL access with VIEWACTIVITY or VIEWACTIVITYREDACTED cluster privilege for cluster-wide visibility.\n---\n\n# Triaging Live SQL Activity\n\nDiagnoses 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.\n\n## When to Use This Skill\n\n- Users report \"the cluster is slow right now\"\n- High CPU or memory usage on cluster nodes\n- Need to identify runaway queries or stuck transactions\n- Want to find which applications/users are consuming resources\n- Require immediate triage without DB Console access\n- Need to generate SQL to cancel problematic sessions/queries\n\n**For historical performance analysis:** Use [profiling-statement-fingerprints](../profile-stmt-fingerprints/SKILL.md) to analyze query patterns over time, identify slow fingerprints, and investigate trends without needing live queries.\n**For transaction-level analysis:** Use [profiling-transaction-fingerprints](../profile-txn-fingerprints/SKILL.md) to analyze historical transaction retry patterns, commit latency trends, and statement composition.\n**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.\n\n## Prerequisites\n\n**Required SQL access:**\n- Connection to any CockroachDB node\n- For cluster-wide visibility: `VIEWACTIVITY` or `VIEWACTIVITYREDACTED` privilege\n  - `VIEWACTIVITYREDACTED`: Redacts constants in other users' queries (recommended for privacy)\n  - `VIEWACTIVITY`: Shows full query text for all users\n  - Without these: Only see your own sessions/queries\n- Basic understanding of SQL query execution\n- (Optional) `CANCELQUERY` / `CANCELSESSION` privileges for cancellation operations\n\n**Check your privileges:**\n```sql\nSHOW SYSTEM GRANTS FOR <username>;\n```\n\nSee [permissions reference](references/permissions.md) for detailed RBAC setup.\n\n## Core Diagnostic Approach\n\nCockroachDB provides SQL-only interfaces for live activity triage:\n\n| Interface | Purpose | Cluster-wide? |\n|-----------|---------|---------------|\n| `SHOW CLUSTER STATEMENTS` | Currently executing queries | Yes (with VIEWACTIVITY) |\n| `SHOW CLUSTER SESSIONS` | Active client sessions | Yes (with VIEWACTIVITY) |\n| `crdb_internal.cluster_transactions` | In-progress transactions | Yes (with VIEWACTIVITY) |\n\n**Triage workflow:**\n1. Identify long-running queries (> 5-10 minutes)\n2. Correlate to sessions and applications\n3. Check transaction retry counts (high retries = contention)\n4. Drill down by app/user/client\n5. (Optional) Cancel runaway work\n\n**Safety:** All diagnostic queries are read-only. Cancellation is opt-in with explicit warnings.\n\n## Core Diagnostic Queries\n\n### Long-Running Queries\n\nIdentify queries running longer than a specified threshold:\n\n```sql\n-- Queries running longer than 5 minutes\nWITH q AS (SHOW CLUSTER STATEMENTS)\nSELECT\n  query_id,\n  node_id,\n  session_id,\n  user_name,\n  client_address,\n  application_name,\n  start,\n  now() - start AS running_for,\n  substring(query, 1, 200) AS query_preview,\n  distributed,\n  phase\nFROM q\nWHERE start < now() - INTERVAL '5 minutes'\nORDER BY start\nLIMIT 50;\n```\n\n**Key columns:**\n- `running_for`: How long the query has been executing\n- `query_preview`: First 200 characters (protects against massive queries)\n- `phase`: execution phase (preparing, executing, etc.)\n- `distributed`: whether query spans multiple nodes\n\n**Customizable thresholds:**\n- Change `INTERVAL '5 minutes'` to `'10 minutes'`, `'30 seconds'`, etc.\n- Adjust `LIMIT` based on cluster size and expected load\n\n### Active Sessions\n\nFind sessions with long-running active queries:\n\n```sql\n-- Sessions with active queries running > 5 minutes\nWITH s AS (SHOW CLUSTER SESSIONS)\nSELECT\n  node_id,\n  session_id,\n  user_name,\n  client_address,\n  application_name,\n  status,\n  active_query_start,\n  now() - active_query_start AS active_query_for,\n  substring(active_queries, 1, 200) AS active_queries_preview,\n  substring(last_active_query, 1, 200) AS last_query_preview\nFROM s\nWHERE active_query_start IS NOT NULL\n  AND active_query_start < now() - INTERVAL '5 minutes'\nORDER BY active_query_start\nLIMIT 50;\n```\n\n**Key columns:**\n- `active_query_for`: Duration of current active query\n- `application_name`: Source application for drill-down\n- `client_address`: Client IP/hostname for troubleshooting\n- `status`: Session state (Idle, Active, etc.)\n\n### Active Transactions\n\nIdentify long-running transactions (potential blockers):\n\n```sql\n-- Transactions running > 5 minutes\nSELECT\n  id AS txn_id,\n  node_id,\n  session_id,\n  application_name,\n  start,\n  now() - start AS running_for,\n  num_stmts,\n  num_retries,\n  num_auto_retries,\n  substring(txn_string, 1, 200) AS txn_string_preview\nFROM crdb_internal.cluster_transactions\nWHERE start < now() - INTERVAL '5 minutes'\nORDER BY start\nLIMIT 50;\n```\n\n**Key columns:**\n- `num_retries` / `num_auto_retries`: High retry counts indicate contention\n- `num_stmts`: Number of statements in transaction (large = potentially problematic)\n- `txn_string`: Transaction fingerprint\n\n**Production safety note:** `crdb_internal.cluster_transactions` is production-approved and safe for triage.\n\n## Drill-Down by Application, User, or Client\n\nOnce you identify suspicious activity, drill down by filtering:\n\n### Filter by Application\n\n```sql\n-- All activity from specific application\nWITH q AS (SHOW CLUSTER STATEMENTS)\nSELECT query_id, user_name, start, now() - start AS running_for,\n       substring(query, 1, 200) AS query_preview\nFROM q\nWHERE application_name = 'payments-api'\nORDER BY start;\n```\n\n### Filter by User\n\n```sql\n-- All activity from specific user\nWITH s AS (SHOW CLUSTER SESSIONS)\nSELECT session_id, application_name, client_address,\n       active_query_start, substring(active_queries, 1, 200) AS active_queries_preview\nFROM s\nWHERE user_name = 'app_user'\n  AND active_query_start IS NOT NULL\nORDER BY active_query_start;\n```\n\n### Filter by Client Address\n\n```sql\n-- All sessions from specific client IP\nWITH s AS (SHOW CLUSTER SESSIONS)\nSELECT session_id, user_name, application_name,\n       status, substring(active_queries, 1, 200) AS active_queries_preview\nFROM s\nWHERE client_address LIKE '10.0.1.%'\nORDER BY active_query_start;\n```\n\n### Combined Filters\n\n```sql\n-- Long queries from specific app and user\nWITH q AS (SHOW CLUSTER STATEMENTS)\nSELECT query_id, node_id, start, now() - start AS running_for,\n       substring(query, 1, 200) AS query_preview\nFROM q\nWHERE application_name = 'payments-api'\n  AND user_name = 'app_user'\n  AND start < now() - INTERVAL '10 minutes'\nORDER BY start;\n```\n\n## Safety Considerations\n\n**Read-only operations:**\nAll diagnostic queries (`SHOW` statements, `crdb_internal.cluster_transactions`) are read-only and safe to run in production.\n\n**Cancellation operations (opt-in):**\n\n**CAUTION: Canceling queries/sessions terminates user work**\n\nOnly proceed if:\n- You've confirmed the query/session is runaway or stuck\n- You have authorization to interrupt user workloads\n- You've notified stakeholders if appropriate\n- You have `CANCELQUERY` or `CANCELSESSION` privileges\n\n## Canceling Runaway Work (Opt-In)\n\n### Cancel a Specific Query\n\n```sql\n-- 1. Identify the query_id from triage queries above\n-- 2. Cancel it\nCANCEL QUERY '<query_id>';\n```\n\n**Example:**\n```sql\nCANCEL QUERY '15f9e0e91f072f0f0000000000000001';\n```\n\n### Cancel an Entire Session\n\n```sql\n-- 1. Identify the session_id from triage queries above\n-- 2. Cancel all queries in that session\nCANCEL SESSION '<session_id>';\n```\n\n**Example:**\n```sql\nCANCEL SESSION '15f9e0e91f072f0f';\n```\n\n**Verification:**\nAfter canceling, re-run the triage queries to confirm the query/session is gone.\n\n**Required privileges:**\n- `CANCELQUERY` system privilege to cancel queries\n- `CANCELSESSION` system privilege to cancel sessions\n- Admin role has both by default\n\nSee [permissions reference](references/permissions.md) for granting these privileges.\n\n## Common Triage Workflows\n\n### Workflow 1: \"Cluster is slow\" investigation\n\n**Scenario:** Users report general slowness.\n\n1. **Check for long-running queries:**\n   ```sql\n   -- Run the \"Long-Running Queries\" diagnostic\n   -- Look for queries running > 5-10 minutes\n   ```\n\n2. **Identify source applications:**\n   ```sql\n   -- Group by application to find culprits\n   WITH q AS (SHOW CLUSTER STATEMENTS)\n   SELECT application_name, COUNT(*) AS num_queries,\n          AVG(now() - start) AS avg_duration\n   FROM q\n   WHERE start < now() - INTERVAL '5 minutes'\n   GROUP BY application_name\n   ORDER BY num_queries DESC;\n   ```\n\n3. **Drill down into specific app:**\n   ```sql\n   -- Filter by top application from step 2\n   -- Use \"Filter by Application\" query\n   ```\n\n4. **Decide on action:**\n   - Contact app team to investigate query patterns\n   - Cancel specific runaway queries if critical\n   - Check for schema/index issues if queries are legitimate\n\n### Workflow 2: Find high-retry transactions\n\n**Scenario:** Suspect contention issues.\n\n1. **Check for high retry counts:**\n   ```sql\n   SELECT application_name, AVG(num_retries) AS avg_retries,\n          MAX(num_retries) AS max_retries, COUNT(*) AS num_txns\n   FROM crdb_internal.cluster_transactions\n   WHERE start < now() - INTERVAL '5 minutes'\n   GROUP BY application_name\n   HAVING AVG(num_retries) > 5\n   ORDER BY avg_retries DESC;\n   ```\n\n2. **Investigate specific transactions:**\n   ```sql\n   -- Find transactions with >10 retries\n   SELECT id, application_name, num_retries, num_stmts,\n          substring(txn_string, 1, 200) AS txn_preview\n   FROM crdb_internal.cluster_transactions\n   WHERE num_retries > 10\n   ORDER BY num_retries DESC;\n   ```\n\n3. **Next steps:**\n   - Review transaction patterns for contention\n   - Check for lock conflicts or hotspots\n   - Consider schema changes to reduce contention\n\n### Workflow 3: Identify resource hogs by user\n\n**Scenario:** Need to attribute load to specific users.\n\n1. **Count active queries per user:**\n   ```sql\n   WITH q AS (SHOW CLUSTER STATEMENTS)\n   SELECT user_name, COUNT(*) AS num_active_queries,\n          AVG(now() - start) AS avg_duration\n   FROM q\n   GROUP BY user_name\n   ORDER BY num_active_queries DESC;\n   ```\n\n2. **Drill down to specific user's activity:**\n   ```sql\n   -- Use \"Filter by User\" query\n   ```\n\n3. **Take action:**\n   - Contact user if unexpected load\n   - Review user's query patterns\n   - Cancel if clearly runaway\n\n## Troubleshooting\n\n| Issue | Cause | Fix |\n|-------|-------|-----|\n| `SHOW CLUSTER STATEMENTS` returns empty | No active queries, or insufficient privileges | Grant `VIEWACTIVITY` or `VIEWACTIVITYREDACTED`; verify cluster has active load |\n| Query text shows `<hidden>` | Using `VIEWACTIVITYREDACTED` privilege | This is expected for privacy; use `VIEWACTIVITY` if full text needed |\n| Can't cancel query: \"permission denied\" | Missing `CANCELQUERY` privilege | Grant `CANCELQUERY` system privilege to your user |\n| `crdb_internal.cluster_transactions` slow | High transaction volume on cluster | Add filters (application_name, time threshold) to reduce result set |\n| \"relation does not exist\" error | Typo in table name or old CockroachDB version | Verify you're using production-approved tables; check CockroachDB version compatibility |\n| Triage queries themselves are slow | Cluster under extreme load | Use more aggressive filters (shorter time window, specific apps); consider canceling obvious runaway work first |\n\n## Key Considerations\n\n- **Privacy:** Use `VIEWACTIVITYREDACTED` instead of `VIEWACTIVITY` to protect sensitive query constants in multi-tenant environments\n- **Performance impact:** Triage queries are read-only and lightweight, but avoid running them in tight loops during extreme load\n- **LIMIT clause:** Always include `LIMIT` to prevent overwhelming output on large clusters\n- **Time thresholds:** Adjust `INTERVAL` based on your workload (5 minutes is a reasonable default, but fast OLTP may need 30 seconds)\n- **Cancellation is disruptive:** Only cancel queries/sessions after confirming they're problematic; coordinate with application teams when possible\n- **Not for historical analysis:** These queries show current state only; for trends over time, use DB Console or Prometheus metrics\n- **Production-approved sources:** Only use `SHOW CLUSTER STATEMENTS`, `SHOW CLUSTER SESSIONS`, and `crdb_internal.cluster_transactions` for production triage\n\n## References\n\n**Skill references:**\n- [SQL query variations and examples](references/sql-queries.md)\n- [RBAC and privilege setup](references/permissions.md)\n\n**Related skills:**\n- [profiling-statement-fingerprints](../profile-stmt-fingerprints/SKILL.md) - For historical performance pattern analysis and trend identification\n- [profiling-transaction-fingerprints](../profile-txn-fingerprints/SKILL.md) - For historical transaction-level analysis including retry storms and commit latency\n\n**Official CockroachDB Documentation:**\n- [SHOW STATEMENTS](https://www.cockroachlabs.com/docs/stable/show-statements.html)\n- [SHOW SESSIONS](https://www.cockroachlabs.com/docs/stable/show-sessions.html)\n- [CANCEL QUERY](https://www.cockroachlabs.com/docs/stable/cancel-query.html)\n- [CANCEL SESSION](https://www.cockroachlabs.com/docs/stable/cancel-session.html)\n- [crdb_internal](https://www.cockroachlabs.com/docs/stable/crdb-internal.html)\n- [VIEWACTIVITY privilege](https://www.cockroachlabs.com/docs/stable/security-reference/authorization.html#supported-privileges)\n"
}