Skip to main content
On a , you must be an or a SQL user with the or (or the legacy VIEWACTIVITY ) to access this area of the DB Console. Refer to .
The Insights page exposes problems that CockroachDB has detected in your workloads and schemas. The page also offers recommendations to improve the performance of your workloads. These are called insights in the DB Console. The Insights page helps you:
  • Identify SQL statements with , , or .
  • Identify that should be created, altered, replaced, or dropped to improve performance.

​
Workload Insights tab

The Workload Insights tab displays insights related to and executions.

​
Transaction Executions view

To display this view, click Insights in the left-hand navigation of the DB Console and select Workload Insights > Transaction Executions. The Transaction Executions view provides an overview of all that have been flagged with insights.
The rows in this page are populated from the and crdb_internal.cluster_txn_execution_insights tables.
  • The results displayed in the Transaction Executions view will be available as long as a corresponding row in the crdb_internal.transaction_contention_events or crdb_internal.cluster_txn_execution_insights tables exists. The rows in crdb_internal.transaction_contention_events on each node must use less space than sql.contention.event_store.capacity, and the rows in crdb_internal.transaction_contention_events cannot exceed sql.insights.execution_insights_capacity.
  • The default tracing behavior captures a small percent of transactions so not all contention events will be recorded. When investigating , you can set the to always capture contention events.
The transaction insights table has the following columns:

​
Transaction Execution details

The transaction execution details view provides more details on a transaction execution insight.
  • Start Time: The timestamp when the transaction execution started.
  • End Time: The timestamp when the transaction execution ended.
  • Elapsed Time: The time that elapsed during transaction execution.
  • CPU Time: The amount of CPU time spent executing the transaction. The CPU time represents the time spent and work done within SQL execution operators. The CPU time includes time spent in the . It does not include time spent in the .
  • Rows Read: The total number of rows read by the transaction execution.
  • Rows Written: The total number of rows written by the transaction execution.
  • Priority: The of the transaction execution.
  • Number of Retries: The total number of retries of the transaction.
  • Session ID: The ID of the the transaction was executed from.
  • Application: The name specified by the .
  • Transaction Fingerprint ID: The fingerprint ID of the transaction execution.
The Insights column shows the insight type. The Details column provides details on the insight.

​
Transaction with ID {transaction ID} waited on

This section provides details of the transaction executions that block the transaction ID flagged with High Contention:
  • Transaction Execution ID: The execution ID of the blocking transaction execution.
  • Transaction Fingerprint ID: The transaction fingerprint ID of the blocking transaction execution.
  • Statement Waiting Execution ID: The execution ID of the waiting statement.
  • Statement Waiting Fingerprint ID: The statement fingerprint ID of the waiting statement.
  • Transaction Execution: The queries attempted in the transaction.
  • Contention Start Time (UTC): The timestamp at which was detected for the transaction.
  • Contention Time: The time that transactions with this execution ID were with other transactions within the specified time interval.
  • Schema Name: The name of the contended schema.
  • Database Name: The name of the contended database.
  • Table Name: The name of the contended table.
  • Index Name: The name of the contended index.

​
Statement Executions view

The Statement Executions view provides an overview of all that have been flagged with insights. To display this view, click Insights in the left-hand navigation of the DB Console and select Workload Insights > Statement Executions.
The rows in this page are populated from the table.
  • The results displayed on the Statement Executions view will be available as long as the number of rows in each node is less than the .
  • The default tracing behavior captures a small percent of transactions, so not all contention events will be recorded. When investigating transaction contention, you can set the sql.trace.txn.enable_threshold cluster setting to always capture contention events.
Click Columns to select the columns to display in the table. The statement insights table has the following columns available:

​
Statement Execution details

The statement execution details view provides more details on a statement execution insight.
  • Start Time: The timestamp when the statement execution started.
  • End Time: The timestamp when the statement execution ended.
  • Elapsed Time: The time that elapsed during statement execution.
  • CPU Time: The amount of CPU time spent executing the statement. The CPU time represents the time spent and work done within SQL execution operators. The CPU time includes time spent in the . It does not include time spent in the .
  • Rows Read: The total number of rows read by the statement execution.
  • Rows Written: The total number of rows written by the statement execution.
  • Transaction Priority: The of the transaction for the statement execution.
  • Full Scan: Whether the execution performed a full scan of the table.
  • Transaction Retries: The total number of retries of the transaction for the statement execution.
  • Session ID: The ID of the the statement was executed from.
  • Transaction Fingerprint ID: The ID of the transaction fingerprint for the statement execution.
  • Transaction Execution ID: The ID of the transaction execution for the statement execution.
  • Statement Fingerprint ID: The fingerprint ID of the statement fingerprint for the statement execution.

​
Statement with ID {transaction ID} waited on

This section provides details of the transaction executions that block the statement ID flagged with High Contention:
  • Transaction Execution ID: The execution ID of the blocking transaction execution.
  • Transaction Fingerprint ID: The transaction fingerprint ID of the blocking transaction execution.
  • Contention Time: The time that transactions with this execution ID were with other transactions within the specified time interval.
  • Database Name: The name of the contended database.
  • Schema Name: The name of the contended schema.
  • Table Name: The name of the contended table.
  • Index Name: The name of the contended index.

​
Workload Insight types

