profiling-transaction-fingerprints

What this skill does

# Profiling Transaction Fingerprints

Analyzes historical transaction performance patterns using aggregated SQL statistics to identify high-retry transactions, contention patterns, and commit latency issues. Uses `crdb_internal.transaction_statistics` for time-windowed analysis of retry behavior, commit latency, and statement composition - entirely via SQL without requiring DB Console access.

**Complement to profiling-statement-fingerprints:** This skill analyzes transaction-level patterns (groups of statements with retry behavior); for statement-level optimization, see [profiling-statement-fingerprints](../profiling-statement-fingerprints/SKILL.md).

**Complement to triaging-live-sql-activity:** This skill analyzes historical transaction patterns; for immediate triage of currently active transactions, see [triaging-live-sql-activity](../triaging-live-sql-activity/SKILL.md).

## When to Use This Skill

- Identify transactions with high retry counts
- Analyze commit latency trends for transaction fingerprints
- Find transactions with high contention at transaction boundary
- Understand statement composition of problematic transactions
- Investigate transaction retry storms or abort patterns
- SQL-only historical transaction analysis without DB Console access

**For immediate incident response:** Use [triaging-live-sql-activity](../triaging-live-sql-activity/SKILL.md) to triage currently active transactions and cancel runaway work.
**For statement-level optimization:** Use [profiling-statement-fingerprints](../profiling-statement-fingerprints/SKILL.md) to analyze individual query patterns.

## Prerequisites

- SQL connection to CockroachDB cluster
- `VIEWACTIVITY` or `VIEWACTIVITYREDACTED` cluster privilege for cluster-wide visibility
  - Same privilege requirements as profiling-statement-fingerprints
- Understanding of transaction performance concepts
- Transaction statistics collection enabled (default): `sql.stats.automatic_collection.enabled = true`

**Check transaction stats collection:**
```sql
SHOW CLUSTER SETTING sql.stats.automatic_collection.enabled;
-- Should return: true
```

See [triaging-live-sql-activity permissions reference](../triaging-live-sql-activity/references/permissions.md) for RBAC setup (same privileges).

## Core Concepts

### Transaction Fingerprints vs Live Transactions

**Transaction fingerprint:** Normalized transaction pattern grouping statements with parameterized constants.

**Key differences:**
- **Time scope:** Historical hourly buckets vs real-time current state
- **Granularity:** Aggregated retry/commit stats vs individual transaction instances
- **Relationship:** Transaction = collection of statement fingerprints

### 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 | Retries, commit latency, execution counts |
| **Sampled** | `statistics.execution_statistics.*` | Probabilistic sample governed by `sql.txn_stats.sample_rate` (default 0.01) | Contention, network, memory/disk |

**Critical:** Sampled metrics have `cnt` field showing sample size. Always check:
```sql
WHERE (statistics->'execution_statistics'->>'cnt') IS NOT NULL
```

### JSON Field Extraction

CockroachDB stores transaction metadata and statistics as JSONB. Use these operators:

**Operators:**
- `->`: Extract JSON object/value (returns JSON)
- `->>`: Extract as text (returns text)
- `::TYPE`: Cast to specific type
- `encode(fingerprint_id, 'hex')`: Convert binary fingerprint to hex string

**Transaction-specific examples:**
```sql
encode(fingerprint_id, 'hex') AS txn_fingerprint_id                     -- Hex encoding
(statistics->'statistics'->>'maxRetries')::INT                           -- Max retry count
(statistics->'statistics'->'retryLat'->>'mean')::FLOAT8                  -- Retry latency (seconds)
(statistics->'statistics'->'commitLat'->>'mean')::FLOAT8                 -- Commit latency (seconds)
(statistics->'statistics'->'svcLat'->>'mean')::FLOAT8                    -- Service latency (seconds)
metadata->'stmtFingerprintIDs' AS stmt_fingerprint_ids_json             -- Statement composition
```

**Units:**
- Latency fields: **seconds** (FLOAT8)
- CPU/contention: **nanoseconds** (divide by 1e9 for seconds)
- Memory/disk: **bytes** (consider / 1048576 for MB)

See [JSON field reference](references/json-field-reference.md) for complete schema.

### Statement Composition

**metadata.stmtFingerprintIDs:** JSONB array mapping transaction to constituent statements

**Use case:** Understand which statements compose high-retry transactions

**Cross-reference workflow:** Join transaction_statistics with statement_statistics on fingerprint IDs

**Example pattern:**
```sql
-- Extract statement fingerprint IDs from transaction
metadata -> 'stmtFingerprintIDs' AS stmt_ids

-- Use with jsonb_array_elements_text to expand and join
jsonb_array_elements_text(metadata->'stmtFingerprintIDs') AS stmt_fingerprint_id
```

## Core Diagnostic Queries

### Query 1: Top Transactions by Retries and Contention

```sql
-- Identify transactions with high retry counts and contention
SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  metadata->>'db' AS database,
  metadata->>'app' AS application,
  (statistics->'statistics'->>'cnt')::INT AS execution_count,
  (statistics->'statistics'->>'maxRetries')::INT AS max_retries,
  (statistics->'statistics'->'retryLat'->>'mean')::FLOAT8 AS mean_retry_lat_seconds,
  (statistics->'execution_statistics'->'contentionTime'->>'mean')::FLOAT8 / 1e9 AS mean_contention_seconds,
  aggregated_ts
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'statistics'->>'maxRetries')::INT > 0
ORDER BY (statistics->'statistics'->>'maxRetries')::INT DESC
LIMIT 20;
```

**Key columns:** `max_retries` shows maximum retry count; `mean_retry_lat_seconds` shows time spent in retries; `mean_contention_seconds` shows lock wait time.

**Interpretation:** High max_retries (>10) indicates transaction conflicts; correlate with contention to identify lock hotspots.

### Query 2: Statement Composition Analysis

```sql
-- Extract statement fingerprints for high-retry transactions
SELECT
  encode(t.fingerprint_id, 'hex') AS txn_fingerprint_id,
  t.metadata->>'app' AS application,
  (t.statistics->'statistics'->>'maxRetries')::INT AS max_retries,
  jsonb_array_length(t.metadata->'stmtFingerprintIDs') AS num_statements,
  t.metadata->'stmtFingerprintIDs' AS stmt_fingerprint_ids,
  t.aggregated_ts
FROM crdb_internal.transaction_statistics t
WHERE t.aggregated_ts > now() - INTERVAL '24 hours'
  AND (t.statistics->'statistics'->>'maxRetries')::INT > 10
  AND t.metadata->'stmtFingerprintIDs' IS NOT NULL
ORDER BY max_retries DESC
LIMIT 20;
```

**Key columns:** `num_statements` shows transaction complexity; `stmt_fingerprint_ids` contains statement IDs for cross-reference with statement_statistics.

**Use case:** Understand which statement combinations cause retries; use Query 7 to drill down to specific statements.

### Query 3: High Commit Latency Transactions

```sql
-- Find transactions with slow commit latency
SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  metadata->>'db' AS database,
  metadata->>'app' AS application,
  (statistics->'statistics'->>'cnt')::INT AS execution_count,
  (statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 AS mean_commit_lat_secon