Aegis Example Workflows

This feature is in and is only available to enrolled organizations. To enroll your organization, contact your Cockroach Labs account team. This feature is subject to change.

The example workflows on this page show how to use Cockroach Aegis reports and recommendations alongside your own measurements on a test cluster. In each workflow, you generate a workload, examine the evidence Aegis produces, decide whether a change is appropriate, and check the observations after the workload or configuration changes. This page describes two workflows:

Understand transaction contention: run a small TPC-C workload, read Aegis’s analysis of hot-row contention, reduce concurrency, and compare the two phases.
Review and apply an index recommendation: create a table without a secondary index, generate a repeated full-scan query, review Aegis’s index recommendation, apply the index, and verify the effect.

Both workflows retrieve Aegis’s analysis through MCP and apply any changes through a separate SQL connection. Aegis can also explain a workload’s behavior without recommending a change, as the contention workflow shows. For information about how Aegis produces reports and recommendations, see Cockroach Aegis Overview. For MCP setup, see Connect through MCP.

Before you begin

Use a dedicated, non-production cluster with no other workload. These examples write data and consume cluster resources. The reference contention run used a single-node, 4-vCPU Advanced cluster, which is sufficient for that example. A production configuration would add nodes for fault tolerance and load distribution.
Connect the cluster to Aegis and keep the connector running. For instructions, see How do I connect a cluster?.
Connect an MCP client and verify instance and report access. For instructions, see Connect through MCP. The instructions shown here rely heavily on your agent using MCP.
Install the cockroach binary and check cockroach version. Each example specifies its workload commands. For more information, see cockroach workload.
Use a separate SQL credential with permission to create the test database, read and write its tables, and create an index when an example requires it. Keep the Aegis connector role at its recommended read-only grants; the workload and any applied changes use this separate credential.
Set PGURL to this test cluster’s connection URL, initially using defaultdb. Omit the password and retain sslmode=verify-full and the correct sslrootcert path. Supply the workload credential through PGPASSWORD using your normal secure credential-loading process, so that the password stays out of command arguments and shell history. For more information, see Connection parameters.
For a CockroachDB Cloud cluster, use the ccloud CLI to obtain these values. ccloud cluster sql --connection-url {cluster_name} returns a connection URL that already includes sslmode=verify-full and the path to the downloaded CA certificate, and ccloud cluster user create creates the workload SQL user. For more information, see and .

Transaction contention
Index recommendation

Understand transaction contention

In this example, you will run a small TPC-C workload and use Aegis reports to investigate transaction contention. You will compare the reports with workload measurements, reduce concurrency, and check whether later observations cover the change. The goal is to understand the evidence Aegis provides, and to understand the workflow.Check cockroach workload run tpcc --help before proceeding. Allow time for data initialization, two 12-minute measurement periods, and Aegis check-ins. The workload commands include a 30-second ramp before each measurement period. A check-in can complete without rewriting its reports, so allow for more than one check-in per phase.

Step 1. Prepare the test dataset

Confirm the SQL target and inspect the existing databases:

cockroach sql --url "$PGURL" --execute 'SELECT now(), current_user; SHOW DATABASES;'

Verify that the host in PGURL is the intended test cluster. Choose an unused database name, changing the following example if the name already exists, so that no existing database is overwritten:

export AEGIS_TPCC_DB='aegis_tpcc_contention'
AEGIS_TPCC_ARTIFACTS=$(mktemp -d)
export AEGIS_TPCC_ARTIFACTS

cockroach workload init tpcc \
  --db "$AEGIS_TPCC_DB" \
  --warehouses 10 --seed 1 --data-loader IMPORT \
  "$PGURL"

Initialize the dataset once. Both phases use it, and their transactions will change its data. Wait for initialization to finish successfully before continuing.TPC-C includes payment and new-order transactions that update shared rows. With 10 warehouses, payments update a small set of warehouse totals, and district updates touch a small set of totals and order counters. Concurrent transactions can end up waiting on these rows even when overall CPU utilization is low.For the underlying concepts, read Understand hotspots, particularly row hotspots, and Understand and avoid transaction contention.

Step 2. Capture an idle baseline

After initialization finishes, select the intended Aegis instance over MCP with list_instances and confirm its identity with get_instance. Retrieve get_reports and list_recommendations for that instance.Save the report bodies, check_in_id, and as_of, as well as each report’s changed, created_at, and version_id fields. Read the observation times inside the reports. Your baseline should describe the idle cluster after initialization. If it still describes initialization or an earlier workload, retain that context and wait for observations of the idle cluster.To get a fresh report, wait for a scheduled check-in or request one with Check in in the Aegis web UI. Retrieving reports through MCP leaves the check-in schedule unchanged. Check progress with get_instance before retrieving the completed snapshot.

