Triaging Live Sql Activity

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 to analyze query patterns over time, identify slow fingerprints, and investigate trends without needing live queries. For transaction-level analysis: Use profiling-transaction-fingerprints to analyze historical transaction retry patterns, commit latency trends, and statement composition. For background job monitoring: Use monitoring-background-jobs 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:

SHOW GRANTS ON ROLE <username>;

See permissions reference for detailed RBAC setup.

Core Diagnostic Approach

CockroachDB provides SQL-only interfaces for live activity triage:

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:

-- 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:

-- 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):

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

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

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

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

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

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

Example:

CANCEL QUERY '15f9e0e91f072f0f0000000000000001';

Cancel an Entire Session

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

Example:

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 reference for granting these privileges.

Common Triage Workflows

Workflow 1: "Cluster is slow" investigation

Scenario: Users report general slowness.

1. Check for long-running queries:

   -- Run the "Long-Running Queries" diagnostic
   -- Look for queries running > 5-10 minutes

2. Identify source applications:

   -- Group by application to find culprits
   WITH q AS (SHOW CLUSTER STATEMENTS)
   SELECT application_name, COUNT(*) AS num_queries,
          AVG(now() - start) AS avg_duration
   FROM q
   WHERE start < now() - INTERVAL '5 minutes'
   GROUP BY application_name
   ORDER BY num_queries DESC;

3. Drill down into specific app:

   -- Filter by top application from step 2
   -- Use "Filter by Application" query

4. Decide on action:

• Contact app team to investigate query patterns
• Cancel specific runaway queries if critical
• Check for schema/index issues if queries are legitimate

Workflow 2: Find high-retry transactions

Scenario: Suspect contention issues.

1. Check for high retry counts:

   SELECT application_name, AVG(num_retries) AS avg_retries,
          MAX(num_retries) AS max_retries, COUNT(*) AS num_txns
   FROM crdb_internal.cluster_transactions
   WHERE start < now() - INTERVAL '5 minutes'
   GROUP BY application_name
   HAVING AVG(num_retries) > 5
   ORDER BY avg_retries DESC;

2. Investigate specific transactions:

   -- Find transactions with >10 retries
   SELECT id, application_name, num_retries, num_stmts,
          substring(txn_string, 1, 200) AS txn_preview
   FROM crdb_internal.cluster_transactions
   WHERE num_retries > 10
   ORDER BY num_retries DESC;

3. Next steps:

• Review transaction patterns for contention
• Check for lock conflicts or hotspots
• Consider schema changes to reduce contention

Workflow 3: Identify resource hogs by user

Scenario: Need to attribute load to specific users.

1. Count active queries per user:

   WITH q AS (SHOW CLUSTER STATEMENTS)
   SELECT user_name, COUNT(*) AS num_active_queries,
          AVG(now() - start) AS avg_duration
   FROM q
   GROUP BY user_name
   ORDER BY num_active_queries DESC;

2. Drill down to specific user's activity:

   -- Use "Filter by User" query

3. Take action:

• Contact user if unexpected load
• Review user's query patterns
• Cancel if clearly runaway

Troubleshooting

Key Considerations

• Privacy: Use VIEWACTIVITYREDACTED instead of VIEWACTIVITY to protect sensitive query constants in multi-tenant environments
• Performance impact: Triage queries are read-only and lightweight, but avoid running them in tight loops during extreme load
• LIMIT clause: Always include LIMIT to prevent overwhelming output on large clusters
• Time thresholds: Adjust INTERVAL based on your workload (5 minutes is a reasonable default, but fast OLTP may need 30 seconds)
• Cancellation is disruptive: Only cancel queries/sessions after confirming they're problematic; coordinate with application teams when possible
• Not for historical analysis: These queries show current state only; for trends over time, use DB Console or Prometheus metrics
• Production-approved sources: Only use SHOW CLUSTER STATEMENTS, SHOW CLUSTER SESSIONS, and crdb_internal.cluster_transactions for production triage

References

Skill references:

• SQL query variations and examples
• RBAC and privilege setup

