profiling-statement-fingerprints

# Profiling Statement Fingerprints Analyzes 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. **Complement to triaging-live-sql-activity:** This skill analyzes historical patterns; for immediate triage of currently running queries, see [triaging-live-sql-activity](https://github.com/cockroachlabs/cockroachdb-skills/blob/main/skills/cockroachdb-observability-and-diagnostics/triaging-live-sql-activity/SKILL.md). ## When to Use This Skill - Identify slowest statement fingerprints over past hours/days/weeks - Find queries with high CPU consumption, contention, or admission waits - Investigate performance regressions or plan changes - Locate full table scans or missing indexes via index recommendations - Analyze resource consumption by application or database - SQL-only historical analysis without DB Console access **For immediate incident response:** Use [triaging-live-sql-activity](https://github.com/cockroachlabs/cockroachdb-skills/blob/main/skills/cockroachdb-observability-and-diagnostics/triaging-live-sql-activity/SKILL.md) to triage currently running queries and cancel runaway work. **For transaction-level analysis:** Use [profiling-transaction-fingerprints](https://github.com/cockroachlabs/cockroachdb-skills/blob/main/skills/cockroachdb-observability-and-diagnostics/profiling-transaction-fingerprints/SKILL.md) to analyze retry patterns, commit latency, and statement composition at the transaction boundary. **For background job monitoring:** Use [monitoring-background-jobs](https://github.com/cockroachlabs/cockroachdb-skills/blob/main/skills/cockroachdb-observability-and-diagnostics/monitoring-background-jobs/SKILL.md) for long-running schema changes and automatic jobs excluded from statement statistics. ## Prerequisites - SQL connection to CockroachDB cluster - `VIEWACTIVITY` or `VIEWACTIVITYREDACTED` cluster privilege for cluster-wide visibility - Statement statistics collection enabled (default): `sql.stats.automatic_collection.enabled = true` **Check collection status:** ```sql SHOW CLUSTER SETTING sql.stats.automatic_collection.enabled; -- Should return: true ``` See [triaging-live-sql-activity permissions reference](https://github.com/cockroachlabs/cockroachdb-skills/blob/main/skills/cockroachdb-observability-and-diagnostics/triaging-live-sql-activity/references/permissions.md) for RBAC setup (same privileges). ## Core Concepts ### Statement Fingerprints vs Live Queries **Statement fingerprint:** Normalized SQL pattern with parameterized constants (e.g., `SELECT * FROM users WHERE id = $1` vs `SELECT * FROM users WHERE id = 123`) **Key differences:** - **Time scope:** Historical hourly buckets vs real-time current state - **Granularity:** Aggregated pattern statistics vs individual execution instances ### Time-Series Bucketing **aggregated_ts:** Hourly UTC buckets (e.g., `2026-02-21 14:00:00` = 14:00-14:59 executions) **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. **Best practice:** Always filter by time window: `WHERE aggregated_ts > now() - INTERVAL '24 hours'` ### Aggregated vs Sampled Metrics | Metric Category | JSON Path | Scope | Use Case | |-----------------|-----------|-------|----------| | **Aggregated** | `statistics.statistics.*` | All executions | Latency, row counts, execution counts | | **Sampled** | `statistics.execution_statistics.*` | Probabilistic sample governed by `sql.txn_stats.sample_rate` (default 0.01) | CPU, contention, admission wait, memory/disk | **Critical:** Always check sampled metrics presence: `WHERE (statistics->'execution_statistics'->>'cnt') IS NOT NULL` ### JSON Field Extraction **Operators:** - `->`: Extract JSON object (returns JSON) - `->>`: Extract as text (returns text) - `::TYPE`: Cast to specific type **Examples:** ```sql metadata->>'db' -- Database name (statistics->'statistics'->>'cnt')::INT -- Execution count (statistics->'statistics'->'runLat'->>'mean')::FLOAT8 -- Mean latency (seconds) (statistics->'execution_statistics'->'cpuSQLNanos'->>'mean')::FLOAT8 / 1e9 -- CPU (convert nanos to seconds) ``` **Units:** Latency = seconds, CPU/admission = nanoseconds (÷ 1e9), Memory/disk = bytes (÷ 1048576 for MB) See [JSON field reference](references/json-field-reference.md) for complete schema. ## Core Diagnostic Queries ### Query 1: Top Statements by Mean Run Latency ```sql SELECT fingerprint_id, metadata->>'db' AS database, metadata->>'query' AS query_text, (statistics->'statistics'->>'cnt')::INT AS execution_count, (statistics->'statistics'->'runLat'->>'mean')::FLOAT8 AS mean_run_lat_seconds, (statistics->'statistics'->'runLat'->>'max')::FLOAT8 AS max_run_lat_seconds, (metadata->>'fullScan')::BOOL AS full_scan, metadata->'index_recommendations' AS index_recommendations, aggregated_ts FROM crdb_internal.statement_statistics WHERE aggregated_ts > now() - INTERVAL '24 hours' AND (statistics->'statistics'->'runLat'->>'mean')::FLOAT8 > 1.0 -- > 1 second mean latency ORDER BY (statistics->'statistics'->'runLat'->>'mean')::FLOAT8 DESC LIMIT 20; ``` **Focus:** Slowest queries; check `full_scan` and `index_recommendations` for optimization opportunities. ### Query 2: Admission Control Impact ```sql SELECT fingerprint_id, metadata->>'db' AS database, metadata->>'query' AS query_text, (statistics->'statistics'->>'cnt')::INT AS execution_count, (statistics->'execution_statistics'->'admissionWaitTime'->>'mean')::FLOAT8 / 1e9 AS mean_admission_wait_seconds, (statistics->'statistics'->'runLat'->>'mean')::FLOAT8 AS mean_run_lat_seconds, aggregated_ts FROM crdb_internal.statement_statistics WHERE aggregated_ts > now() - INTERVAL '24 hours' AND (statistics->'execution_statistics'->>'cnt') IS NOT NULL AND (statistics->'execution_statistics'->'admissionWaitTime'->>'mean')::FLOAT8 > 0 ORDER BY (statistics->'execution_statistics'->'admissionWaitTime'->>'mean')::FLOAT8 DESC LIMIT 20; ``` **Interpretation:** High admission wait = cluster at resource limits (CPU, memory, I/O). Ratio > 1.0 (wait > runtime) indicates severe queueing. ### Query 3: Plan Hash Diversity ```sql SELECT fingerprint_id, metadata->>'db' AS database, metadata->>'query' AS query_text, COUNT(DISTINCT plan_hash) AS distinct_plan_count, array_agg(DISTINCT plan_hash ORDER BY plan_hash) AS plan_hashes, SUM((statistics->'statistics'->>'cnt')::INT) AS total_executions FROM crdb_internal.statement_statistics WHERE aggregated_ts > now() - INTERVAL '7 days' GROUP BY fingerprint_id, metadata->>'db', metadata->>'query' HAVING COUNT(DISTINCT plan_hash) > 1 ORDER BY COUNT(DISTINCT plan_hash) DESC, SUM((statistics->'statistics'->>'cnt')::INT) DESC LIMIT 20; ``` **Interpretation:** Multiple plans indicate instability from schema changes, statistics updates, or routing changes. Performance can vary significantly between plans. ### Query 4: High Contention Statements ```sql SELECT fingerprint_id, metadata->>'db' AS database, metadata->>'app' AS application, substring(metadata->>'query', 1, 150) AS query_preview, (statistics->'statistics'->>'cnt')::INT AS execution_count, (statistics->'execution_statistics'->'contentionTime'->>'mean')::FLOAT8 / 1e9 AS mean_contention_seconds, ROUND( ((statistics->'execution_statistics'->'contentionTime'->>'mean')::FLOAT8 / 1e9) / NULLIF((statistics->'statistics'->'runLat'->>'mean')::FLOAT8, 0) * 100, 2 ) AS contention_pct_of_runtime, aggregated_ts FROM crdb_internal.statement_statistics WHERE aggregated_ts > now() - INTERVAL '24 hours' AND (statistics->'execution_statistics'->>'cnt') IS NOT NULL AND (statistics->'execution_statistics'->'contentionTime'->>'mean')::FLOAT8 > 0 ORDER BY (statistics->'execution_statistics'->'contentionTime'->>'mean')::FLOAT8 DESC LIMIT 20; ``` **Interpretation:** >20% contention = transaction conflicts, hot row access. Remediate with batching, transaction boundary changes, or schema redesign. ### Query 5: High CPU Consumers ```sql SELECT fingerprint_id, metadata->>'db' AS database, substring(metadata->>'query', 1, 150) AS query_preview, (statistics->'execution_statistics'->'cpuSQLNanos'->>'mean')::FLOAT8 / 1e9 AS mean_cpu_seconds, (statistics->'statistics'->>'cnt')::INT AS total_executions, ROUND( ((statistics->'execution_statistics'->'cpuSQLNanos'->>'mean')::FLOAT8 / 1e9) * (statistics->'statistics'->>'cnt')::INT, 2 ) AS estimated_total_cpu_seconds, (metadata->>'fullScan')::BOOL AS full_scan, aggregated_ts FROM crdb_internal.statement_statistics WHERE aggregated_ts > now() - INTERVAL '24 hours' AND (statistics->'execution_statistics'->>'cnt') IS NOT NULL AND (statistics->'execution_statistics'->'cpuSQLNanos'->>'mean')::FLOAT8 > 0 ORDER BY estimated_total_cpu_seconds DESC LIMIT 20; ``` **Focus:** `estimated_total_cpu_seconds` shows cluster impact. High mean CPU often correlates with `full_scan = true`. ### Query 6: Memory and Disk Spill Detection ```sql SELECT fingerprint_id, metadata->>'db' AS database, substring(metadata->>'query', 1, 150) AS query_preview, (statistics->'execution_statistics'->'maxMemUsage'->>'mean')::FLOAT8 / 1048576 AS mean_mem_mb, (statistics->'execution_statistics'->'maxMemUsage'->>'max')::FLOAT8 / 1048576 AS max_mem_mb, (statistics->'execution_statistics'->'maxDiskUsage'->>'mean')::FLOAT8 / 1048576 AS mean_disk_mb, (statistics->'execution_statistics'->'maxDiskUsage'->>'max')::FLOAT8 / 1048576 AS max_disk_mb, metadata->>'stmtType' AS statement_type, aggregated_ts FROM crdb_internal.statement_statistics WHERE aggregated_ts > now() - INTERVAL '24 hours' AND (statistics->'execution_statistics'->>'cnt') IS NOT NULL AND (statistics->'execution_statistics'->'maxDiskUsage'->>'mean')::FLOAT8 > 0 -- Has disk spills ORDER BY (statistics->'execution_statistics'->'maxDiskUsage'->>'mean')::FLOAT8 DESC LIMIT 20; ``` **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`. ### Query 7: Error-Prone Statements ```sql SELECT fingerprint_id, metadata->>'db' AS database, substring(metadata->>'query', 1, 150) AS query_preview, (statistics->'statistics'->>'cnt')::INT AS total_executions, COALESCE((statistics->'statistics'->>'failureCount')::INT, 0) AS failure_count, ROUND( COALESCE((statistics->'statistics'->>'failureCount')::INT, 0)::NUMERIC / NULLIF((statistics->'statistics'->>'cnt')::INT, 0) * 100, 2 ) AS failure_rate_pct, aggregated_ts FROM crdb_internal.statement_statistics WHERE aggregated_ts > now() - INTERVAL '24 hours' AND (statistics->'statistics'->>'cnt')::INT > 10 AND COALESCE((statistics->'statistics'->>'failureCount')::INT, 0) > 0