Step 3. Run the higher-concurrency phase

Record the start time and run TPC-C with 32 active workers:

date -u '+%Y-%m-%dT%H:%M:%SZ'
cockroach workload run tpcc \
  --db "$AEGIS_TPCC_DB" --warehouses 10 --seed 1 \
  --workers 32 --active-workers 32 --conns 32 \
  --wait 0 --max-rate 100 --max-ops 75000 \
  --duration 12m --ramp 30s --display-every 30s \
  --histograms "$AEGIS_TPCC_ARTIFACTS/32w.json" \
  --conn-vars application_name=aegis-docs-tpcc-32w,statement_timeout=30s \
  "$PGURL"

The command disables TPC-C think time and caps workload operations at 100 per second. These operations are multi-statement transactions, so the rate differs from SQL statements per second, and the cap is an upper bound rather than a guaranteed throughput.

This is a contention exercise rather than a compliant TPC-C benchmark or a capacity-sizing test.

Watch the output, and stop the workload if it reports errors or the cluster shows unexpected resource pressure. Keep the load as specified; the goal is to see how Aegis gathers evidence, and getting a recommendation is optional. If your cluster can run this workload comfortably, you may not see a recommendation. Save the final totals and the end time after the command exits successfully.While the workload runs, allow several minutes of observations, then request a check-in or wait for a scheduled one. When it completes, retrieve and save the reports and recommendations through MCP, and confirm that the report bodies describe this application’s activity during the run.

Step 4. Interpret the contention evidence

Ask your agent to compare the reports with the baseline using a prompt like the following:

Use Aegis MCP to retrieve get_instance, get_reports, and list_recommendations
for {instance_id}. Compare with the saved baseline and the TPC-C run that
started at {start_time_utc}, using application_name aegis-docs-tpcc-32w.
Report check_in_id and as_of, each report's changed/created_at/version_id,
and the observation windows in its body, and confirm that they cover this
phase. Explain any evidence of hot rows, lock waits, serialization retries,
and resource pressure. Separate Aegis's findings from your own inferences
and from the workload runner's measurements. If there are no
recommendations, explain the report findings instead. This is a read-only
analysis: leave the cluster, the workload, and recommendation state
unchanged.

Replace {instance_id} with the Aegis instance ID and {start_time_utc} with the recorded UTC start time. Supply the saved baseline reports in the conversation.In the reference run, Aegis identified contention on warehouse and district rows, including warehouse payment totals and district order counters. Its cluster report showed CPU and disk headroom. The workload report identified serialization retries and explained why optimizer index suggestions were not promoted into recommendations: those suggestions targeted scan paths that were a small part of the cost in this synthetic workload.What follows are suggestions for interpreting the findings:

Contention on a small set of keys can occur at low overall CPU. Updates to a single hot row serialize on that row, so adding nodes or an index can address different bottlenecks.
Serialization retries are distinct from failed transactions. The reference workload may have reported zero terminal errors, while Aegis was likely to identify retry events. For more information, see Transaction retry errors.
An empty recommendation list can accompany a report with substantial analysis. Read the report for the explanation.
Verify the diagnosis with the cluster’s contention and statement-performance views, filtered by the phase’s application name and time window, following the instructions in Monitor and analyze transaction contention. Note that cached contention entries can persist after load stops, so pair them with a time window when reading current contention.

Step 5. Compare reduced concurrency

Wait for the first command to exit. Record the next start time, then repeat the run with 8 active workers:

date -u '+%Y-%m-%dT%H:%M:%SZ'
cockroach workload run tpcc \
  --db "$AEGIS_TPCC_DB" --warehouses 10 --seed 1 \
  --workers 32 --active-workers 8 --conns 32 \
  --wait 0 --max-rate 100 --max-ops 75000 \
  --duration 12m --ramp 30s --display-every 30s \
  --histograms "$AEGIS_TPCC_ARTIFACTS/8w.json" \
  --conn-vars application_name=aegis-docs-tpcc-8w,statement_timeout=30s \
  "$PGURL"