Related skills:

• profiling-statement-fingerprints - For historical performance pattern analysis and trend identification
• profiling-transaction-fingerprints - For historical transaction-level analysis including retry storms and commit latency

Official CockroachDB Documentation:

• SHOW STATEMENTS
• SHOW SESSIONS
• CANCEL QUERY
• CANCEL SESSION
• crdb_internal
• VIEWACTIVITY privilege

RBAC and Privilege Setup

This reference provides detailed information about CockroachDB privileges required for SQL activity triage, including how to grant them and security best practices.

Privilege Comparison

VIEWACTIVITY vs VIEWACTIVITYREDACTED

Redaction example:

• With VIEWACTIVITY: SELECT * FROM users WHERE email = 'user@example.com'
• With VIEWACTIVITYREDACTED: SELECT * FROM users WHERE email = '_'

Cancellation Privileges

Admin Role Defaults

The admin role has all privileges by default:

• VIEWACTIVITY
• CANCELQUERY
• CANCELSESSION
• All other system privileges

Checking Current Privileges

Check Your Own Privileges

-- Show all system privileges for your current user
SHOW GRANTS ON ROLE <your_username>;

Example output:

database_name | schema_name | object_name | object_type | grantee  | privilege_type       | is_grantable
--------------+-------------+-------------+-------------+----------+---------------------+--------------
NULL          | NULL        | NULL        | system      | myuser   | VIEWACTIVITYREDACTED | false
NULL          | NULL        | NULL        | system      | myuser   | CANCELQUERY          | false

Check Another User's Privileges (Admin Only)

-- Show privileges for another user
SHOW GRANTS ON ROLE <other_username>;

Check Role Membership

-- Show all roles you belong to
SHOW GRANTS ON ROLE <your_username>;

-- Show members of a specific role
SHOW GRANTS ON ROLE admin;

Granting Privileges

Prerequisite: You must be an admin user to grant system privileges.

Grant VIEWACTIVITYREDACTED (Recommended for Production)

-- Grant cluster-wide activity visibility with redaction
GRANT SYSTEM VIEWACTIVITYREDACTED TO <username>;

Use when:

• User needs to triage cluster-wide performance issues
• Privacy/security requires protecting query constants
• Multi-tenant or production environments

Grant VIEWACTIVITY (Full Query Text)

-- Grant cluster-wide activity visibility without redaction
GRANT SYSTEM VIEWACTIVITY TO <username>;

Use when:

• User needs full query text for debugging
• Single-tenant or development environments
• Privacy is not a concern

Warning: This exposes potentially sensitive data (passwords, emails, PII) in query constants.

Grant CANCELQUERY

-- Grant ability to cancel individual queries
GRANT SYSTEM CANCELQUERY TO <username>;

Use when:

• User is authorized to terminate runaway queries
• DBA or on-call SRE role

Grant CANCELSESSION

-- Grant ability to cancel entire sessions
GRANT SYSTEM CANCELSESSION TO <username>;

Use when:

• User is authorized to terminate client connections
• DBA or on-call SRE role

Grant Multiple Privileges at Once

-- Grant full triage and cancellation privileges
GRANT SYSTEM VIEWACTIVITYREDACTED, CANCELQUERY, CANCELSESSION TO <username>;

Revoking Privileges

Revoke Specific Privilege

-- Revoke activity viewing privilege
REVOKE SYSTEM VIEWACTIVITYREDACTED FROM <username>;

-- Revoke cancellation privilege
REVOKE SYSTEM CANCELQUERY FROM <username>;

Revoke Multiple Privileges

-- Revoke all triage-related privileges
REVOKE SYSTEM VIEWACTIVITYREDACTED, CANCELQUERY, CANCELSESSION FROM <username>;

Role-Based Access Control (RBAC)

Instead of granting privileges to individual users, use roles for easier management:

Create a Triage Role

-- Create a role for triage operations
CREATE ROLE triage_operator;

-- Grant triage privileges to the role
GRANT SYSTEM VIEWACTIVITYREDACTED, CANCELQUERY TO triage_operator;

