Analyzing query workloads with Performance Explorer¶

You can use Performance Explorer in Snowsight to review interactive metrics for SQL workloads. The metrics show the overall health of your Snowflake environment, query activity, changes to warehouses, and changes to tables.

Benefits of Performance Explorer¶

Performance Explorer can help you answer the following key questions about Snowflake activity:

• Overall activity: Are queries generally succeeding, and can Snowflake users get their work done?
• Change over time: If query activity or resources look different from what I expected, what has changed and when did the changes occur?
• Hot spots: When I look for opportunities to take action, where should I focus my attention?

Common use cases for Performance Explorer¶

Performance Explorer can help with the following use cases:

• Investigating problem reports about queries or workloads: If a Snowflake workload has started to behave differently, determine what else might have changed recently, such as the resources that the workload depends on or neighboring workload activity.
• Proactively identifying hotspots: If a warehouse or table shows persistent errors or saturation, identify and address the hotspot before it affects critical workloads.
• Identifying optimization opportunities: Find warehouses and tables that might be mismatched to the query activity they support, and adjust workloads and resources to make them compatible.

Required privileges¶

Performance Explorer shows account activity that is similar to data in Account Usage views (for example, query history and access history). What you can see in each part of the dashboard depends on your privileges. Snowflake grants the SNOWFLAKE.PERFORMANCE_EXPLORER_PUBLIC_USER application role to the PUBLIC role so that users can open Performance Explorer in Snowsight; the following rules determine whether sections show full account data, filtered data, an empty state, or a permission error.

Full access to Performance Explorer data in the account¶

You have full access to Performance Explorer data for your account when any role granted to you meets one of the following conditions:

• Your role is the ACCOUNTADMIN role.
• Your role has been granted IMPORTED PRIVILEGES on the shared SNOWFLAKE database (see Enabling other roles to use schemas in the SNOWFLAKE database).
• Your role has been granted the SNOWFLAKE.PERFORMANCE_EXPLORER_USER application role.

For example, to give the user jdoe full Performance Explorer access by using a custom role, run:

USE ROLE ACCOUNTADMIN;

CREATE ROLE pe_viewer_role;
GRANT APPLICATION ROLE SNOWFLAKE.PERFORMANCE_EXPLORER_USER TO ROLE pe_viewer_role;
GRANT ROLE pe_viewer_role TO USER jdoe;

Seeing query metrics without full account access¶

If you do not have full access as described above, you can still see query activity when any role granted to you satisfies one of the following:

• Your role has been granted the GOVERNANCE_VIEWER database role in the SNOWFLAKE database (account-wide query activity).
• Your role has the MONITOR privilege on the account, or the MANAGE WAREHOUSES privilege on the account, which effectively covers all warehouses (query activity that uses warehouses).
• Your role has OWNERSHIP, MONITOR, or OPERATE on at least one warehouse (query activity only for queries that ran on warehouses you can monitor or operate). For details, see Warehouse privileges.

If none of the above apply and you cannot MONITOR or OPERATE any warehouse, Performance Explorer shows a permission error for query activity. This is intentional: without at least one authorized warehouse, no query metrics would be visible. In practice, many accounts include a default warehouse that all users can monitor; see Snowsight templates.

Warehouse filters list warehouses you are allowed to use in filters (for example, warehouses you can MONITOR or OPERATE, and in some cases warehouses that had query activity in the retention window). Warehouse-scoped visibility is similar in spirit to the rules for Query History in Monitor query activity with Query History, but Performance Explorer uses all roles granted to you and combines several privilege types, so the exact rules differ.

Database filters and database-oriented breakdowns¶

To see all databases in database filters (and related aggregations by database), any role granted to you must satisfy one of the following:

• Your role meets the conditions for full access to Performance Explorer data in the account (see the preceding section).
• Your role has been granted the OBJECT_VIEWER database role in the SNOWFLAKE database.
• Your role has RESOLVE ALL on the account.
• Your role has MONITOR on the account.

Otherwise, you only see databases where any role granted to you has at least one privilege on the database. Queries can still appear in other sections even if they touch a database you cannot list, except where the UI explicitly names databases (for example, certain side-panel breakdowns).

Warehouse events¶

To see warehouse events for all warehouses that appear in your authorized query activity, any role granted to you must satisfy one of the following:

• Your role meets the conditions for full access to Performance Explorer data in the account (see the preceding section).
• Your role has been granted the USAGE_VIEWER database role in the SNOWFLAKE database.
• Your role has the MONITOR privilege on the account.
• Your role has the MANAGE WAREHOUSES privilege on the account.

Otherwise, warehouse events are limited to warehouses where you have OWNERSHIP, MONITOR, or OPERATE. If you have none of those warehouse privileges or usage-related database roles, Performance Explorer shows a permission error for warehouse events.

Top tables and table change events¶

The Top tables section and table change events require full access or the GOVERNANCE_VIEWER database role in the SNOWFLAKE database. Snowflake does not offer a lower-privilege, per-table alternative for these sections due to security and performance constraints. If you do not meet this bar, those sections show a permission error.

Empty charts, filtered results, and permission errors¶

For security reasons, an empty chart or table can mean either that there was no activity in the selected period or that your roles cannot see that activity. Tile-level permission errors call out missing privileges (for example, governance visibility for table metrics).

Privilege changes and data freshness¶

Updates to grants and revocations can take a few hours to affect what Performance Explorer shows.

Open Performance Explorer¶

To open Performance Explorer, complete the following steps:

1. Sign in to Snowsight.
2. In the navigation menu, select Monitoring » Performance Explorer.

