# Statements Page

Viewing the Statements page requires the Cluster Monitor, Cluster Operator, or Cluster Admin role.

The Statements page provides information about the execution of SQL statements in your cluster, using data in the cluster's `crdb_internal` system catalog. To view this page, select a cluster from the Clusters page, and click SQL Activity in the Monitoring section of the left side navigation. The Statements tab is selected.

It offers two views:

- Statement Fingerprints show information about completed SQL statements.
- Active Executions show information about SQL statements which are currently executing.

Choose a view by selecting the Statement Fingerprints or Active Executions radio button. The selection is retained when you switch between the Statements and Transactions tabs on the SQL Activity page.

Tip:

If you haven't yet executed any queries in the cluster as a user, this page will be blank.

## Statement Fingerprints view

The Statements Fingerprints view helps you:

- Identify frequently executed or high latency SQL statements.
- View SQL statement fingerprint details.
- Download SQL statement diagnostics for troubleshooting.

To view this page, click SQL Activity in the left-hand navigation of the CockroachDB Cloud Console. The Statements tab is selected. The Statement Fingerprints radio button is selected and the Statements table displays.

The following screenshot shows the statement fingerprint for `SELECT city, id FROM vehicles WHERE city = $1` while running the `movr` workload:

If you click the statement fingerprint in the Statements column, the Statement Fingerprint page displays.

## Active Executions view

The Active Executions view helps you:

- Understand and tune workload performance, particularly for long-running statements.

To display this view, click SQL Activity in the left-hand navigation of the CockroachDB Cloud Console. The Statements tab is selected. Click the Active Executions radio button. The Active Executions table displays.

Note:

When Auto Refresh is On, active executions are polled every 10 seconds. Faster-running executions will potentially disappear upon each refresh.

The following screenshot shows the active statement execution for `INSERT INTO users VALUES ($1, $2, $3, $4, $5)` while running the `movr` workload:

If you click the execution ID in the Statement Execution ID column, the Statement Execution details page displays.

### Refresh

To control refresh of the data on the Active Executions views of the SQL Activity Statements and Transactions pages, the following controls have been added:

- An Active Statements|Transactions As Of timestamp: Indicates when the last refresh was performed.
- A manual Refresh button: When clicked, refreshes data immediately.
- An Auto Refresh toggle: When toggled On (default), refreshes data immediately and then automatically every 10 seconds. When toggled Off, stops automatic data refresh. The toggle setting is shared by both the Statements and the Transactions pages. Changing the setting on one page changes it on the other page.

If Auto Refresh is toggled On, navigating to the Active Executions view on either the Statements page or Transactions page refreshes the data.

If Auto Refresh is toggled Off and the data has not been refreshed in over 10 minutes, there will be an alert under the controls similar to: `Your active statements|transactions data is 11 minutes old. Consider refreshing for the latest information.`

## Statement Fingerprints results

The statement fingerprints returned are determined by the selected Search Criteria.

By default, the Top `100` statement fingerprints By `% of All Runtime` for the `Past Hour` are returned.

1. To change the number of results returned, select `25`, `50`, `100`, or `500` from the Top dropdown. To return a larger number, select `More` and choose an option: `1000`, `5000`, `10000`.
2. To change the sort column, from the By dropdown, select a commonly sorted column: `% of All Runtime`, `SQL CPU Time`, `Contention Time`, `Execution Count`, `P99 Latency`, `Statement Time`. To sort by other columns, select `More` from the dropdown and choose an option: `Last Execution Time`, `Max Latency`,`Max Memory`, `Min Latency`, `Network`, `P50 Latency`, `P90 Latency`, `Retries`, `Rows Processed`.
   Note:
   The `More` options may increase the page loading time and are not generally recommended.
3. Select the Time Range.

4. Click Apply.

   The list of statements that satisfy the search criteria is displayed. The results can be further searched and filtered.

Warning:

Results are not automatically refreshed.

The actual time range of the statistics is displayed at the top right of the results, for example, `Showing aggregated stats from 20:00 to 21:59 (UTC)`. If you select a preset time interval, such as `Past Hour`, be aware that since the statistics displayed are not automatically refreshed, they may become stale. To see the latest statistics, update the Search Criteria and apply.

However, it may take up to 10 minutes for the most recent statistics to be included in the aggregated statistics. 10 minutes is the default for the `sql.stats.flush.interval` cluster setting. For in-memory statistics, directly query the following in-memory table: `crdb_internal.cluster_statement_statistics`.

### Time interval

To view statement fingerprints within a specific time interval, select a time interval from the selector at the top of the tab. The time interval field supports preset time intervals (1 Hour, 6 Hours, 1 Day, etc.) and custom time intervals. To select a custom time interval, click the time interval field and select Custom time interval. In the Start (UTC) and End (UTC) fields select or type a date and time.

Use the arrow buttons to cycle through previous and next time intervals. To select the most recent interval, click Now. When you select a time interval, the same interval is selected in the Metrics page.

CockroachDB persists statement statistics up to 1 million rows before the oldest row is deleted. The retention period of statistics is reduced the more active a workload is and the more distinct statement fingerprints there are.

Note:

It's possible to select an interval for which no statement statistics exist.

By default, the Statements page shows SQL statements from all applications and databases running on the cluster.

Note:

For the Statement Fingerprints view, the search and filter are applied after results are returned based on the Search Criteria.

To search using the search field:

1. Enter a string in the search box at the top of the tab. To search for exact terms in order, wrap the search string in quotes.

2. Press `Enter`.

   The list of statements is filtered by the string.

### Filter

To filter the statements:

1. Click the Filters field.

   - To filter by application, select App and select one or more applications.

     - Queries from the SQL shell are displayed under the `$ cockroach` app.
     - If you haven't set `application_name` in a client connection string, it appears as `unset`.

   - To filter by one or more databases (Database), SQL statement types (Statement Type), or nodes on which the statement ran (Node), click the field and select one or more checkboxes.

     The Statement Type values map to the CockroachDB statement types data definition language (DDL), data manipulation language (DML), data control language (DCL), and transaction control language (TCL).

   - To display only statement fingerprints that take longer than a specified time to run, specify the time and units.

   - To display only statement fingerprints with queries that cause full table scans, click Only show statements that contain queries with full table scans.

2. Click Apply.

The following screenshot shows the statements that contain the string `rides` for the `movr` application filtered by `Statement Type: DML`:

## Statement statistics

Statistics aggregation is controlled by the `sql.stats.aggregation.interval` cluster setting, set to 1 hour by default.

Aggregated statistics are flushed from memory to statistics tables in the `crdb_internal` system catalog every 10 minutes. The flushing interval is controlled by the `sql.stats.flush.interval` cluster setting.

The default retention period of the statistics tables is based on the number of rows up to 1 million records. When this threshold is reached, the oldest records are deleted. The `diagnostics.forced_sql_stat_reset.interval` cluster setting controls when persisted statistics are deleted only if the internal cleanup service experiences a failure.

If desired, admin users may reset SQL statistics in the DB Console UI and `crdb_internal` system catalog by clicking reset SQL stats. This link does not appear for non-admin users.

### Example

See View historical statement statistics and the sampled logical plan per fingerprint.

## SQL statement fingerprints

The Statements page displays SQL statement fingerprints.

A statement fingerprint represents one or more SQL statements by replacing literal values (e.g., numbers and strings) and placeholders with underscores (`_`). Lists with only literals or placeholders and similar expressions are shortened to their first item followed by `__more__`.

Fingerprints can help you quickly identify frequently executed SQL statements and their latencies.

For multiple SQL statements to be represented by a fingerprint, they must be identical aside from their literal values and placeholders.

These SQL statements:

- `INSERT INTO new_order(product_id, customer_id, transaction_id) VALUES (380, 11, 11098)`
- `INSERT INTO new_order(product_id, customer_id, transaction_id) VALUES (192, 891, 20)`
- `INSERT INTO new_order(product_id, customer_id, transaction_id) VALUES (784, 452, 78)`
- `INSERT INTO new_order(product_id, customer_id, transaction_id) VALUES ($1, $2, 11098)`
- `INSERT INTO new_order(product_id, customer_id, transaction_id) VALUES ($1, $2, 300)`
- `INSERT INTO new_order(product_id, customer_id, transaction_id) VALUES ($1, 11, 11098)`
- `INSERT INTO new_order(product_id, customer_id, transaction_id) VALUES ($1, $2, $3)`

have the fingerprint `INSERT INTO new_order(product_id, customer_id, transaction_id) VALUES (_, __more__)`

The following statements are not represented by the preceding fingerprint:

- `INSERT INTO new_order(product_id, customer_id, transaction_id, item_count) VALUES (380, 11, 11098, 1)`;
- `INSERT INTO new_order(product_id, customer_id, transaction_id, item_count) VALUES (192, 891, 20, 2)`;
- `INSERT INTO new_order(product_id, customer_id, transaction_id, item_count) VALUES (784, 452, 78, 3)`;

Instead, they have the fingerprint `INSERT INTO new_order(product_id, customer_id, transaction_id, item_count) VALUES (_, __more__)`

It is possible to see the same fingerprint listed multiple times when statements with this fingerprint were executed by more than one `application_name`.

## Statements table

Click Columns to select the columns to display in the table.

The Statements table gives details for each SQL statement fingerprint:

Column | Description
--- | ---
Statements | SQL statement fingerprint. To view additional details, click the SQL statement fingerprint to open its Statement Fingerprint page. Statement fingerprints are displayed per application rather than grouped into a single fingerprint. This may result in multiple rows for the same statement fingerprint, one per application name.
Execution Count | Cumulative number of executions of statements with this fingerprint within the time interval. The bar indicates the ratio of runtime success (gray) to retries (red) for the SQL statement fingerprint.
Database | The database in which the statement was executed.
Application Name | The name specified by the `application_name` session setting.
Statement Time | Average planning and execution time of statements with this statement fingerprint within the time interval. The gray bar indicates the mean latency. The blue bar indicates one standard deviation from the mean. Hover over the bar to display exact values.
% of All Runtime | The percentage of execution time taken by this statement fingerprint compared to all other statements executed within the time period, including those not displayed. Runtime is calculated as the mean execution latency multiplied by the execution count. Note: The sum of the values in this column may not equal 100%. Each fingerprint's percentage is calculated by dividing the fingerprint's runtime by the sum of the runtimes for all statement fingerprints in the time interval. "All statement fingerprints" means all user statement fingerprints (not only those displayed by the search criteria), as well as internal statement fingerprints that are never included in the displayed result set. The search criteria are applied after the `% of All Runtime` calculation.
Contention Time | Average time statements with this fingerprint were in contention with other transactions within the time interval. The gray bar indicates mean contention time. The blue bar indicates one standard deviation from the mean. Hover over the bar to display exact values.
SQL CPU Time | Average SQL CPU time spent executing within the specified time interval. It does not include SQL planning time or KV execution time. The gray bar indicates mean SQL CPU time. The blue bar indicates one standard deviation from the mean. The SQL CPU time includes time spent in the SQL layer. It does not include time spent in the storage layer.
Min Latency | The lowest latency value for all statement executions with this fingerprint.
Max Latency | The highest latency value for all statement executions with this fingerprint.
Rows Processed | Average number of rows read and written while executing statements with this fingerprint within the time interval.
Bytes Read | Aggregation of all bytes read from disk across all operators for statements with this fingerprint within the time interval. The gray bar indicates the mean number of bytes read from disk. The blue bar indicates one standard deviation from the mean. Hover over the bar to display exact values.
Max Memory | Maximum memory used by a statement with this fingerprint at any time during its execution within the time interval. The gray bar indicates the average max memory usage. The blue bar indicates one standard deviation from the mean. Hover over the bar to display exact values.
Network | Amount of data transferred over the network for statements with this fingerprint within the time interval. If this value is 0, the statement was executed on a single node. The gray bar indicates the mean number of bytes sent over the network. The blue bar indicates one standard deviation from the mean. Hover over the bar to display exact values.
Retries | Cumulative number of automatic (internal) retries by CockroachDB of statements with this fingerprint within the time interval.
Regions/Nodes | The regions and nodes on which statements with this fingerprint executed. Nodes are not visible for CockroachDB Basic clusters or for clusters that are not multi-region.
Last Execution Time (UTC) | The timestamp when the statement was last executed.
Statement Fingerprint ID | The ID of the statement fingerprint.
Diagnostics | Activate and download diagnostics for this fingerprint. To activate, click the Activate button. The Activate statement diagnostics dialog displays. After you complete the dialog, the column displays the status of diagnostics collection (WAITING, READY, or ERROR). Click and select a bundle to download or select Cancel request to cancel diagnostics bundle collection. Statements are periodically cleared from the Statements page based on the start time. To access the full history of diagnostics for the fingerprint, see the Diagnostics tab of the Statement Details page.