-- Assign users to the role
GRANT triage_operator TO alice, bob, charlie;

Create a Read-Only Triage Role

-- Create a role for read-only triage (no cancellation)
CREATE ROLE triage_viewer;

-- Grant only viewing privilege
GRANT SYSTEM VIEWACTIVITYREDACTED TO triage_viewer;

-- Assign users to the role
GRANT triage_viewer TO viewer_user;

Example: Multi-Tier Access Model

-- Tier 1: View only (redacted)
CREATE ROLE triage_viewer;
GRANT SYSTEM VIEWACTIVITYREDACTED TO triage_viewer;
GRANT triage_viewer TO tier1_user;

-- Tier 2: View + Cancel Queries
CREATE ROLE triage_operator;
GRANT SYSTEM VIEWACTIVITYREDACTED, CANCELQUERY TO triage_operator;
GRANT triage_operator TO tier2_user;

-- Tier 3: Full triage + Cancel Sessions (DBA)
CREATE ROLE triage_admin;
GRANT SYSTEM VIEWACTIVITY, CANCELQUERY, CANCELSESSION TO triage_admin;
GRANT triage_admin TO dba_user;

Least Privilege Examples

On-Call SRE (Read-Only Triage)

-- Grant minimal privileges for read-only triage
GRANT SYSTEM VIEWACTIVITYREDACTED TO oncall_sre;

Rationale:

• Can diagnose issues without terminating workloads
• Protects against accidental cancellations
• Maintains privacy with redaction

Database Administrator (Full Triage)

-- Grant full triage and intervention privileges
GRANT SYSTEM VIEWACTIVITY, CANCELQUERY, CANCELSESSION TO dba_user;

Rationale:

• Needs full query text for deep debugging
• Authorized to terminate problematic workloads
• Trusted with sensitive data

Application Team Lead (Limited Scope)

Challenge: CockroachDB doesn't support application-scoped privileges.

Workaround: 1. Grant VIEWACTIVITYREDACTED for cluster-wide visibility 2. Train users to filter by their application_name in queries 3. Use audit logging to monitor cancellation operations

-- Grant view-only access
GRANT SYSTEM VIEWACTIVITYREDACTED TO app_team_lead;

-- User manually filters in queries:
-- WHERE application_name = 'my-app'

Security Best Practices

1. Use VIEWACTIVITYREDACTED by Default

Unless you have a specific need for full query text, always use VIEWACTIVITYREDACTED to protect sensitive data.

-- Recommended
GRANT SYSTEM VIEWACTIVITYREDACTED TO triage_user;

-- Avoid unless necessary
GRANT SYSTEM VIEWACTIVITY TO triage_user;

2. Separate View and Cancel Privileges

Not everyone who can diagnose issues should be able to cancel work. Use separate roles:

-- Most users: view only
GRANT SYSTEM VIEWACTIVITYREDACTED TO triage_viewer;

-- Senior users: view + cancel
GRANT SYSTEM VIEWACTIVITYREDACTED, CANCELQUERY TO triage_operator;

3. Enable Audit Logging

Track who cancels queries and sessions for accountability:

-- Enable audit logging for cancellation events
SET CLUSTER SETTING sql.log.admin_audit.enabled = true;

Logged events include:

• CANCEL QUERY operations
• CANCEL SESSION operations
• User who issued the command
• Timestamp

4. Rotate Privileges Regularly

Review and revoke privileges for users who no longer need them:

-- Quarterly privilege audit
SHOW GRANTS ON ROLE ALL;

-- Revoke from inactive users
REVOKE SYSTEM VIEWACTIVITYREDACTED FROM inactive_user;

5. Use Roles Instead of Direct Grants

Manage privileges via roles for easier auditing and updates:

-- Good: Use roles
CREATE ROLE triage_operator;
GRANT SYSTEM VIEWACTIVITYREDACTED TO triage_operator;
GRANT triage_operator TO alice;