The Workload Insights tab surfaces the following type of insights:

​
Failed Execution

The transaction or statement execution failed. The following screenshot shows a failed statement execution: Failed statement execution The following screenshot shows the execution details of the preceding failed statement execution. Failed statement execution details The Insights column shows the name of the insight, in this case Failed Execution. The Details column provides the Error Code, such as 40001. CockroachDB uses PostgreSQL Error Codes. 40001 indicates a serialization_failure.

​
High Contention

The transaction or statement execution experienced high time according to the threshold set in the . The following screenshot shows the execution of a transaction flagged with High Contention: Transaction execution The following screenshot shows the execution details of the preceding transaction execution: Transaction execution details

​
High Retry Count

The statement execution experienced a high number of according to the threshold set in the .

​
Slow Execution

The statement (or a statement in the transaction) experienced slow execution. Depending on the settings in Configuration, either of the following conditions trigger this insight:
  • Execution time is greater than the value of the .
  • Anomaly detection is enabled (sql.insights.anomaly_detection.enabled), execution time is greater than the value of sql.insights.anomaly_detection.latency_threshold, and is greater than the p99 latency and more than double the median latency. For details, see Detect slow executions.

​
Suboptimal Plan

The plan could be improved for some statement(s) in the transaction execution. Possible causes include outdated statistics and missing indexes. The statement execution has resulted in one or more index recommendations that would improve the plan. The following screenshot shows the statement execution of the query described in : Statement execution The following screenshot shows the execution details of the preceding statement execution: Statement execution details The Insights column shows the name of the insight, in this case Suboptimal Plan; the Details column provides details on the insight; and the final column contains a Create Index button. Click the Create Index button to perform a query to mitigate the cause of the insight, in this case to create an index on the ride start_time that stores the rider_id.

​
Schema Insights tab

To display this view, click Insights in the left-hand navigation of the DB Console and select Schema Insights. This view lists the that have not been used and should be dropped, and/or the ones that should be created, altered, or replaced (based on statement execution).
  • The drop recommendations are the same as those on the page.
  • The create, alter, and replace recommendations are the same as those on the on the Statements page. Whereas the Explain Plans tab shows all recommendations, the Schema Insights view shows only the latest recommendations for that statement fingerprint. If you execute a statement again after creating or updating an index, the recommendation disappears.
The following screenshot shows the insight that displays after you run the query described in 6 or more times: Schema insight 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.
  • Insights: Contains one of the following insight types: Create Index, Alter Index, Replace Index, Drop Unused Index.
  • Details: Details for each insight. Different insight types display different details fields:
    • Create Index, Alter Index, or Replace Index: A Statement Fingerprint field displays the statement fingerprint that would be optimized with the creation, alteration, or replacement of the index; and a Recommendation field displays the SQL query to create, alter, or replace the index.
    • Drop Unused Index: An Index field displays the name of the index to drop; and a Description field displays the reason for dropping the index.
will see an action button in the final column, which will execute the SQL statement suggested by the schema insight, for example “Create Index”. Upon clicking the action button, a confirmation dialog displays a warning about the cost of and the option to copy the SQL statement for later execution in a SQL client.

​
Search and filter

By default, the Workload Insights view shows all statements or transactions that have insights. By default, the Schema Insights view shows all Schema Insights. 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 is filtered by the string.

​
Time interval

In the Workload Insights view, to see transactions or statement executions 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 page.
It’s possible to select an interval for which no workload insights exist.

​
Filter

To filter the results on the Workload Insights or Schema Insights view:
  1. Click the Filters field.
    • To filter by , select Application Name 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 insight types, select Workload Insight Type or Schema Insight Type and select one or more types.
  2. Click Apply

​
Configuration

You can configure the behavior of insights using the following .

​
Workload insights settings

You can configure Workload Insights with the following :

​
Detect slow executions

There are two different methods for detecting slow executions. By default, they are both enabled and you can configure them based on your workload. The first method flags all executions running longer than . This is analogous to checking the . The second method attempts to detect unusually slow executions. You can enable this detection with and configure it with . CockroachDB will then keep a streaming histogram in memory for each distinct statement fingerprint that has seen an execution latency longer than sql.insights.anomaly_detection.latency_threshold, and will flag any execution with a latency in the 99th percentile (greater than p99) for its fingerprint. Additional controls filter out executions that are less actionable:
  • The execution’s latency must also be longer than twice the median latency (> 2*p50) for that fingerprint. This ensures that the elevated latency is significant enough to warrant attention.
  • The execution’s latency must also be longer than sql.insights.anomaly_detection.latency_threshold. Some executions are slower than usual, but are still fast enough for the workload.
The cluster setting limits the amount of memory available for tracking these streaming latency histograms. When this threshold is surpassed, the least-recently touched histogram is evicted. The default setting is sufficient for tracking about 1,000 fingerprints. You can track the sql.insights.anomaly_detection.memory and sql.insights.anomaly_detection.evictions to determine if the settings are appropriate for your workload. If you see a steady stream of evictions or churn, you can either raise the sql.insights.anomaly_detection.memory_limit cluster setting, to allow for more storage; or raise the sql.insights.anomaly_detection.latency_threshold cluster setting, to examine fewer statement fingerprints.

​
Schema insights settings

You can configure the index recommendations in the Schema Insights tab, , and with the following :

​
See also