Use the CockroachDB SQL Activity Page to Find Slow Queries (2026)

The CockroachDB SQL Activity page is your secret weapon for pinpointing performance bottlenecks, but most people use it by just staring at the "Duration" column, which is like trying to find a needle in a haystack by looking at the needle’s length.

Let’s look at a real-time example. Imagine you’re running a transactional workload, and you want to see what’s taking up the most time. You’d navigate to the DB Console, then to Activity -> Statements.

+------------------------------+-----------------------------------------------------------------+-----------+-----------+------------+
| Application/User             | Statement                                                       | Duration  | Execution | Rows Read  |
+------------------------------+-----------------------------------------------------------------+-----------+-----------+------------+
| app_user@localhost:38472     | INSERT INTO orders (customer_id, order_date, total_amount) VALUES ($1, $2, $3) | 150ms     | 1,200     | 0          |
| reporting_job@localhost:5432 | SELECT COUNT(*) FROM users WHERE signup_date > $1               | 300ms     | 50        | 1          |
| app_user@localhost:38472     | UPDATE products SET stock_level = stock_level - $1 WHERE product_id = $2 | 120ms     | 1,000     | 0          |
| reporting_job@localhost:5432 | SELECT AVG(amount) FROM transactions WHERE transaction_date BETWEEN $1 AND $2 | 500ms     | 25        | 1          |
+------------------------------+-----------------------------------------------------------------+-----------+-----------+------------+

This table shows queries that have recently executed. The Duration column gives you the average time for that specific query type. The Execution column shows how many times that query type has run. A query with a long duration but low execution count might be a one-off problem, while a query with a moderate duration and millions of executions can be far more impactful.

The real power comes from understanding the Fingerprint ID and the Execution count in relation to Duration. CockroachDB aggregates identical queries (with different bind variable values) into a single "fingerprint." This fingerprint ID is crucial for understanding the total impact of a query pattern.

Here’s the mental model:

Identify High-Impact Queries: Don’t just sort by Duration. Sort by Duration * Execution. This gives you the total time spent across all executions for that query fingerprint. A query running 100ms 1000 times (10 seconds total) is often worse than a query running 1 second once.
Deep Dive into Execution Details: Once you’ve found a high-impact query fingerprint, click on it. This opens a detailed view. Here, you’ll see Execution Plan details. This is where you find the actual SQL statement and its plan.
Analyze the Execution Plan: The plan shows how CockroachDB is executing the query. Look for:
- full scan: This means the database is reading every row in a table, which is usually bad for large tables.
- index scan (specifically index-only scan): This is generally good, as it only reads the necessary data from an index.
- join: Check the type of join and the columns being joined on.
- sort: Large sorts can be expensive.
Examine Rows Read vs. Rows Written: A query that reads millions of rows but only returns a few might be inefficient. Similarly, a query that writes a lot of data might be a candidate for optimization if it’s happening frequently.
Check Service Latency: This metric includes network time, queueing time, and execution time. A high Service Latency on a query that has a low Execution Time in the plan suggests network or contention issues.
Look at Execution Time vs. Service Latency: If Service Latency is much higher than Execution Time, it indicates the query is waiting for something else – perhaps locks, other transactions, or network round trips.

The SQL Activity page shows you the average duration for a query fingerprint. What it doesn’t immediately show you is the distribution of those durations. A query that averages 100ms could have 99% of executions under 10ms and one outlier taking 10 seconds, skewing the average dramatically. The Execution Statistics tab within the detailed query view will give you Min, Max, and p99 latency, which is where you’ll catch those extreme outliers.

You’ll find that many slow queries are caused by missing indexes, inefficient joins, or queries that scan large portions of tables unnecessarily. The key is to combine the aggregated view of the SQL Activity page with the detailed execution plan analysis to understand why a query is slow.

Once you’ve optimized your slow queries, the next challenge is often understanding how your schema design impacts query performance under load.