-- Avoid: Direct grants to many users
GRANT SYSTEM VIEWACTIVITYREDACTED TO alice;
GRANT SYSTEM VIEWACTIVITYREDACTED TO bob;
GRANT SYSTEM VIEWACTIVITYREDACTED TO charlie;

6. Document Privilege Grants

Maintain documentation of who has what privileges and why:

-- Add comments to roles
COMMENT ON ROLE triage_operator IS 'Read-only triage access for on-call team';

Common Privilege Issues

Verifying Privilege Effects

Test VIEWACTIVITYREDACTED

-- 1. Grant privilege
GRANT SYSTEM VIEWACTIVITYREDACTED TO test_user;

-- 2. As test_user, run triage query
WITH q AS (SHOW CLUSTER STATEMENTS)
SELECT query_id, user_name, substring(query, 1, 200) AS query_preview
FROM q
LIMIT 10;

-- 3. Verify you see other users' queries with redacted constants

Test CANCELQUERY

-- 1. Grant privilege
GRANT SYSTEM CANCELQUERY TO test_user;

-- 2. Identify a query to cancel
WITH q AS (SHOW CLUSTER STATEMENTS)
SELECT query_id FROM q LIMIT 1;

-- 3. As test_user, attempt to cancel
CANCEL QUERY '<query_id_from_step_2>';

-- 4. Verify no "permission denied" error

References

Official CockroachDB Documentation:

• Authorization Overview
• GRANT (System Privilege)
• REVOKE (System Privilege)
• SHOW GRANTS
• CREATE ROLE

SQL Query Variations and Examples

This reference provides detailed SQL query variations for different triage scenarios. All queries are production-safe and read-only unless explicitly marked as cancellation operations.

Query Variations by Time Threshold

30 Seconds Threshold (Fast OLTP Workloads)

-- Long-running queries (>30 seconds)
WITH q AS (SHOW CLUSTER STATEMENTS)
SELECT query_id, node_id, user_name, application_name,
       start, now() - start AS running_for,
       substring(query, 1, 200) AS query_preview
FROM q
WHERE start < now() - INTERVAL '30 seconds'
ORDER BY start
LIMIT 50;

1 Minute Threshold

-- Long-running queries (>1 minute)
WITH q AS (SHOW CLUSTER STATEMENTS)
SELECT query_id, node_id, user_name, application_name,
       start, now() - start AS running_for,
       substring(query, 1, 200) AS query_preview
FROM q
WHERE start < now() - INTERVAL '1 minute'
ORDER BY start
LIMIT 50;

10 Minutes Threshold

-- Long-running queries (>10 minutes)
WITH q AS (SHOW CLUSTER STATEMENTS)
SELECT query_id, node_id, user_name, application_name,
       start, now() - start AS running_for,
       substring(query, 1, 200) AS query_preview
FROM q
WHERE start < now() - INTERVAL '10 minutes'
ORDER BY start
LIMIT 50;

30 Minutes Threshold (OLAP/Analytics)

-- Long-running queries (>30 minutes)
WITH q AS (SHOW CLUSTER STATEMENTS)
SELECT query_id, node_id, user_name, application_name,
       start, now() - start AS running_for,
       substring(query, 1, 200) AS query_preview
FROM q
WHERE start < now() - INTERVAL '30 minutes'
ORDER BY start
LIMIT 50;

Advanced Filtering Patterns

Regex Pattern for Application Names

-- Filter by application name pattern
WITH q AS (SHOW CLUSTER STATEMENTS)
SELECT query_id, application_name, start, now() - start AS running_for,
       substring(query, 1, 200) AS query_preview
FROM q
WHERE application_name ~ '^payments-.*'  -- Regex pattern
  AND start < now() - INTERVAL '5 minutes'
ORDER BY start
LIMIT 50;

IP Subnet Filtering

-- Sessions from specific IP subnet
WITH s AS (SHOW CLUSTER SESSIONS)
SELECT session_id, user_name, application_name, client_address,
       active_query_start, substring(active_queries, 1, 200) AS active_queries_preview
FROM s
WHERE client_address LIKE '10.0.1.%'  -- /24 subnet
  OR client_address LIKE '192.168.%'  -- /16 subnet