Keep --workers 32, the warehouse count, and the connection-pool limit unchanged. --active-workers 8 limits how many workers execute at once while preserving the existing workers’ warehouse assignments. The pool closes idle connections, so the live connection count varies under lower load. Run the two phases sequentially on the same dataset.Repeat the check-in and report-retrieval process during this phase. Ask the agent to compare with the higher-concurrency reports, substituting the new application name and start time. Compare measured throughput as well as latency: lower concurrency can reduce contention while also reducing the rate of completed work.

Reference measurements and limits

A reference run we performed produced the following workload-client totals. Each phase had a 12-minute measurement period after a 30-second ramp:

Measurement	32 active workers	8 active workers
Completed transactions	47,394	14,343
Transactions/sec	65.8	19.9
Overall transaction p99	1,140.9 ms	1,006.6 ms
New-order transaction p99	1,006.6 ms	738.2 ms
Terminal workload errors	0	0

Separate SQL observations showed normalized CPU around 22–23% during the higher-concurrency phase and around 8% during the lower-concurrency phase. Warehouse-update profiles showed average statement service time around 42–44 ms at higher concurrency and 6–7 ms at lower concurrency. These are observations from selected statistics windows rather than whole-run aggregates or Aegis measurements.Throughput fell substantially while the overall client p99 improved modestly. The runs were sequential on a changing dataset, used a laptop client, and were an exercise rather than an isolated capacity benchmark. Statement service latency, sampled contention time, and client transaction latency have different meanings and measurement windows, so compare like with like.

In this test, two completed check-ins during the lower-concurrency phase carried the earlier reports forward unchanged. Their as_of timestamps advanced, while both report bodies and created_at values still described the higher-concurrency phase. For this phase, the direct measurements above stand on their own, and Aegis’s analysis of it was unavailable.

If your reports are unchanged or do not cover the phase, record the gap. A new check-in ID indicates a new check-in, and each report’s changed and created_at fields show whether its content is new. Wait for an applicable report, or use your existing monitoring tools for the comparison.

Step 6. Check recovery and choose a next action

After the second workload exits, record its end time and verify through your normal monitoring tools that workload traffic subsides. Leave the connector running, allow time for idle observations, and retrieve reports after a later check-in.Confirm that the report contents cover the period after the stop time. In the reference test, direct SQL observations showed CPU returning to about 1.2%, while the first post-run MCP snapshot still carried the higher-concurrency reports. A later scheduled check-in produced reports describing an idle cluster with near-zero traffic and CPU around 1–3%, which confirmed recovery.Use what you learned to investigate a real workload’s conflicting transactions and application requirements. Before choosing a mitigation, see Reduce transaction contention and Troubleshoot lock contention.Retain the test logs and reports needed for your comparison. Remove the test data and deprovision dedicated test resources through your normal cleanup process when you no longer need them. Note that stopping the TPC-C runs via cockroach workload still leaves the database and the connector in place, and cloud cluster charges continue to accrue.

Review and apply an index recommendation

In this example, you will create an orders table without a secondary index and run a query that counts a customer’s pending orders. You use Aegis to identify the repeated full scan, review its recommendation, apply the index through a separate SQL connection, and compare the query plan and subsequent reports.This complements the contention example: an index lets a selective query skip unrelated rows, while a hot row remains contended regardless of indexing. Aegis recommendations depend on the observed workload and its context, so an optimizer suggestion is an input to a recommendation.

Step 1. Create a table without a secondary index

Using the separate workload SQL credential, open cockroach sql --url "$PGURL". Verify the intended test cluster and run SHOW DATABASES. The following example uses aegis_index_example; choose a different name throughout if that database already exists.

CREATE DATABASE aegis_index_example;
CREATE TABLE aegis_index_example.public.orders (
    id             INT8 PRIMARY KEY,
    customer_email STRING NOT NULL,
    product        STRING NOT NULL,
    amount         DECIMAL(10,2) NOT NULL,
    status         STRING NOT NULL,
    created_at     TIMESTAMPTZ NOT NULL
);

Exit the SQL shell and seed 200,000 synthetic rows in eight batches of 25,000. Run this block in Bash. The subshell stops on the first error; if a batch fails, inspect the partial dataset before proceeding.

