Using Snowflake Performance Investigator (PI)
The Snowflake Performance Investigator (Snowflake PI) provides a more in-depth analysis and investigation of the Instance activity and resource consumption.
To view the Snowflake PI dashboard:
- In the left navigation pane, click Databases. The Database dashboard displays a list of all instances.
- Click on the required Snowflake PI instance from the list.
The Snowflake PI dashboard displays the following components:
The Instance View - Provides the ability to investigate and analyze the resource consumption of the instance.
The Performance Tree - Provides iterative access to any dimension associated with Snowflake database activity. For more information, refer to Using the Performance Tree.
The Resource Menu - Selecting one resource updates the performance tree, resource consumption charts, and overview section to display only the activity related to the selected resource. For more information, refer to Understanding the overall Instance Activity with the Resource Selector.
This section covers the following key areas:
Understanding the overall Instance Activity with the Resource Selector
The Snowflake PI dashboard includes a resource selector that allows you to filter activity by the resource it is waiting for. Each resource indicates a group of several wait events that relate to the same area. By selecting a resource, you set the PI dashboard to show only activity relevant to the resource selected. It also highlights relevant metrics for investigation issues related to the selected resource. For example, by selecting Warehouse, the performance tree is filtered to show only activity that was waiting for warehouse availability and show metrics like Tuple Deleted/Inserted/Updated.
The following table explains the resources available:
| Resource | Description |
|---|---|
| Workload | This resource enables you to review the instance workload by consolidating all resources into a single view. |
| Compile | This resource is the time it takes for the optimizer to create an optimal plan for the efficient execution of the query in the Cloud Services Layer. Compilation time for more complex queries will likely be longer. This process may also involve other operations like pruning and statistics analysis. In some cases, the compilation time can be longer than the execution time. |
| Lock | This resource indicates that the process is waiting for a lock on a resource held by another transaction to be released. |
| Other | Miscellaneous instance waits include infrequent or special-purpose wait states that should typically remain close to zero. |
| Retry | This resource indicates time spent retrying the query if the original execution failed. This may be due to an actionable cause - such as an out of memory error - or a non-actionable cause - such as a network outage. |
| Warehouse | This resource indicates time spent queued in the warehouse. While warehouses represent the compute resources that handle most of the query execution, this category indicates wait time due to warehouse resource unavailability. This delay may be caused by waiting for provisioning due to startup or resizing to complete, resource repair, or due to overloading, in which execution is delayed until the warehouse has freed up sufficient resources from other activities to run the queued statement. |
Using the Performance Tree
The performance tree provides iterative (up to three levels) access to any of the key dimensions associated with Snowflake database activity, based on the OLAP multidimensional model and an instance view of the instance activity. Domain nodes offer a hierarchical view of all types of Snowflake activity characteristics.
Selecting a dimension from the tree determines what subset of activity is displayed. Iterative drill-down into domains of interest provides increasingly refined focus and diagnosis.
For example, to begin the investigation by first identifying the most active DB User, follow the steps described below:
- Select the Users node, to display the most active database users in the selected time range. That is, the database users who consumed the highest amount of the selected resource.
- Select the first user, to focus the entire window on that user’s activity.
- Identify the most demanding Snowflake statement that this specific user has executed, by expanding the user node and then selecting the Snowflake statement dimension node. This displays the most active Snowflake statements executed by this user.
- Select a specific Snowflake statement to focus the entire window on the selected statement’s activity.
- Select Warehouses under the selected Snowflake Statement, to view the warehouse on which the statement was run.
In a similar manner, such iterative drilldowns can be carried out into any Snowflake dimension of interest to gain a complete understanding of the causes of its behavior.
The default Snowflake dimensions are as follows:
- Statements - Executed SQL commands are listed in a dropdown menu. You can select a specific SQL statement to view details such as Resource Breakdown, Workload Metrics, and Blocked Sessions.
- Databases - The database context in which the session performs its operations. A session may switch between multiple databases during its lifetime.
- Sessions - This displays the top sessions that consumed the most active time during the selected time frame.
- Users - Snowflake users executing SQL statements.
- Roles - The roles which the Snowflake user was employing when running SQL statements. Roles are groups of privileges which can be assigned to one or multiple users.
- Warehouses - The name of the warehouse on which a SQL statement was executed. Warehouses are bundles of compute resources that handle most operations in Snowflake, including query executions.
Viewing the Historical Metrics
The Snowflake PI dashboard is divided into three sections that are correlated to each other:
- Resource breakdown charts - This section displays data in different charts:
- Baseline chart - Displays the instance workload compared to the baseline over time.
- Breakdown chart - Activity of the instance by second.
- Resource Breakdown Pie chart - Displays the resource breakdown usage by % of the total instance activity.
- Workload Metrics - A table that displays various resource consumption metrics, providing an in-depth view of instance activity. Each resource includes its default metrics. Selecting each dimension in the performance tree along with a specific resource affects the data displayed at each level. For example, selecting the Lock resource will cause the Instance view dimension to show only lock-related data. The Statements dimension will display only the statements that were experiencing locks, and the Users dimension will present only the users affected by locks. This filtering applies across all dimensions and resources.
- Blocked Sessions - This section displays the list of blocked sessions for a specific timeframe. For more information, refer to Analyzing Blocked Sessions.
The Workload Metrics section displays parameters such as name, resource, average, min, max, total, and per execution for each metric.
The metrics listed in the table below are available only for certain dimension levels.
| Resource menu | Default Metrics Available |
|---|---|
| Workload | - Active Time - Displays the sum of all wait events within the current time interval. - Average SQL Response Time - Displays the average duration of the statements executed during the current time interval, calculated as the elapsed time divided by the execution count. - Elapsed Time - Displays the total time consumed for executing the statements. - Executions - Displays the number of times statements were executed that were active during the current interval. |
| Compile | - Active Time - Displays the sum of all wait events within the current time interval. - Average SQL Response Time - Displays the average duration of the statements executed during the current time interval, calculated as the elapsed time divided by the execution count. - Elapsed Time - Displays the total time consumed for executing the statements. - Executions - Displays the number of times statements were executed that were active during the current interval. - Compile Wait - Displays the time spent by the Cloud Services layer in compiling queries for execution. |
| Execution | - Active Time - Displays the sum of all wait events within the current time interval. - Average SQL Response Time - Displays the average duration of the statements executed during the current time interval, calculated as the elapsed time divided by the execution count. - Elapsed Time - Displays the total time consumed for executing the statements. - Executions - Displays the number of times statements were executed that were active during the current interval. - Bytes Scanned - Displays number of bytes scanned from tables during the statement execution. - Bytes Written to Result - Displays number of bytes written to a result object for a query that produces one. - Execution Status Failed With Error - Displays the number of statements that failed due to an error. - Execution Status Failed With Incident - Displays the number of statements that failed due to an error. - Execution Status Success - Displays the number of statements that completed successfully. - Execution Wait - Displays the time spent executing statements. |
| Lock | - Active Time - Displays the sum of all wait events within the current time interval. - Average SQL Response Time - Displays the average duration of the statements executed during the current time interval, calculated as the elapsed time divided by the execution count. - Elapsed Time - Displays the total time consumed for executing the statements. - Executions - Displays the number of times statements were executed that were active during the current interval. - Lock Wait - Displays the time spent blocked by a concurrent statement holding a lock on a needed resource. |
| Other | - Active Time - Displays the sum of all wait events within the current time interval. - Average SQL Response Time - Displays the average duration of the statements executed during the current time interval, calculated as the elapsed time divided by the execution count. - Elapsed Time - Displays the total time consumed for executing the statements. - Executions - Displays the number of times statements were executed that were active during the current interval. - List External File Time - Displays the time spent listing external files. - Other Wait - Displays the total time spent in generally incidental processes not covered by other categories. |
| Retry | - Active Time - Displays the sum of all wait events within the current time interval. - Average SQL Response Time - Displays the average duration of the statements executed during the current time interval, calculated as the elapsed time divided by the execution count. - Elapsed Time - Displays the total time consumed for executing the statements. - Executions - Displays the number of times statements were executed that were active during the current interval. - Fault Handling Time - Displays the time spent performing query retries caused by errors that are not actionable. - Query Retry Time - Displays the time spent performing query retries caused by errors that are actionable. - Retry Wait - Displays the total time spent performing retries for queries that failed due to actionable or non-actionable reasons. |
| Warehouse | - Active Time - Displays the sum of all wait events within the current time interval. - Average SQL Response Time - Displays the average duration of the statements executed during the current time interval, calculated as the elapsed time divided by the execution count. - Elapsed Time - Displays the total time consumed for executing the statements. - Executions - Displays the number of times statements were executed that were active during the current interval. - Queued Overload Time - Displays the time queries spent in a warehouse queue due to the warehouse being overloaded by the current query workload. - Queued Provisioning Time - Displays the time queries spent in a warehouse queue due to compute resources in the process of being provisioned during creation, resume or resize. - Queued Repair Time - Displays the time queries spent in a warehouse queue due to compute resources needing repair. - Warehouse Wait - Displays the total time spent in warehouse queues while waiting for compute resources for execution to become available. |
Analyzing Blocked Sessions
This section displays the details of the blocked session, the session that is causing the block, and any deadlock sessions. To view blocked sessions for a specific timeframe, click the Duration dropdown list, select the minimum and maximum time range limit, and then click Apply. Some information may not be applicable for Snowflake or unavailable based on the data present at collection time. You can view the following metric parameters for the sessions:
- Event Window- Displays the start date of the session (date-time format).
- End Date- Displays the end date of the session (date-time format).
- Session- Displays the session ID attempting to run the transaction.
- Blocked By- Displays the session ID holding the lock that is blocking the transaction.
- Status - Current status of the transaction.
- Object Name - Displays the locked resource in contention.
- Duration- How long the transaction has been blocked.
- Program- N/A for Snowflake.
- DB User- Snowflake user name associated with the session.
- Client Machine- N/A for Snowflake.
- SQL ID- Displays the parameterized query hash for the blocking query.
- SQL Text- Displays the SQL text of the blocking query.
