Statements Page

The Statements page helps you:

To view this page, click SQL Activity in the left-hand navigation of the CockroachDB Cloud Console and then click the Statements tab.

Search and filter

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

Search field

To search using the search field, type a string over Search Statements and press Enter. The list of statements is filtered by the string.

Date range

To search by date, click the date range selector and pick a date range that is within the time period. Click reset time to reset the date to the last hour.

It's possible to select a date range for which no statement statistics exist. 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.

Filter

To filter the statements, click the Filters field.

To filter by application, select App and choose one or more applications. When no application is selected internal statements are not displayed.

Note:
  • Internal queries are displayed under the $ internal app.
  • 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.

You can also filter by one or more databases (Database), SQL statement types (Statement Type), and for statement fingerprints that take longer than a specified time to run. To display only statements with queries that cause full table scans, click Only show statements that contain queries with full table scans.

The following screenshot shows the statements that contain the string rides for the movr application:

Movr rides statements

Statement statistics

Statistics are aggregated once an hour and organized by Aggregation Interval. Statistics between two hourly intervals belong to the nearest hour rounded down. For example, a statement execution ending at 1:50 would have its statistics aggregated in the 1:00-2:00 interval.

Statistics are persisted in statistics tables in the crdb_internal system catalog. The default retention period of the statistics tables is based on the number of rows up to 10 million records. When this threshold is reached, the oldest records are deleted.

To reset SQL statistics in the DB Console UI and crdb_internal system catalog, click clear SQL stats.

Note:

The diagnostics.sql_stat_reset.interval cluster setting no longer controls the DB Console UI or the persisted statistics tables. diagnostics.sql_stat_reset.interval still resets the node_statement_statistics and node_transaction_statistics in-memory statistics.

Example

This example command shows how to query the two most important JSON columns: metadata and statistics:

SELECT
  aggregated_ts,
  fingerprint_id,
  app_name,
  metadata -> 'query' AS statement_text,
  metadata -> 'stmtTyp' AS statement_type,
  metadata -> 'db' AS database_name,
  metadata -> 'distsql' AS is_distsql,
  metadata -> 'fullScan' AS has_full_scan,
  metadata -> 'vec' AS used_vec,
  statistics -> 'execution_statistics' -> 'contentionTime' -> 'mean' AS contention_time_mean,
  statistics -> 'statistics' -> 'cnt' AS execution_count,
  statistics -> 'statistics' -> 'firstAttemptCnt' AS number_first_attempts,
  statistics -> 'statistics' -> 'numRows' -> 'mean' AS number_rows_returned_mean,
  statistics -> 'statistics' -> 'rowsRead' -> 'mean' AS number_rows_read_mean,
  statistics -> 'statistics' -> 'runLat' -> 'mean' AS runtime_latecy_mean,
  sampled_plan
FROM crdb_internal.statement_statistics;

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) with underscores (_). This 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:

  • 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)

The preceding SQL statements have the fingerprint:

INSERT INTO new_order(product_id, customer_id, no_w_id) VALUES (_, _, _)

The following statements cannot be represented by the same fingerprint:

  • INSERT INTO orders(product_id, customer_id, transaction_id) VALUES (380, 11, 11098)
  • 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 ($1, 11, 11098)
  • 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, $3)

It is possible to see the same fingerprint listed multiple times in the following scenarios:

  • Statements with this fingerprint were executed by more than one application_name.
  • Statements with this fingerprint were executed both successfully and unsuccessfully.

Understand the Statements page

Use the Statements page to identify SQL statements that you want to troubleshoot. This might include statements that are experiencing high latencies, multiple retries, or execution failures. You can optionally create and retrieve diagnostics for these statements.

Tip:

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

Statements table

Click Column selector 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 Transaction Details page.
Aggregation Interval (UTC) The interval over which statistics are aggregated.

For example, if a statement is executed at 1:23PM it will fall in the 1:00PM - 2:00PM interval.
Execution Count Cumulative number of executions of statements with this fingerprint within the aggregation 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.
Rows Read Average number of rows read from disk while executing statements with this fingerprint within the aggregation interval).

The gray bar indicates the mean number of rows returned. The blue bar indicates one standard deviation from the mean. Hover over the bar to display exact values.
Bytes Read Aggregation of all bytes read from disk across all operators for statements with this fingerprint within the aggregation 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.
Rows Written Aggregation of all rows written to disk across all operators for statements with this fingerprint. This column is not displayed by default.

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.
Statement Time Average planning and execution time of statements with this statement fingerprint within the aggregation 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.
Contention Average time statements with this fingerprint were in contention with other transactions within the aggregation 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.
Max Memory Maximum memory used by a statement with this fingerprint at any time during its execution within the aggregation 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 aggregation 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 retries of statements with this fingerprint within the aggregation interval.
% of All Runtime How much time this statement fingerprint took to execute compared to all other statements that were executed within the time period. It is expressed as a percentage. The runtime is the mean execution latency multiplied by the execution count.
Regions/Nodes The regions and nodes on which statements with this fingerprint executed.

Regions/Nodes are not visible for CockroachDB Serverless (beta) clusters.
Diagnostics Activate and download diagnostics for this fingerprint. To activate, click the Activate button. The column displays the status of diagnostics collection (WAITING, READY, OR ERROR). When the status is READY, click Download bundle to download the most recent diagnostics bundle.

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 section of the Statement Details page.