(
  set -e
  for low in 1 25001 50001 75001 100001 125001 150001 175001; do
    high=$((low + 24999))
    cockroach sql --url "$PGURL" --execute "
      SET statement_timeout = '30s';
      INSERT INTO aegis_index_example.public.orders
      SELECT i,
             'user' || (i % 1000)::STRING || '@example.com',
             'product-' || (i % 50)::STRING,
             (i % 10000)::DECIMAL / 100,
             CASE ((i // 1000) % 4)
               WHEN 0 THEN 'pending' WHEN 1 THEN 'shipped'
               WHEN 2 THEN 'delivered' ELSE 'returned'
             END,
             '2026-01-01'::TIMESTAMPTZ + i * INTERVAL '1 minute'
      FROM generate_series($low, $high) AS g(i);"
  done
)

Reconnect with cockroach sql --url "$PGURL", collect table statistics, and verify the row count and indexes:

ANALYZE aegis_index_example.public.orders;
SELECT count(*) FROM aegis_index_example.public.orders;
SHOW INDEXES FROM aegis_index_example.public.orders;

Continue only if the table has 200,000 rows and only the orders_pkey index. SHOW INDEXES should return one row per column for that index. No index should yet exist that supports the customer_email and status filters. Remember to keep the Aegis connector role unchanged for this exercise.

Step 2. Capture the query plan and Aegis baseline

Run this read-only query with EXPLAIN ANALYZE, which executes the query and reports its plan and measurements:

EXPLAIN ANALYZE SELECT count(*)
                  FROM aegis_index_example.public.orders
                 WHERE customer_email = 'user42@example.com'
                   AND status = 'pending';

Save the output. Look for the index used, scan spans, rows decoded from KV, and execution time. In a reference run, the plan scanned all 200,000 rows of orders_pkey to find 50 matching orders. Three samples took 82–83 ms of execution time each.Through MCP, select the intended instance with list_instances and get_instance, then save get_reports and list_recommendations. Record report and recommendation IDs and timestamps. Existing reports might still describe an earlier workload; use their observation windows to establish the baseline.

Step 3. Generate observations and retrieve the recommendation

Exit the SQL shell. Run the following command in each of two terminals, with the same PGURL and securely loaded credential. Record the UTC start time.

date -u '+%Y-%m-%dT%H:%M:%SZ'
cockroach sql --url "$PGURL" --watch 500ms --execute "
  SET application_name = 'aegis-docs-index-before';
  SET statement_timeout = '30s';
  SELECT count(*) FROM aegis_index_example.public.orders
  WHERE customer_email = 'user42@example.com' AND status = 'pending';"

Each command repeatedly runs the same query and returns 50. This is a small read workload rather than a throughput benchmark. Monitor cluster resources and client errors. --watch runs until interrupted, so set a 20-minute timer, stay with the test, and stop both clients with Ctrl+C when you have the observations you need or the timer expires. Stop sooner if errors or unexpected resource pressure occur.Allow a scheduled Aegis check-in, or request Check in in the web UI while the workload is active. After it completes, retrieve get_reports and list_recommendations. Statistics collection and report updates can take time; leave cluster-wide collection intervals at their defaults.Compare each recommendation’s check_in_id and timestamps with the report snapshot. In this test, the new recommendation became visible while its check-in was still running, before get_reports returned that check-in’s completed reports. Report and recommendation reads are separate observations with their own timestamps.Ask your agent to review the evidence using a prompt like the following:

Use Aegis MCP to retrieve reports and recommendations for {instance_id}.
Find any recommendation about aegis_index_example.public.orders and the
aegis-docs-index-before workload that started at {start_time_utc}.
Report its identity, timestamps, proposed SQL, affected query, execution
count, rows scanned, latency, and the observation windows in the reports.
Explain why this index is useful and what storage and write costs it
introduces. Distinguish an optimizer suggestion from a recorded Aegis
recommendation. This is a read-only review: leave the cluster and
recommendation state unchanged.

Replace {instance_id} and {start_time_utc} with your instance ID and recorded UTC start time.In a reference run, Aegis recorded a recommendation citing 603 executions, about 72.6 ms mean statement service latency, and 200,000 rows read per execution. It identified a missing composite index and explained that the query would be covered without a STORING clause. It also noted the modest absolute workload and available cluster headroom. These were Aegis’s reported statistics, which differ from the three individual EXPLAIN ANALYZE measurements above.If there is no applicable recommendation, inspect the reports, workload activity, permissions, and freshness. Aegis might judge the improvement low priority or retain older reports. Wait for a recommendation that targets this query; an optimizer suggestion alone is an input to Aegis’s analysis, and this workflow reviews what Aegis recorded.

Step 4. Review and apply the index

Save the recommendation’s ID, status, evidence, and proposed SQL, then stop both baseline clients. Confirm that the recommendation targets your test database and matches the query you measured. Check for an equivalent existing index and review the index’s backfill and performance costs. For more information, see Secondary index best practices, Index performance trade-offs, and CREATE INDEX.For the reference dataset, the reviewed recommendation was the following additive change:

CREATE INDEX ON aegis_index_example.public.orders (customer_email, status);

After review and approval, execute the applicable statement through the separate workload SQL connection. Wait for it to complete and check its result before proceeding. If the client command fails or times out, check whether the schema change started before retrying. For more information, see Online schema changes.Verify the index and result:

SHOW INDEXES FROM aegis_index_example.public.orders;
SELECT count(*)
  FROM aegis_index_example.public.orders
 WHERE customer_email = 'user42@example.com' AND status = 'pending';

The reference run created orders_customer_email_status_idx, and the query still returned 50. You applied the index through your own SQL connection. Resolving the recommendation through MCP would change only its Aegis state.

Step 5. Verify the effect and revisit Aegis

Repeat the exact EXPLAIN ANALYZE query from Step 2 without forcing an index. Confirm that the optimizer selects the new index and scans a selective span rather than the whole table.The reference run produced these direct measurements on the same table:

Measurement	Before the index	After the index
Selected index	`orders_pkey`	`orders_customer_email_status_idx`
Rows decoded from KV	200,000	50
Bytes decoded from KV	17 MiB	3.1 KiB
Execution time, three samples	82–83 ms	0.70–0.73 ms
Query result	50	50

Repeat the two-client watch command with application_name = 'aegis-docs-index-after', leaving the query, data, and 500-ms interval unchanged. Set a five-minute timer for this phase and stop both clients when it expires. Record start and end times, and request or await another check-in covering activity after the index became available.Retrieve reports and recommendations again. Ask your agent to compare with the saved pre-change evidence, using the index-creation time and after-phase application name. Look for evidence that Aegis observed the schema change and the new query behavior. Check recommendation state separately, since Aegis updates its record during a later check-in. Fresh snapshot timestamps indicate a new check-in, and each report’s fields and observation windows show whether it contains a new diagnosis.In the reference run, the next requested check-in updated both reports. Aegis identified the index-creation event and completed schema change, reported that the full-scan condition was gone, and noted a latest cluster p99 SQL service latency around 0.84 ms. That cluster percentile is a different measurement from the per-statement mean and individual query execution times above.See the recommendation in the web UI. That reference-run screenshot uses the database name aegis_index_20260909 instead of aegis_index_example.Retain the recommendation, query plans, report snapshots, and timing notes. Use your normal change process for any real application rollout. Remove the example database and deprovision dedicated test resources when you no longer need them; stopping the query clients leaves both in place.

Tutorials

Get Started

Migrate Data

Develop and Connect

Manage Workloads

Monitor and Optimize

Aegis Example Workflows

Before you begin

Understand transaction contention

Step 1. Prepare the test dataset

Step 2. Capture an idle baseline

Step 3. Run the higher-concurrency phase

Step 4. Interpret the contention evidence

Step 5. Compare reduced concurrency

Reference measurements and limits

Step 6. Check recovery and choose a next action

Review and apply an index recommendation

Step 1. Create a table without a secondary index

Step 2. Capture the query plan and Aegis baseline

Step 3. Generate observations and retrieve the recommendation

Step 4. Review and apply the index

Step 5. Verify the effect and revisit Aegis

See also

​Before you begin

​Understand transaction contention

​Step 1. Prepare the test dataset

​Step 2. Capture an idle baseline

​Step 3. Run the higher-concurrency phase

​Step 4. Interpret the contention evidence

​Step 5. Compare reduced concurrency

​Reference measurements and limits

​Step 6. Check recovery and choose a next action

​Review and apply an index recommendation

​Step 1. Create a table without a secondary index

​Step 2. Capture the query plan and Aegis baseline

​Step 3. Generate observations and retrieve the recommendation

​Step 4. Review and apply the index

​Step 5. Verify the effect and revisit Aegis

​See also

Before you begin

Understand transaction contention

Step 1. Prepare the test dataset

Step 2. Capture an idle baseline

Step 3. Run the higher-concurrency phase

Step 4. Interpret the contention evidence

Step 5. Compare reduced concurrency

Reference measurements and limits

Step 6. Check recovery and choose a next action

Review and apply an index recommendation

Step 1. Create a table without a secondary index

Step 2. Capture the query plan and Aegis baseline

Step 3. Generate observations and retrieve the recommendation

Step 4. Review and apply the index

Step 5. Verify the effect and revisit Aegis

See also