This article is the third in a series about the Snowflake Query Profile.
- Part 1: Accessing the Query Profile
- Part 2: The Query Execution Plan
- Part 3: The Information Panes (this article)
- Part 4: Using the Query Profile to Analyze a Query
If you’re not familiar with how to get to the Snowflake query profile, I recommend first reading Part 1: Accessing the Query Profile
No matter how you access the Query Profile, it will look something like this:
Notice that there are two primary areas here. The query execution plan on the left, and the information panes on the right. In this article we’ll focus on the information panes on the right.
Information Panes
Before we dig into the information available here, there a couple of things that are important to understand. The first is that the panes here are context-dependent. This means that the information contained depends on what is selected in the query execution plan at the left.
The second thing to be aware of here is that many metrics are only displayed if they have a non-zero value. If, for example, you don’t see a metric about the amount of remote spilling that occurred, that means that no remote spilling occurred, assuming that is relevant to the context of the selection in the query execution plan on the left.
The primary information panes available are:
- Most Expensive Nodes
- Profile Overview
- Statistics
- Attributes
Most Expensive Nodes
At the top of the information panes is the Most Expensive Nodes pane. This pane is particularly useful for understanding where in a query the most CPU time is spent. This pane will list the top few operator nodes in order of expense, and provide an approximate percentage of the query’s cpu time that is consumed by those nodes. Here’s an example of what this looks like:
Here, we can see the top 4 operator nodes for this query. One of them – TableScan [23] – consumes 77% of the cpu time for this query! This box is very useful for helping us on where to focus to improve the execution time of a query. Personally, I love to see a single operator node taking up the lion’s share of the time, because this means I have a leverage point. I can focus in on that operator node, and the time I spend improving it is likey to have an actual impact on overall execution time. It is a much more difficult thing to improve the performance of a query when no single operator node has more than a few percentage points of the overall cpu time. In that scenario, even if I am able to entirely eliminate the time spent on an operator node, it is not likely to impact overall query execution time.
In this information pane, you can click on each operator node, and the query plan on the left will locate that operator node for you. That may not seem like much of an impact for small test queries like this one, but when you’re dealing with complex queries with hundreds of operator nodes, it can be quite useful.
Profile Overview
When you enter the query profile, or when no specific operator node is selected in the query plan, the numbers shown here are for the query as a whole. This pane transforms when you click on an operator node to show details for just that onperator node. Here is the overall view for the Profile Overview:
And here is what it shows if we click on TableScan [23]:
I find this pane to be most useful at the query level instead of for individual operator nodes. This is also where the total query execution time is reported. The balance of where time is spent can be interesting. If the majority of time is spent in remote I/O, then seeking to scan less data may be where we spend time. If the majority of time is spent in Synchronization, that might be an indication that we’re running a small query on a warehouse that is too large. If a lot of time is spent in initialization, that can be an indicator that the query is overly complex or the table is overly wide, or that we had to start the warehouse before running the query.
Statistics
The Statistics information pane is one that varies a lot depending on the context. It is useful at both the query level and at the operator node level. Here is what it looks like for the whole query:
And here is what it shows if we click on TableScan [23]:
This pane is where we can understand what micro-partition pruning we are seeing both for this query and for individual tables in the plan. It is also where local and remote spilling are reported if they are present.
Attributes
The Attributes information pane does not usually appear when the context is the full query. It usually appears when an operator node is selected in the query plan on the left. Here is what it shows if we click on TableScan [23]:
It provides the full table name and the columns we are scanning from that table.
Here is what it looks like for Filter [21]:
Knowing the filter conditions for an expensive step can be particularly useful. This is also where we can understand what the join key is for a join.
Summary
This article completes the basic tour of the Query Profile. In the next article in this series, I’ll dig into some specific patterns to look for when using the query profile.
References
For reference, we are using the following query for the examples of the query profile that we’ve explored in this article and the last two in this series. You can run this query as-is in your own free demo account to explore the output.
with
v1 as(
select i_category, i_brand,
cc_name,
d_year, d_moy,
sum(cs_sales_price) sum_sales,
avg(sum(cs_sales_price)) over
(partition by i_category, i_brand, cc_name, d_year) avg_monthly_sales,
rank() over (partition by i_category, i_brand, cc_name order by d_year, d_moy) rn
from item, catalog_sales, date_dim, call_center
where cs_item_sk = i_item_sk and
cs_sold_date_sk = d_date_sk and
cc_call_center_sk= cs_call_center_sk and
(
d_year = 2001 or
( d_year = 2001-1 and d_moy =12) or
( d_year = 2001+1 and d_moy =1)
)
group by i_category, i_brand,
cc_name , d_year, d_moy),
v2 as(
select v1.i_brand
,v1.d_year, v1.d_moy
,v1.avg_monthly_sales
,v1.sum_sales, v1_lag.sum_sales psum, v1_lead.sum_sales nsum
from v1, v1 v1_lag, v1 v1_lead
where v1.i_category = v1_lag.i_category and
v1.i_category = v1_lead.i_category and
v1.i_brand = v1_lag.i_brand and
v1.i_brand = v1_lead.i_brand and
v1.cc_name = v1_lag. cc_name and
v1.cc_name = v1_lead. cc_name and
v1.rn = v1_lag.rn + 1 and
v1.rn = v1_lead.rn - 1)
select *
from v2
where d_year = 2001 and
avg_monthly_sales > 0 and
case when avg_monthly_sales > 0
then abs(sum_sales - avg_monthly_sales) / avg_monthly_sales
else null
end > 0.1
order by sum_sales - avg_monthly_sales, nsum
limit 100;