Note:

To obtain the execution statistics, CockroachDB samples a percentage of the executions. If you see `no samples` displayed in the Contention, Max Memory, or Network columns, there are two possibilities: - Your statement executed successfully but wasn't sampled because there were too few executions of the statement. - Your statement has failed (the most likely case). You can confirm by clicking the statement and viewing the value for Failure Count.

To view statement details, click a SQL statement fingerprint in the Statements column to open the Statement Fingerprint page.

## Statement Fingerprint page

The details displayed on the Statement Fingerprint page reflect the time interval selected on the Statements page and the application name and database specified in the selected row of the Statements table.

### Overview

The Overview section displays the SQL statement fingerprint and execution attributes:

Attribute | Description
--- | ---
Nodes | The nodes on which the statements executed. Click a node ID to view node statistics. Nodes are not displayed for CockroachDB Standard clusters.
Regions | The regions on which the statements executed. Regions are not displayed for CockroachDB Standard clusters.
Database | The database on which the statements executed.
Application Name | The name specified by the `application_name` session setting. Click the name to view all statements run by that application.
Fingerprint ID | The ID of the statement fingerprint in hexadecimal format. It may be used to query the `crdb_internal.statement_statistics` table.
Failure Count | The total number of failed executions of this statement fingerprint.
Full scan? | Whether the execution performed a full scan of the table.
Vectorized execution? | Whether the execution used the vectorized execution engine.
Transaction type | The type of transaction (implicit or explicit).
Last execution time | The timestamp when the statement was last executed.

The Overview section also displays the SQL statement fingerprint statistics that correspond to the charts below:

Statistic | Description
--- | ---
Statement Time | The time taken by the planner to create an execution plan and for CockroachDB to execute statements.
Rows Processed | The total number of rows read and written.
Execution Retries | The number of retries.
Execution Count | The total number of executions. It is calculated as the sum of first attempts and retries.
Contention Time | The amount of time spent waiting for resources. For more information about contention, see Understanding and avoiding transaction contention.
SQL CPU Time | The amount of SQL CPU time spent executing the statement. The SQL CPU time represents the time spent and work done within SQL execution operators. It does not include SQL planning time or KV execution time.
Client Wait Time | The time spent waiting for the client to send the statement while holding the transaction open. A high wait time indicates that you should revisit the entire transaction and batch your statements.

The following screenshot shows the statement fingerprint of the query described in Use the right index:

#### Insights

The Insights table is displayed when CockroachDB has detected a problem with the statement fingerprint.

- Insights: Provides the Workload Insight type.
- Details: Provides a description and possible recommendation.
- Latest Execution ID: The ID of the latest statement execution. To display the details of the statement execution, click the ID.

The following screenshot shows the insights of the statement fingerprint illustrated in Overview:

#### Charts

Charts following the execution attributes display statement fingerprint statistics:

Statistic | Description
--- | ---
Statement Time | The time taken by the planner to create an execution plan and for CockroachDB to execute statements.
Rows Processed | The total number of rows read and written.
Execution Retries | The number of retries.
Execution Count | The total number of executions. It is calculated as the sum of first attempts and retries.
Contention Time | The amount of time spent waiting for resources. For more information about contention, see Understanding and avoiding transaction contention.
SQL CPU Time | The amount of SQL CPU time spent executing the statement. The SQL CPU time represents the time spent and work done within SQL execution operators. It does not include SQL planning time or KV execution time.
Client Wait Time | The time spent waiting for the client to send the statement while holding the transaction open. A high wait time indicates that you should revisit the entire transaction and batch your statements.

The following charts summarize the executions of the statement fingerprint illustrated in Overview:

### Explain Plans

The Explain Plans tab displays statement plans for an explainable statement in the selected time interval. You can use this information to optimize the query. For more information about plans, see `EXPLAIN`.

The following screenshot shows an execution of the query discussed in Overview:

The plan table shows the following details:

Column | Description
--- | ---
Plan Gist | A sequence of bytes representing the flattened tree of operators and operator-specific metadata of the statement plan.
Used Indexes | The table indexes used by the plan. To see table details, click on the table name. To see index details, click on the index name.
Insights | The number of insights for the plan. To configure when to trigger insights, see Schema insights settings.
Last Execution Time | The timestamp when the statement was last executed.
Average Execution Time | The average execution time for all the executions of the plan.
Execution Count | The number of times the plan was executed.
Average Rows Read | The average number of rows read when the plan was executed.
Full Scan | Whether the execution performed a full scan of the table.
Min Latency | The lowest latency value for all statement executions with this Explain Plan.
Max Latency | The highest latency value for all statement executions with this Explain Plan.
Distributed | Whether the execution was distributed.
Vectorized | Whether the execution used the vectorized execution engine.

To display the plan that was executed, click the plan gist. For the plan gist `AgHUAQIABQAAAAHYAQIAiA...`, the following plan displays:

#### Insights

The plan table displays the number of insights related to the plan. If a plan has at least 1 insight, when you click the plan gist, a table of insights that describe how to improve the performance will follow the plan.

The following screenshot shows 1 insight found after running the query discussed in Overview 6 or more times:

Note:

CockroachDB uses the threshold of 6 executions before offering an insight because it assumes that you are no longer merely experimenting with a query at that point.

In this case the insight is recommending that you create an index on the `start_time` column of the `rides` table and storing the `rider_id`.

If you click Create Index, a confirmation dialog displays a warning about the cost of online schema changes and a button to copy the SQL statement for later execution in a SQL client.

If you click Apply to create the index and then execute the statement again, the Explain Plans tab will show that the second execution (in this case at `19:40`), which uses the index and has no insight, takes less time than the first 6 executions.

### Diagnostics

The Diagnostics tab allows you to activate and download diagnostics for a SQL statement fingerprint.

Note:

The Diagnostics tab is only visible to `admin` users or SQL users with the `VIEWACTIVITY` system privilege. The tab is not visible for roles with the `VIEWACTIVITYREDACTED` system privilege (or the legacy `VIEWACTIVITYREDACTED` role option) defined.

When you activate diagnostics for a fingerprint, CockroachDB waits for the next SQL query that matches this fingerprint to be run on any node. On the next match, information about the SQL statement is written to a diagnostics bundle that you can download. This bundle consists of statement traces in various formats (including a JSON file that can be imported to Jaeger), a physical query plan, execution statistics, and other information about the query. The bundle contents are identical to those produced by `EXPLAIN ANALYZE (DEBUG)`. You can use the information collected in the bundle to diagnose problematic SQL statements, such as slow queries. We recommend that you share the diagnostics bundle with our support team, which can help you interpret the results.

Tip:

Diagnostics will be collected a maximum of N times for a given activated fingerprint where N is the number of nodes in your cluster.