ORDER BY active_query_start;

Multi-Condition WHERE Clauses

-- Complex filtering: specific app, user, and duration
WITH q AS (SHOW CLUSTER STATEMENTS)
SELECT query_id, node_id, user_name, application_name,
       start, now() - start AS running_for,
       substring(query, 1, 200) AS query_preview
FROM q
WHERE application_name IN ('payments-api', 'billing-api')
  AND user_name = 'app_user'
  AND start < now() - INTERVAL '10 minutes'
  AND distributed = true  -- Only distributed queries
ORDER BY start
LIMIT 50;

Exclude Internal Queries

-- Filter out CockroachDB internal queries
WITH q AS (SHOW CLUSTER STATEMENTS)
SELECT query_id, user_name, application_name,
       start, now() - start AS running_for,
       substring(query, 1, 200) AS query_preview
FROM q
WHERE application_name != '$ internal'  -- Exclude internal app
  AND user_name != 'node'               -- Exclude node user
  AND start < now() - INTERVAL '5 minutes'
ORDER BY start
LIMIT 50;

Aggregation Queries

Top N Longest Running Queries

-- Top 10 longest running queries right now
WITH q AS (SHOW CLUSTER STATEMENTS)
SELECT query_id, user_name, application_name,
       start, now() - start AS running_for,
       substring(query, 1, 200) AS query_preview
FROM q
ORDER BY start ASC  -- Oldest first = longest running
LIMIT 10;

Count by Application

-- Number of active queries per application
WITH q AS (SHOW CLUSTER STATEMENTS)
SELECT application_name,
       COUNT(*) AS num_queries,
       AVG(now() - start) AS avg_duration,
       MAX(now() - start) AS max_duration
FROM q
GROUP BY application_name
ORDER BY num_queries DESC;

Count by User

-- Number of active queries per user
WITH q AS (SHOW CLUSTER STATEMENTS)
SELECT user_name,
       COUNT(*) AS num_queries,
       AVG(now() - start) AS avg_duration,
       MAX(now() - start) AS max_duration
FROM q
GROUP BY user_name
ORDER BY num_queries DESC;

Count by Node

-- Number of active queries per node
WITH q AS (SHOW CLUSTER STATEMENTS)
SELECT node_id,
       COUNT(*) AS num_queries,
       AVG(now() - start) AS avg_duration,
       MAX(now() - start) AS max_duration
FROM q
GROUP BY node_id
ORDER BY num_queries DESC;

Average/Max Durations by Application

-- Average and max query durations per application
WITH q AS (SHOW CLUSTER STATEMENTS)
SELECT application_name,
       COUNT(*) AS num_queries,
       AVG(now() - start) AS avg_duration,
       MAX(now() - start) AS max_duration,
       MIN(now() - start) AS min_duration
FROM q
WHERE start < now() - INTERVAL '1 minute'  -- Only queries >1 min
GROUP BY application_name
HAVING COUNT(*) > 5  -- Only apps with >5 active queries
ORDER BY avg_duration DESC;

Transaction-Specific Queries

High Retry Detection

-- Transactions with excessive retries (>10)
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_preview
FROM crdb_internal.cluster_transactions
WHERE num_retries > 10
ORDER BY num_retries DESC
LIMIT 50;

Long-Running with Many Statements

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

Transactions by Retry Count Histogram

-- Histogram of transactions by retry count
SELECT
  CASE
    WHEN num_retries = 0 THEN '0 retries'
    WHEN num_retries BETWEEN 1 AND 5 THEN '1-5 retries'
    WHEN num_retries BETWEEN 6 AND 10 THEN '6-10 retries'
    WHEN num_retries > 10 THEN '>10 retries'
  END AS retry_bucket,
  COUNT(*) AS num_transactions
FROM crdb_internal.cluster_transactions
GROUP BY retry_bucket
ORDER BY retry_bucket;

Contention Analysis

-- Applications with high average retry counts
SELECT application_name,
       COUNT(*) AS num_txns,
       AVG(num_retries) AS avg_retries,
       MAX(num_retries) AS max_retries,
       AVG(num_auto_retries) AS avg_auto_retries
