Query profile

  • Article

You can use a query profile to visualize the details of a query execution. The query profile helps you troubleshoot performance bottlenecks during the query's execution. For example:

  • You can visualize each query operator and related metrics, such as the time spent, number of rows processed, rows processed, and memory consumption.
  • You can identify the slowest part of a query execution at a glance and assess the impacts of modifications to the query.
  • You can discover and fix common mistakes in SQL statements, such as exploding joins or full table scans.

Requirements

To view a query profile, you must either be the owner of the query or you must have at least CAN MONITOR permission on the SQL warehouse that executed the query.

View a query profile

You can view the query profile from the query history using the following steps:

  1. View query history.

  2. Click the name of a query. A query details panel appears on the right side of the screen.

    Query execution summary simple view

  3. Click See query profile.

    Note

    If Query profile is not available is displayed, no profile is available for this query. A query profile is not available for queries that run from the query cache. To circumvent the query cache, make a trivial change to the query, such as changing or removing the LIMIT.

  4. To view the query profile in graph view (the default), click Graph view. To view the query profile as a tree, click Tree view.

    • Graph view is optimized for visualizing how data flows from one node to another.
    • Tree view is optimized for quickly finding issues with the query's performance, such as identifying the longest-running operator.

  5. In graph view or tree view, you can click one of the tabs at the top of the page to view details about each of the query's operators.

    • Time spent: Aggregated time spent for each operation. The task's total time is also provided.
    • Rows: The number and size of the rows affected by each of the query's operators.
    • Peak memory: The peak memory each of the query's operators consumed.

    Note

    Some non-Photon operations are executed as a group and share common metrics. In this case, all operations have the same value as the parent operator for a given metric.

  6. In graph view, you can click on an operator to show detailed metrics. In tree view, you can click the name of the operator to see the related details.

  7. Each node is labeled with its operation. By default, metrics for some operations are hidden. These operations are unlikely to be the cause of performance bottlenecks. To see information for all operations, and to see additional metrics, click Kebab menu at the top of the page, then click Enable verbose mode. The most common operations are:

    • Scan: Data was read from a datasource and output as rows.
    • Join: Rows from multiple relations were combined (interleaved) into a single set of rows.
    • Union: Rows from multiple relations that use the same schema were concatenated into a single set of rows.
    • Shuffle: Data was redistributed or repartitioned. Shuffle operations are expensive with regard to resources because they move data between executors on the cluster.
    • Hash / Sort: Rows were grouped by a key and evaluated using an aggregate function such as SUM, COUNT, or MAX within each group.
    • Filter: Input is filtered according to a criteria, such as by a WHERE clause, and a subset of rows is returned.
    • (Reused) Exchange: A Shuffle or Broadcast Exchange is used to redistribute the data among the cluster nodes based on the desired partitioning.
    • Collect Limit: The number of rows returned was truncated by using a LIMIT statement.
    • Take Ordered And Project: The top N rows of the query result were returned.

  8. (Optional) For Databricks SQL queries, you can also view the query profile in the Apache Spark UI. Click the Kebab menu kebab menu at the top of the page, then click Open in Spark UI.

  9. To close the query profile, click Hide query profile near the bottom of the panel.

You can also access the query profile in the following parts of the UI:

  • From the SQL editor: When the query completes, a link near the bottom of the page shows the time elapsed and number of rows returned. Click that link to open the query details panel. Click See query profile.

    Open query history from editor output

  • From a notebook: If your notebook is attached to a SQL warehouse or serverless compute, you can access the query profile using the link under the cell that contains the query. Click See performance to open the run history. Click a statement to open the query details panel.

    Open query history notebook output

  • From the Delta Live Tables pipeline UI: You can access the query history and profile from the Query History tab in the pipeline UI. See Access query history for Delta Live Tables pipelines

View details about the query profile

The query profile lists the query's top-level operators in reverse order, with the last operator listed first. Use the following steps to familiarize yourself with the different parts of the query profile.

  1. Use the Time spent, Rows, and Peak memory buttons near the top of the query profile view to see the top-level metrics in each category.
  2. Click an operator to view details about the operation, such as the description and metrics about the duration, memory consumed, number and size of rows returned, and any grouping and aggregation expressions or output attributes used.
  3. To close operator details, click Back to Query details.
  4. To close the query profile, click Hide query profile near the bottom of the right-side panel.

Share a query profile

To share a query profile with another user:

  1. View query history.
  2. Click the name of the query.
  3. To share the query, you have two choices:
    • If the other user has the CAN MANAGE permission on the query, you can share the URL for the query profile with them. Click Share. The URL is copied to your clipboard.
    • Otherwise, if the other user does not have the CAN MANAGE permission or is not a member of the workspace, you can download the query profile as a JSON object. Download. The JSON file is downloaded to your local system.

Import a query profile

To import the JSON for a query profile:

  1. View query history.

  2. Click the kebab menu Kebab menu on the upper right, and select Import query profile (JSON).

  3. In the file browser, select the JSON file that was shared with you and click Open. The JSON file is uploaded and the query profile is displayed.

    When you import a query profile, it is dynamically loaded into your browser session and does not persist in your workspace. You need to re-import it each time you want to view it.

  4. To close the imported query profile, click X at the top of the page.

Next steps