Diagnostics is not enabled for CockroachDB Serverless (beta) clusters.

Statement Details page

Click a SQL statement fingerprint to open Statement Details. For each statement fingerprint, the details include:

The Statement Details page supports the search param aggregated_ts. If set, the page displays the statement statistics aggregated at that interval. If unset, the page displays the statement statistics aggregated over the date range specified on the Statements page.

Overview

The Overview section displays the SQL statement fingerprint and essential statistics:

Mean statement time is the cumulative time taken to execute statements with this fingerprint within the aggregation interval.

  • Planning time is the cumulative time taken by the planner to create an execution plan for statements with this fingerprint within the specified time interval.
  • Execution time is the cumulative time taken to execute statements with this fingerprint in the specified time interval.

Resource usage displays statistics about storage, memory, and network usage for the SQL statement fingerprint.

  • Mean rows/bytes read displays the mean average number of rows and bytes read from the storage layer for statements with this fingerprint within the aggregation interval .
  • Max memory usage displays the maximum memory used by a statement with this fingerprint at any time during its execution within the aggregation interval or specified time interval.
  • Network usage displays the amount of data transferred over the network for statements with this fingerprint within the aggregation interval. If this value is 0, the statement was executed on a single node.
  • Max scratch disk usage displays the maximum amount of data spilled to temporary storage on disk while executing statements with this fingerprint within the aggregation interval.

Statement details displays information about the execution of the statement.

  • Interval start time represents the start time of the statistics aggregation interval for a statement. For example, if a statement is executed at 1:23PM it will fall in the 1:00PM - 2:00PM time interval.
  • Nodes: the nodes on which the statements executed. Click the node ID to view node statistics.

    Nodes are not visible for CockroachDB Serverless (beta) clusters.
  • Regions: the regions on which the statements executed.

    Regions are not visible for CockroachDB Serverless (beta) clusters.
  • Database: the database on which the statements executed.
  • App: the name specified by the application_name session setting.
  • Failed?: whether the statement failed to execute.
  • Used cost-based optimizer?: whether the execution used the cost-based optimizer.
  • Distributed execution?: whether the execution was distributed.
  • Vectorized execution?: whether the execution used the vectorized execution engine.
  • Transaction type: the type of transaction (implicit or explicit).
  • Last execution time: when the statement was last executed.

Execution counts displays execution statistics for the SQL statement fingerprint.

  • First attempts: the cumulative number of first attempts at executing statements with this fingerprint within the aggregation interval.
  • Total executions: the total number of executions of statements with this fingerprint. It is calculated as the sum of first attempts and retries.
  • Retries: the cumulative number of retries of statements with this fingerprint within the aggregation interval.
  • Max Retries: the highest number of retries of a single statement with this fingerprint within the aggregation interval. For example, if three statements with the same fingerprint had to be retried 0, 1, and 5 times, then the Max Retries value for the fingerprint is 5.

Diagnostics

The Diagnostics section allows you to activate and download diagnostics for the SQL statement fingerprint.

Note:

The Diagnostics tab is not enabled for CockroachDB Serverless (beta) clusters.

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, and database schema which could be sensitive. Be careful when generating and sharing statement bundles.

Activate diagnostics collection and download bundles

CockroachDB Cloud Console Statements Page

To activate diagnostics collection, click the Activate diagnostics button.

A row with the activation time and collection status is added to the Statement diagnostics table. The statuses are:

  • WAITING indicates that a SQL statement matching the fingerprint has not yet been recorded.
  • ERROR indicates that the attempt at diagnostics collection failed.
  • READY indicates that the diagnostics have been collected. Click Download bundle Bundle (.zip) to download the diagnostics bundle.

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 page for a statement fingerprint, click the All statement diagnostics link.

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

Explain Plan

The Explain Plan section displays CockroachDB's statement plan for an explainable statement. You can use this information to optimize the query. For more information about plans, see EXPLAIN.

CockroachDB Cloud Console Statements Page

By default, the explain plan for each fingerprint is sampled every 5 minutes. You can change the interval with the sql.metrics.statement_details.plan_collection.period cluster setting. For example, to change the interval to 2 minutes, run the following SET CLUSTER SETTING command:

icon/buttons/copy
> SET CLUSTER SETTING sql.metrics.statement_details.plan_collection.period  = '2m0s';

Execution Stats

The Execution Stats section has three subsections:

  • Execution Latency by Phase displays the service latency of statements matching this fingerprint, broken down by phase (parse, plan, run, overhead), as well as the overall service latency. Overhead comprises the statements that remain after subtracting parse, plan, and run latencies from the overall latency. These might include fetching table descriptors that were not cached, or other background tasks required to execute the query.

  • Other Execution Statistics displays the following statistics.

    Statistic Description
    Rows Read The number of rows read by the statement. The gray bar indicates the mean number of rows read. The blue bar indicates one standard deviation from the mean.
    Disk Bytes Read The size of the data read by the statement. The gray bar indicates the mean number of bytes read. The blue bar indicates one standard deviation from the mean.
  • Stats by Node provides a breakdown of the number of statements of the selected fingerprint per gateway node. You can use this table to determine whether, for example, you are executing queries on a node that is far from the data you are requesting (see Optimize Statement Performance).

    Stats by Node are not visible for CockroachDB Serverless (beta) clusters.

See also

YesYes NoNo