Update to CockroachDB

{
  "description": "Ranks and analyzes statement fingerprints using aggregated SQL statistics from crdb_internal.statement_statistics to identify slow, resource-intensive, or error-prone query patterns. Use when investigating historical performance trends, identifying optimization opportunities, or diagnosing recurring slowness without DB Console access.",
  "included_files": [
    {
      "relative_path": "references/json-field-reference.md",
      "size_in_bytes": 14003
    },
    {
      "relative_path": "references/metrics-and-units.md",
      "size_in_bytes": 17323
    },
    {
      "relative_path": "references/sql-query-variations.md",
      "size_in_bytes": 20760
    }
  ],
  "name": "profile-stmt-fingerprints",
  "skill_md_contents": "---\nname: profile-stmt-fingerprints\ndescription: Ranks and analyzes statement fingerprints using aggregated SQL statistics from crdb_internal.statement_statistics to identify slow, resource-intensive, or error-prone query patterns. Use when investigating historical performance trends, identifying optimization opportunities, or diagnosing recurring slowness without DB Console access.\ncompatibility: Requires SQL access with VIEWACTIVITY or VIEWACTIVITYREDACTED cluster privilege. Uses crdb_internal.statement_statistics (production-safe). Execution statistics fields are sampled and may be sparse.\n---\n\n# Profiling Statement Fingerprints\n\nAnalyzes historical statement performance patterns using aggregated SQL statistics to identify slow, resource-intensive, or error-prone query fingerprints. Uses `crdb_internal.statement_statistics` for time-windowed analysis of latency, CPU, contention, admission delays, and failure rates - entirely via SQL without requiring DB Console access.\n\n**Complement to triaging-live-sql-activity:** This skill analyzes historical patterns; for immediate triage of currently running queries, see [triaging-live-sql-activity](../triaging-live-sql-activity/SKILL.md).\n\n## When to Use This Skill\n\n- Identify slowest statement fingerprints over past hours/days/weeks\n- Find queries with high CPU consumption, contention, or admission waits\n- Investigate performance regressions or plan changes\n- Locate full table scans or missing indexes via index recommendations\n- Analyze resource consumption by application or database\n- SQL-only historical analysis without DB Console access\n\n**For immediate incident response:** Use [triaging-live-sql-activity](../triaging-live-sql-activity/SKILL.md) to triage currently running queries and cancel runaway work.\n**For transaction-level analysis:** Use [profiling-transaction-fingerprints](../profile-txn-fingerprints/SKILL.md) to analyze retry patterns, commit latency, and statement composition at the transaction boundary.\n**For background job monitoring:** Use [monitoring-background-jobs](../monitoring-background-jobs/SKILL.md) for long-running schema changes and automatic jobs excluded from statement statistics.\n\n## Prerequisites\n\n- SQL connection to CockroachDB cluster\n- `VIEWACTIVITY` or `VIEWACTIVITYREDACTED` cluster privilege for cluster-wide visibility\n- Statement statistics collection enabled (default): `sql.stats.automatic_collection.enabled = true`\n\n**Check collection status:**\n```sql\nSHOW CLUSTER SETTING sql.stats.automatic_collection.enabled;  -- Should return: true\n```\n\nSee [triaging-live-sql-activity permissions reference](../triaging-live-sql-activity/references/permissions.md) for RBAC setup (same privileges).\n\n## Core Concepts\n\n### Statement Fingerprints vs Live Queries\n\n**Statement fingerprint:** Normalized SQL pattern with parameterized constants (e.g., `SELECT * FROM users WHERE id = $1` vs `SELECT * FROM users WHERE id = 123`)\n\n**Key differences:**\n- **Time scope:** Historical hourly buckets vs real-time current state\n- **Granularity:** Aggregated pattern statistics vs individual execution instances\n\n### Time-Series Bucketing\n\n**aggregated_ts:** Hourly UTC buckets (e.g., `2026-02-21 14:00:00` = 14:00-14:59 executions)\n**Data retention:** Capped by row count, not time. `sql.stats.persisted_rows.max` (default 1,000,000) bounds the persisted statement+transaction rows; older buckets are compacted once the cap is reached. Effective wall-clock window depends on workload diversity.\n**Best practice:** Always filter by time window: `WHERE aggregated_ts > now() - INTERVAL '24 hours'`\n\n### Aggregated vs Sampled Metrics\n\n| Metric Category | JSON Path | Scope | Use Case |\n|-----------------|-----------|-------|----------|\n| **Aggregated** | `statistics.statistics.*` | All executions | Latency, row counts, execution counts |\n| **Sampled** | `statistics.execution_statistics.*` | Probabilistic sample governed by `sql.txn_stats.sample_rate` (default 0.01) | CPU, contention, admission wait, memory/disk |\n\n**Critical:** Always check sampled metrics presence: `WHERE (statistics->'execution_statistics'->>'cnt') IS NOT NULL`\n\n### JSON Field Extraction\n\n**Operators:**\n- `->`: Extract JSON object (returns JSON)\n- `->>`: Extract as text (returns text)\n- `::TYPE`: Cast to specific type\n\n**Examples:**\n```sql\nmetadata->>'db'                                              -- Database name\n(statistics->'statistics'->>'cnt')::INT                      -- Execution count\n(statistics->'statistics'->'runLat'->>'mean')::FLOAT8        -- Mean latency (seconds)\n(statistics->'execution_statistics'->'cpuSQLNanos'->>'mean')::FLOAT8 / 1e9  -- CPU (convert nanos to seconds)\n```\n\n**Units:** Latency = seconds, CPU/admission = nanoseconds (÷ 1e9), Memory/disk = bytes (÷ 1048576 for MB)\n\nSee [JSON field reference](references/json-field-reference.md) for complete schema.\n\n## Core Diagnostic Queries\n\n### Query 1: Top Statements by Mean Run Latency\n\n```sql\nSELECT\n  fingerprint_id,\n  metadata->>'db' AS database,\n  metadata->>'query' AS query_text,\n  (statistics->'statistics'->>'cnt')::INT AS execution_count,\n  (statistics->'statistics'->'runLat'->>'mean')::FLOAT8 AS mean_run_lat_seconds,\n  (statistics->'statistics'->'runLat'->>'max')::FLOAT8 AS max_run_lat_seconds,\n  (metadata->>'fullScan')::BOOL AS full_scan,\n  metadata->'index_recommendations' AS index_recommendations,\n  aggregated_ts\nFROM crdb_internal.statement_statistics\nWHERE aggregated_ts > now() - INTERVAL '24 hours'\n  AND (statistics->'statistics'->'runLat'->>'mean')::FLOAT8 > 1.0  -- > 1 second mean latency\nORDER BY (statistics->'statistics'->'runLat'->>'mean')::FLOAT8 DESC\nLIMIT 20;\n```\n\n**Focus:** Slowest queries; check `full_scan` and `index_recommendations` for optimization opportunities.\n\n### Query 2: Admission Control Impact\n\n```sql\nSELECT\n  fingerprint_id,\n  metadata->>'db' AS database,\n  metadata->>'query' AS query_text,\n  (statistics->'statistics'->>'cnt')::INT AS execution_count,\n  (statistics->'execution_statistics'->'admissionWaitTime'->>'mean')::FLOAT8 / 1e9 AS mean_admission_wait_seconds,\n  (statistics->'statistics'->'runLat'->>'mean')::FLOAT8 AS mean_run_lat_seconds,\n  aggregated_ts\nFROM crdb_internal.statement_statistics\nWHERE aggregated_ts > now() - INTERVAL '24 hours'\n  AND (statistics->'execution_statistics'->>'cnt') IS NOT NULL\n  AND (statistics->'execution_statistics'->'admissionWaitTime'->>'mean')::FLOAT8 > 0\nORDER BY (statistics->'execution_statistics'->'admissionWaitTime'->>'mean')::FLOAT8 DESC\nLIMIT 20;\n```\n\n**Interpretation:** High admission wait = cluster at resource limits (CPU, memory, I/O). Ratio > 1.0 (wait > runtime) indicates severe queueing.\n\n### Query 3: Plan Hash Diversity\n\n```sql\nSELECT\n  fingerprint_id,\n  metadata->>'db' AS database,\n  metadata->>'query' AS query_text,\n  COUNT(DISTINCT plan_hash) AS distinct_plan_count,\n  array_agg(DISTINCT plan_hash ORDER BY plan_hash) AS plan_hashes,\n  SUM((statistics->'statistics'->>'cnt')::INT) AS total_executions\nFROM crdb_internal.statement_statistics\nWHERE aggregated_ts > now() - INTERVAL '7 days'\nGROUP BY fingerprint_id, metadata->>'db', metadata->>'query'\nHAVING COUNT(DISTINCT plan_hash) > 1\nORDER BY COUNT(DISTINCT plan_hash) DESC, SUM((statistics->'statistics'->>'cnt')::INT) DESC\nLIMIT 20;\n```\n\n**Interpretation:** Multiple plans indicate instability from schema changes, statistics updates, or routing changes. Performance can vary significantly between plans.\n\n### Query 4: High Contention Statements\n\n```sql\nSELECT\n  fingerprint_id,\n  metadata->>'db' AS database,\n  metadata->>'app' AS application,\n  substring(metadata->>'query', 1, 150) AS query_preview,\n  (statistics->'statistics'->>'cnt')::INT AS execution_count,\n  (statistics->'execution_statistics'->'contentionTime'->>'mean')::FLOAT8 / 1e9 AS mean_contention_seconds,\n  ROUND(\n    ((statistics->'execution_statistics'->'contentionTime'->>'mean')::FLOAT8 / 1e9) /\n    NULLIF((statistics->'statistics'->'runLat'->>'mean')::FLOAT8, 0) * 100, 2\n  ) AS contention_pct_of_runtime,\n  aggregated_ts\nFROM crdb_internal.statement_statistics\nWHERE aggregated_ts > now() - INTERVAL '24 hours'\n  AND (statistics->'execution_statistics'->>'cnt') IS NOT NULL\n  AND (statistics->'execution_statistics'->'contentionTime'->>'mean')::FLOAT8 > 0\nORDER BY (statistics->'execution_statistics'->'contentionTime'->>'mean')::FLOAT8 DESC\nLIMIT 20;\n```\n\n**Interpretation:** >20% contention = transaction conflicts, hot row access. Remediate with batching, transaction boundary changes, or schema redesign.\n\n### Query 5: High CPU Consumers\n\n```sql\nSELECT\n  fingerprint_id,\n  metadata->>'db' AS database,\n  substring(metadata->>'query', 1, 150) AS query_preview,\n  (statistics->'execution_statistics'->'cpuSQLNanos'->>'mean')::FLOAT8 / 1e9 AS mean_cpu_seconds,\n  (statistics->'statistics'->>'cnt')::INT AS total_executions,\n  ROUND(\n    ((statistics->'execution_statistics'->'cpuSQLNanos'->>'mean')::FLOAT8 / 1e9) *\n    (statistics->'statistics'->>'cnt')::INT, 2\n  ) AS estimated_total_cpu_seconds,\n  (metadata->>'fullScan')::BOOL AS full_scan,\n  aggregated_ts\nFROM crdb_internal.statement_statistics\nWHERE aggregated_ts > now() - INTERVAL '24 hours'\n  AND (statistics->'execution_statistics'->>'cnt') IS NOT NULL\n  AND (statistics->'execution_statistics'->'cpuSQLNanos'->>'mean')::FLOAT8 > 0\nORDER BY estimated_total_cpu_seconds DESC\nLIMIT 20;\n```\n\n**Focus:** `estimated_total_cpu_seconds` shows cluster impact. High mean CPU often correlates with `full_scan = true`.\n\n### Query 6: Memory and Disk Spill Detection\n\n```sql\nSELECT\n  fingerprint_id,\n  metadata->>'db' AS database,\n  substring(metadata->>'query', 1, 150) AS query_preview,\n  (statistics->'execution_statistics'->'maxMemUsage'->>'mean')::FLOAT8 / 1048576 AS mean_mem_mb,\n  (statistics->'execution_statistics'->'maxMemUsage'->>'max')::FLOAT8 / 1048576 AS max_mem_mb,\n  (statistics->'execution_statistics'->'maxDiskUsage'->>'mean')::FLOAT8 / 1048576 AS mean_disk_mb,\n  (statistics->'execution_statistics'->'maxDiskUsage'->>'max')::FLOAT8 / 1048576 AS max_disk_mb,\n  metadata->>'stmtType' AS statement_type,\n  aggregated_ts\nFROM crdb_internal.statement_statistics\nWHERE aggregated_ts > now() - INTERVAL '24 hours'\n  AND (statistics->'execution_statistics'->>'cnt') IS NOT NULL\n  AND (statistics->'execution_statistics'->'maxDiskUsage'->>'mean')::FLOAT8 > 0  -- Has disk spills\nORDER BY (statistics->'execution_statistics'->'maxDiskUsage'->>'mean')::FLOAT8 DESC\nLIMIT 20;\n```\n\n**Interpretation:** Disk usage > 0 = memory spill (~100-1000x slower than in-memory). Common for large aggregations, sorts, hash joins. Fix with indexes or increased `sql.distsql.temp_storage.workmem`.\n\n### Query 7: Error-Prone Statements\n\n```sql\nSELECT\n  fingerprint_id,\n  metadata->>'db' AS database,\n  substring(metadata->>'query', 1, 150) AS query_preview,\n  (statistics->'statistics'->>'cnt')::INT AS total_executions,\n  COALESCE((statistics->'statistics'->>'failureCount')::INT, 0) AS failure_count,\n  ROUND(\n    COALESCE((statistics->'statistics'->>'failureCount')::INT, 0)::NUMERIC /\n    NULLIF((statistics->'statistics'->>'cnt')::INT, 0) * 100, 2\n  ) AS failure_rate_pct,\n  aggregated_ts\nFROM crdb_internal.statement_statistics\nWHERE aggregated_ts > now() - INTERVAL '24 hours'\n  AND (statistics->'statistics'->>'cnt')::INT > 10\n  AND COALESCE((statistics->'statistics'->>'failureCount')::INT, 0) > 0\nORDER BY failure_rate_pct DESC, failure_count DESC\nLIMIT 20;\n```\n\n**Common causes:** Constraint violations, query timeouts, transaction retry errors (40001), permission denied.\n\n## Common Workflows\n\n### Workflow 1: Slowness Investigation\n\n1. **Identify slow fingerprints:** Run Query 1 with 24h window, focus on `mean_run_lat_seconds > 5` and high execution counts\n2. **Check for full scans:** Filter `full_scan = true`, review `index_recommendations`\n3. **Correlate to applications:** Group by `metadata->>'app'`, contact teams with specific patterns\n4. **Cross-reference live activity:** If ongoing, use triaging-live-sql-activity to cancel runaway queries\n\n### Workflow 2: Contention Analysis\n\n1. **Find high-contention statements:** Run Query 4, focus on `contention_pct_of_runtime > 20%`\n2. **Check plan stability:** Run Query 3 for contending fingerprints (plan changes affect lock order)\n3. **Remediate:** Batch operations, use `SELECT FOR UPDATE`, partition hot tables, denormalize schema\n\n### Workflow 3: Admission Control Debugging\n\n1. **Identify admission waits:** Run Query 2, calculate wait ratio\n2. **Correlate with CPU:** Run Query 5 for same window, cross-reference fingerprint IDs\n3. **Analyze time patterns:** Group by `aggregated_ts` to find peak periods\n4. **Triage:** Short-term: spread batch jobs; Long-term: add capacity, optimize queries\n\n### Workflow 4: Memory Spill Investigation\n\n1. **Find spilling statements:** Run Query 6, focus on `max_disk_mb > 100`\n2. **Analyze patterns:** Identify large `GROUP BY`, `ORDER BY`, hash joins\n3. **Remediate:** Add indexes, increase workmem (with caution), rewrite queries, use materialized views\n\n## Safety Considerations\n\n**Read-only operations:** All queries are `SELECT` statements against production-approved `crdb_internal.statement_statistics`.\n\n**Performance impact:**\n\n| Consideration | Impact | Mitigation |\n|---------------|--------|------------|\n| Large table | Many rows with high statement diversity | Always use time filters and `LIMIT` |\n| JSON parsing | CPU overhead | Use specific time windows, avoid tight loops |\n| Broad windows | 7-day queries = more rows | Default to 24h; expand only when needed |\n\n**Privacy:** Use `VIEWACTIVITYREDACTED` to redact query constants in multi-tenant environments.\n\n## Troubleshooting\n\n| Issue | Cause | Fix |\n|-------|-------|-----|\n| Empty results | No data or stats collection disabled | Check `sql.stats.automatic_collection.enabled = true` |\n| `column does not exist` | JSON field typo or version mismatch | Verify field names; check CockroachDB version |\n| NULL in sampled metrics | Metric not sampled in bucket | Filter: `WHERE (statistics->'execution_statistics'->>'cnt') IS NOT NULL` |\n| Query text shows `<hidden>` | Using VIEWACTIVITYREDACTED | Expected; use VIEWACTIVITY if authorized |\n| \"invalid input syntax for type json\" | Malformed JSON path | Check operators: `->` for JSON, `->>` for text |\n| Very slow query | Large table, no time filter | Always add time window and LIMIT |\n| Empty `index_recommendations` | No recommendations or optimal | Normal if indexes exist |\n\n## Key Considerations\n\n- **Time windows:** Default to 24h; expand to 7d for trends\n- **Sampled metrics:** Not all executions captured; check sample size (`cnt`)\n- **JSON safety:** Use defensive NULL checks; handle type casting errors\n- **Privacy:** Use VIEWACTIVITYREDACTED in production\n- **Performance:** Always include time filters and LIMIT\n- **Complement to live triage:** Use together for complete coverage (historical + real-time)\n- **Data retention:** Bounded by the row-count cap `sql.stats.persisted_rows.max` (default 1,000,000), not a TTL; effective time window varies with workload diversity\n- **Plan instability:** Multiple plan hashes indicate optimizer/schema changes\n\n## References\n\n**Skill references:**\n- [JSON field schema and extraction](references/json-field-reference.md)\n- [Metrics catalog and units](references/metrics-and-units.md)\n- [SQL query variations](references/sql-query-variations.md)\n- [RBAC and privileges](../triaging-live-sql-activity/references/permissions.md) (shared with triaging-live-sql-activity)\n\n**Official CockroachDB Documentation:**\n- [crdb_internal](https://www.cockroachlabs.com/docs/stable/crdb-internal.html)\n- [Statements Page (DB Console)](https://www.cockroachlabs.com/docs/stable/ui-statements-page.html)\n- [Monitor and Analyze Transaction Contention](https://www.cockroachlabs.com/docs/stable/monitor-and-analyze-transaction-contention.html)\n- [VIEWACTIVITY privilege](https://www.cockroachlabs.com/docs/stable/security-reference/authorization.html#supported-privileges)\n\n**Related skills:**\n- [triaging-live-sql-activity](../triaging-live-sql-activity/SKILL.md) - For immediate triage of currently running queries\n- [profiling-transaction-fingerprints](../profile-txn-fingerprints/SKILL.md) - For transaction-level analysis including retry patterns and commit latency\n"
}