One of the things that shocked me when I moved from working with IBM Db2 to working with Snowflake was the fact that actual execution plans were available for every query run. In Db2, you have to run utilities to get the explain plan, and even more detailed utilities to also get the actuals. It is such a complicated topic that I have more than five separate articles about Db2 LUW Explain.
In Snowflake, the actuals and the plan for every query is captured by default. There has been an exception created in the last two years – hybrid tables do not follow this pattern. However, when working with standard tables, having this kind of information available can be a gold mine, and it can help with identifying problematic query patterns. As with any database’s explain functionality, reading the Snowflake query profile can be an art that one gains skill in over time. I thought I’d go through some of the things we can learn from a query profile.
This is going to be a series of at least three parts about the Snowflake Query Profile – this is part 1.
- Part 1: Accessing the Query Profile (this article)
- Part 2: The Query Execution Plan
- Part 3: The Information Panes
- Part 4: Using the Query Profile to Analyze a Query
Accessing the Query Profile
Query profiles are available for 14 days after query execution. The query profile can be accessed from several locations.
Snowsight Query Results
The first place to look at the query profile is from the Snowsight query results. When you’ve run a query in a worksheet, and see the results, you can click on the query id on the righthand side. The query profile will come up in a separate tab.
The query id is a unique identifier that you can provide to support or solution engineering if you need to engage Snowflake for additional information. Snowflake employees in these functions can then see all kinds of data about your query, including the query profile. If you ever need help with a query, it is very useful to have this ability to communicate exactly what you’re talking about.
Query History
Another place to get to query profiles is through the Query History. Query History can be opened from the activity menu, which is one of the icons on the left side in Snowsight.
This brings up a list of queries. Click in the row of the query you are interested in:
The screen that comes up doesn’t look like the query profile, but clicking on the “Query Profile” tab will bring it up.
URL Manipulation
Because query ids are useful for identifying and talking about queries, you may just have a query id and want the profile for it. You can go into the query history, and pull up any query profile. In the URL, you can then replace the query id of the random query profile you’ve pulled up with the query id you actually want. For example, let’s say I have a query id of 01bbb1cd-0004-f1f7-0000-000754f36029. I go into query history, and pull up a different query profile, and my url in the browser is
https://app.snowflake.com/txqmqvz/nxb95445/#/compute/history/queries/01bbb1cd-0004-f951-0000-000754f34d79/profilereplacing the query id in the url, I get:
https://app.snowflake.com/txqmqvz/nxb95445/#/compute/history/queries/01bbb1cd-0004-f1f7-0000-000754f36029/profileThis url works for me to get to my desired query profile. You’ll just have to take my word for it, as you won’t be able to access my free trial account to see it in person! Query profile information is still only available for 14 days – you can’t use this as a trick to pull up older queries.
Interpreting 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. We’ll look at each area in greater detail in future parts of this series.
Summary
This is the first stepping stone in working with and understanding the Snowflake Query profile. Hopefully, it gives several ideas on how to get to query profile data. Future articles will dig into the information available in the query profile and how to use it.