Warning:
Statement bundles can contain unredacted user data including histograms and placeholders that contain real data samples, statements that have real data which can contain PII information, and database schema that could be sensitive. Be careful when generating and sharing statement bundles.
In CockroachDB v24.3.1 and later, `admin` users or SQL users with the `VIEWACTIVITY` system privilege can choose to redact user data from the Activate statement diagnostics dialog.
To allow or disallow a role from seeing statements diagnostics bundles, set the `VIEWACTIVITYREDACTED` role option.
In CockroachDB v21.2.x, v22.1.0 to v22.1.16, v22.2.0 to v22.2.6, non-admin SQL users with an authenticated HTTP session could download statement diagnostic bundles given a bundle URL from the DB Console or the `EXPLAIN ANALYZE (DEBUG)` statement with a valid HTTP session cookie. This has been resolved in v22.1.17 and v22.2.7. For more information, see the Technical Advisory A99049.

#### Activate diagnostics collection and download bundles

Warning:

Collecting diagnostics has an impact on performance. All executions of the statement fingerprint will run slower until diagnostics are collected.

To activate diagnostics collection:

1. Click the Activate diagnostics button. The Activate statement diagnostics dialog displays.

2. Choose whether to:

   1. trace and collect diagnostics at the default sampled rate of 1% (or specify a different rate) when the statement execution latency exceeds the default time of 100 milliseconds (or specify a different time), or
   2. trace and collect diagnostics on the next statement execution.

3. Choose whether to collect diagnostics:

   1. For all plan gists, or
   2. For a particular plan gist (select one from the dropdown list).

4. Choose whether the request should expire after 15 minutes, or after a different time, or disable automatic expiration by deselecting the checkbox. Executions of the same statement fingerprint will run slower while diagnostics are activated, so it is recommended to set an expiration time if collecting according to a latency threshold.

5. Choose to Redact sensitive user data, such as histograms, placeholders, statements, and traces that have real data which can contain PII information.

6. Click Activate.

When the statement fingerprint is executed according to the statement diagnostic options selected, a row with the activation time and collection status is added to the Statement diagnostics table.

The collection status values are:

- READY: indicates that the diagnostics have been collected. To download the diagnostics bundle, click Bundle (.zip).
- WAITING: indicates that a SQL statement matching the fingerprint has not yet been recorded. To cancel diagnostics collection, click the Cancel request button.
- ERROR: indicates that the attempt at diagnostics collection failed.

#### View and download diagnostic bundles for all statement fingerprints

Although fingerprints are periodically cleared from the Statements page, all diagnostics bundles are preserved. To view and download diagnostic bundles for all statement fingerprints, do one of the following:

- On the Diagnostics tab for a statement fingerprint, click the All statement diagnostics link.

Click Bundle (.zip) to download any diagnostics bundle.

## Active Executions table

Click Columns to select the columns to display in the table.

Column | Description
--- | ---
Statement Execution ID | The execution ID of the statement execution.
Statement Execution | The SQL statement that was executed.
Status | The status of the execution: `Preparing`, `Waiting`, or `Executing`.
Start Time (UTC) | The timestamp when the execution started.
Time Spent Waiting | The time the execution spent waiting and experiencing lock contention.
Isolation Level | The isolation level used for the statement execution.
Application | The name specified by the `application_name` session setting.

To view details of an active statement execution, click an execution ID in the Statement Execution ID column to open the Statement Execution details page.

## Statement execution details page

The statement execution details page provides the following details on the statement execution.

- Start Time (UTC): The timestamp when the execution started.
- Elapsed Time: The time elapsed since the execution started.
- Status: The status of the execution: `Preparing`, `Waiting`, or `Executing`.
- Full Scan: Whether the execution performed a full scan of the table.
- Application Name: The name specified by the `application_name` session setting.
- User Name: The name of the user running the statement.
- Client Address: The IP address and port of the client that opened the session in which the statement is running.
- Session ID: Link to the session in which the transaction is running.
- Transaction Execution ID: Link to the ID of the transaction in which the statement is executing.

If a statement execution is waiting, the statement execution details are followed by Contention Insights and details of the statement execution on which the blocked statement execution is waiting. For more information about contention, see Understanding and avoiding transaction contention.

## See also

- Troubleshoot Query Behavior
- Transaction retries
- Optimize Statement Performance
- Support Resources
- Raw Status Endpoints
- Transactions Page