FROM crdb_internal.cluster_transactions
WHERE start < now() - INTERVAL '5 minutes'
GROUP BY application_name
HAVING AVG(num_retries) > 3
ORDER BY avg_retries DESC;

Batch Operations

Generate Cancel Commands for Long Queries

-- Generate CANCEL QUERY statements for all queries >10 minutes
WITH q AS (SHOW CLUSTER STATEMENTS)
SELECT 'CANCEL QUERY ''' || query_id || ''';' AS cancel_command
FROM q
WHERE start < now() - INTERVAL '10 minutes'
ORDER BY start;

Usage: 1. Review the generated commands carefully 2. Copy and execute only the specific cancellations you approve 3. Do NOT blindly execute all generated commands

Generate Cancel Commands for Specific App

-- Generate CANCEL QUERY statements for specific application
WITH q AS (SHOW CLUSTER STATEMENTS)
SELECT 'CANCEL QUERY ''' || query_id || ''';' AS cancel_command,
       user_name, now() - start AS running_for,
       substring(query, 1, 100) AS query_preview
FROM q
WHERE application_name = 'runaway-app'
  AND start < now() - INTERVAL '5 minutes'
ORDER BY start;

Generate Cancel Session Commands

-- Generate CANCEL SESSION statements for long-idle sessions
WITH s AS (SHOW CLUSTER SESSIONS)
SELECT 'CANCEL SESSION ''' || session_id || ''';' AS cancel_command,
       user_name, application_name, client_address,
       status, now() - session_start AS session_age
FROM s
WHERE status = 'idle'
  AND session_start < now() - INTERVAL '1 hour'
ORDER BY session_start;

Session Analysis

Sessions by Status

-- Count of sessions by status
WITH s AS (SHOW CLUSTER SESSIONS)
SELECT status, COUNT(*) AS num_sessions
FROM s
GROUP BY status
ORDER BY num_sessions DESC;

Long-Idle Sessions

-- Sessions idle for >30 minutes
WITH s AS (SHOW CLUSTER SESSIONS)
SELECT session_id, user_name, application_name, client_address,
       status, session_start, now() - session_start AS session_age,
       substring(last_active_query, 1, 200) AS last_query_preview
FROM s
WHERE status = 'idle'
  AND session_start < now() - INTERVAL '30 minutes'
ORDER BY session_start
LIMIT 50;

Sessions with Multiple Active Queries

-- Sessions running multiple queries simultaneously
WITH s AS (SHOW CLUSTER SESSIONS)
SELECT session_id, user_name, application_name, client_address,
       active_queries, active_query_start,
       substring(active_queries, 1, 200) AS active_queries_preview
FROM s
WHERE active_queries ~ '.*\;.*'  -- Contains semicolon (multiple queries)
ORDER BY active_query_start;

Full-Text Query Search

Find Queries Containing Specific Table

-- Queries accessing specific table
WITH q AS (SHOW CLUSTER STATEMENTS)
SELECT query_id, user_name, application_name,
       start, now() - start AS running_for,
       substring(query, 1, 500) AS query_preview
FROM q
WHERE query LIKE '%my_table_name%'
ORDER BY start
LIMIT 50;

Find Queries with Specific SQL Pattern

-- Queries containing specific SQL pattern (e.g., JOIN)
WITH q AS (SHOW CLUSTER STATEMENTS)
SELECT query_id, user_name, application_name,
       start, now() - start AS running_for,
       substring(query, 1, 500) AS query_preview
FROM q
WHERE query ~ '.*JOIN.*'  -- Regex for JOIN
ORDER BY start
LIMIT 50;

Notes

• All queries include LIMIT clauses to prevent overwhelming output
• Adjust time thresholds (INTERVAL) based on your workload characteristics
• Use substring() to prevent extremely long query text from cluttering output
• For large clusters, consider adding more aggressive filters (node_id, application_name) to reduce result set size
• Batch cancellation queries are for generating commands only - always review before executing