Performance Explorer contains charts that show metrics related to your workloads and the general health of your Snowflake environment.

To leave feedback about Performance Explorer, select Feedback.

Understanding the Performance Explorer dashboard¶

You can review interactive metrics for SQL workloads by using charts on the Performance Explorer dashboard, and you can apply filters to show metrics about only the query activity and resources that you’re interested in. Charts are grouped under tabs (Queries, Warehouses, and Tables). The page URL updates when you change tabs, and the same tab stays selected if you refresh the page.

Performance Explorer filters¶

At the top of the Performance Explorer dashboard, you can apply the following filters:

• Preset - Choose None or a saved combination of period, warehouse, database, and role filters. From the menu you can save the current filters as a new preset, clear all filters, copy a link that encodes the current filters, and manage saved presets (for example set or change a default preset).

  

• Period - Select a time period, such as the last week, the last two weeks, or a custom range. The dashboard shows metrics for the specified period.

  Performance Explorer displays metrics for one week by default. It supports a period of up to one month, going back from the current date.

  Several Performance Explorer charts show the percentage of change compared to the previous period. The range of the previous period corresponds with the current period range. For example, if the current period is two weeks, then the previous period is the two weeks before the current period started.

• Warehouse - Select a warehouse to view metrics only for query activity that ran using that warehouse. To limit the warehouses in the list, use the search field. To clear the filter, select X.

• Database - Select a database to view metrics only for query activity that accessed that database. To limit the databases in the list, use the search field. To clear the filter, select X.

• Role - Select a role to view metrics only for query activity initiated by that role. To limit the roles in the list, use the search field. To clear the filter, select X.

Performance Explorer charts¶

Performance Explorer displays metrics in different types of charts. It is important to understand the components in each type of chart and how to interpret them.

On the Queries tab, line chart metrics use line charts that are similar to the following image:

The following table describes the callouts in the image:

Some charts include a large average or median value and the percentage of change for the period. When there is more than one line, there is a key to the lines above the chart.

Some charts have an information icon next to the title. Hover over the icon for information about the metrics in the chart.

You can hover over a point in the line chart to see the value for a specific hour:

The Top warehouses section on the Warehouses tab and the Top tables section on the Tables tab have bar charts that are similar to the following image:

The following table describes the callouts in the image:

On both line charts and bar charts, select View details > to open a side panel that displays more detailed information about the metrics on the chart. The detailed information varies based on the metrics shown in the chart. Most side panels present sortable tables that you can use to review metrics for specific warehouses, roles, databases, and queries in the period.

Use the Search results field above the table to filter rows; search is case-insensitive and applies across the side-panel aggregation tabs (for example, By warehouse and By role). Select the download control to export the table as a CSV file. The downloaded file name reflects the chart and the active dashboard filters.

You can select a custom period of time in a side panel by clicking where the custom period starts and dragging to where the custom period ends.

In a side panel, you can select one of the following tabs:

• By warehouse - Shows the activity by warehouses in the period.
• By database - Shows the activity by databases in the period.
• By role - Shows the activity by roles in the period.
• By grouped queries - Shows the queries that were run in the period. Some queries are redacted for security reasons. For information about how queries are grouped, see Use the Grouped Query History view in Snowsight.

If you select a custom period, these tabs refresh to show the metrics only for the selected custom period.

The Top warehouses and Top tables sections also include events charts that are similar to the following image:

An events chart shows a sortable table of events for the type of object. You can examine the data for unexpected events. For more information about warehouse events, see WAREHOUSE_EVENTS_HISTORY view. For more information about table events, see TABLES view.

Reviewing metrics on the Queries tab¶

On the Queries tab, line charts cover reliability signals (failures, retries, overload, blocking) and runtime signals (duration, throughput, wait time, and hourly failure counts). Use them to review trends over the selected period. Performance Explorer summarizes historical windows for your account; it is not a live monitoring dashboard.

The following line-chart metrics are available on the Queries tab:

Reviewing top warehouses¶

On the Warehouses tab, this section of Performance Explorer includes metrics about the warehouses in your Snowflake environment that experienced the most changes in the period. You can review these metrics to see whether your warehouses are functioning as expected to support query activity. The metrics can also show whether any warehouses are associated with trends in query activity that are unusual when compared to other warehouses. You can also determine whether the composition of the workloads that warehouses run have changed.

All metrics in this section show the metric value and the percentage of change since the last period. The percentage of change can be positive or negative, with positive change represented by an up arrow and negative change represented by a down arrow. For each metric, Performance Explorer shows the 10 warehouses with the most changes. To view metrics for more warehouses, select View details > on a chart to open the side panel. If this metric has no value from the last period for a warehouse, --- is shown instead of the percentage of change. There might be no value because the warehouse is new, or because the event being measured is infrequent.

This section includes the following metrics:

Reviewing top tables¶

On the Tables tab, this section of Performance Explorer includes metrics about the tables in your Snowflake environment that experienced the most changes in the period. You can review these metrics to see whether your tables can support query activity and return data as expected. The metrics can also show whether any tables are associated with trends in query activity that are unusual when compared to other tables. You can also determine whether any tables have changed recently and how they have changed.

All metrics in this section show the metric value and the percentage of change since the last period. The percentage of change can be positive or negative, with positive change represented by an up arrow and negative change represented by a down arrow. For each metric, Performance Explorer shows the 10 tables with the most changes. To view metrics for more tables, select View details > on a chart to open the side panel. If this metric has no value from the last period for a table, --- is shown instead of the percentage of change. There might be no value because the table is new or because the event being measured is infrequent.

This section includes the following metrics: