Skip to content
  • AT SNOWFLAKE
  • Industry solutions
  • Partner & Customer Value
  • Product & Technology
  • Strategy & Insights
Languages
  • English
  • Français
  • Deutsch
  • Italiano
  • 日本語
  • 한국어
  • Português
  • Español
  • English
  • Français
  • Deutsch
  • Italiano
  • 日本語
  • 한국어
  • Português
  • Español
  • AT SNOWFLAKE
  • Industry solutions
  • Partner & Customer Value
  • Product & Technology
  • Strategy & Insights
  • English
  • Français
  • Deutsch
  • Italiano
  • 日本語
  • 한국어
  • Português
  • Español
  • Overview
    • Why Snowflake
    • Customer Stories
    • Partners
    • Professional Services
  • OVERVIEW
    • Platform
    • Snowflake Horizon
    • Data Cloud Explained
    • Snowflake Marketplace
    • Snowpark
    • Streamlit
    • Powered by Snowflake
    • Live Demo
    • Cross-Cloud Snowgrid
  • WORKLOADS
    • AI / ML
    • Applications
    • Collaboration
    • Cybersecurity
    • Data Engineering
    • Data Lake
    • Data Warehouse
    • Unistore
  • PRICING
    • Pricing Options
    • Cost & Performance Optimization
  • Industries
    • Advertising, Media, and Entertainment
    • Financial Services
    • Healthcare & Life Sciences
    • Manufacturing
    • Public Sector
    • Retail & Consumer Goods
    • Technology
    • Telecom
  • Departments
    • Marketing
    • IT
    • Finance
    • Cybersecurity
  • Learn
    • Resources
    • Developers
    • Quickstarts
    • Documentation
    • Hands-on Labs
    • Training
    • Certifications
    • Guides
    • Glossary
  • Connect
    • Blog
    • Community
    • Events
    • Webinars
    • Podcast
    • Support
    • Trending
  • Overview
    • About Snowflake
    • Investor Relations
    • Leadership & Board
    • Careers
    • Newsroom
    • Speakers Bureau
    • ESG at Snowflake
    • Snowflake Ventures
Author
Tim Sander Tim Sander
Share
Subscribe
Dec 20, 2022

Analyze Your Query Performance Like Never Before with Programmatic Access to Query Profile

  • Product and Technology
    • Cost Optimization & Performance
Analyze Your Query Performance Like Never Before with Programmatic Access to Query Profile

We understand that query cost and performance are important topics for Snowflake customers, and so we have been building an efficient and zero-maintenance engine that enables our customers to achieve industry-leading performance without needing to constantly monitor and tune query performance. Snowflake customers can simply rely on our engine and don’t need to develop an expert understanding of query execution.

That being said, many customers love visibility into query execution and embrace fine-grained query control. Snowflake’s Query Profile provides a graphical representation of query execution, along with query execution statistics. Based on the Query Profile, customers can take actions such as changing their cluster key, altering their warehouse size, or rewriting their query. This feature has been available in Snowsight, and programmatic access to Query Profile is now available in public preview. Customers can now use get_query_operator_stats to analyze query performance at scale.

The table below summarizes the new table function that enables programmatic access to Query Profile:

When optimizing Snowflake, customers tend to focus on the most expensive queries. Customers can use query_history, an account usage view that contains query data from the past year, to rank queries by execution time or disk spilling. They can focus on the queries with the highest execution time or those with the largest disk spilling, and use Query Profile to delve more deeply in query execution.

Once customers have identified the queries that they want to optimize, they can use programmatic access to a Query Profile to bring more visibility to where improvements can be made, including finding inefficient pruning, “exploding” joins, and identifying patterns in expensive queries.

Identify inefficient pruning

In Snowflake, query pruning can improve query performance by minimizing the amount of data queries need to scan. Customers can improve query pruning using features such as clustering and Search Optimization Service.

In order to diagnose inefficient pruning, customers can use get_query_operator_stats to compare the partitions_scanned and partitions_total values within TableScan operators. Customers can also check for a narrow filter above the TableScan in order to identify potential query performance improvements from better pruning.

Get_query_operator_stats includes the column names for each query operator, so columns used in the filter can be good candidates for a cluster key or search optimization index.

Visibility into “exploding” joins

When a user joins two tables and provides a condition where rows from one table match multiple rows from another table, the result is an “exploding” join where the output can exceed millions of rows. An “exploding” join can also occur if a user omits a join condition.

Example query:

select table1.c1, table2.c2
from table1
join table2 on table1.c1 = table2.c1
 and table1.c2 = table2.c2;

In the above query, if the properties c1 and c2 have a low cardinality, it’s highly likely that this query will output an excessive number of records, even if the source table1 and table2 are relatively small. If the query is expensive, customers can use get_query_operator_stats to analyze the input_rows and output_rows count for the join operator. If the output_rows is excessively large compared to the input_rows, the customer can attribute the poor query performance to the “exploding” join and, if possible, rewrite the query.

Identifying patterns in expensive queries

In most cases, expensive queries aren’t just executed one time. The same query may be repeated thousands of times, each time with different parameter values. Customers can use query_history and get_query_operator_stats to identify queries with similar shape and analyze them all at once, as opposed to troubleshooting each one individually with the Query Profile UI.

Get started

Programmatic access to Query Profile is now in public preview. To get started, review the documentation for get_query_operator_stats.

Share

Test-Driving Snowflake: The Definitive Guide to Maximizing Your Free Trial

Thinking about the free trial but curious about what you’ll be able to do in the trial environment? This guide offers step-by-step advice to help you maximize your Snowflake free trial experience.

Learn More!

Query Profiling

Query Profiling

Read this deep-dive into understanding query profiling.

Delve into the details
Read More
Understanding Your Snowflake Utilization, Part 1: Warehouse Profiling

Understanding Your Snowflake Utilization, Part 1: Warehouse Profiling

In this post, I will show you how to leverage Snowflake's easy-to-use Information Schema functions to gather detailed information about your virtual warehouse usage.

Find Out How
Read More
Audience Targeting in Media, Advertising, and Marketing

Audience Targeting in Media, Advertising, and Marketing

Audience targeting segments consumers based on behavioral and demographic data, assisting marketing and advertising teams in creating personalized campaigns.

More to follow
Read More
What Are OLAP Cubes?

What Are OLAP Cubes?

Online analytical processing and OLAP cubes enable teams to quickly analyze multiple data dimensions. OLAP is ideal for decision-making and problem-solving.

Delve into the details
Read More
Snowflake Inc.
  • Platform
    • Cloud Data Platform
    • Pricing
    • Marketplace
    • Security & Trust
  • Solutions
    • Snowflake for Financial Services
    • Snowflake for Advertising, Media, & Entertainment
    • Snowflake for Retail & CPG
    • Healthcare & Life Sciences Data Cloud
    • Snowflake for Marketing Analytics
  • Resources
    • Resource Library
    • Webinars
    • Documentation
    • Community
    • Procurement
    • Legal
  • Explore
    • News
    • Blog
    • Trending
    • Guides
    • Developers
  • About
    • About Snowflake
    • Investor Relations
    • Leadership & Board
    • Snowflake Ventures
    • Careers
    • Contact

Sign up for Snowflake Communications

Thanks for signing up!

  • Privacy Notice
  • Site Terms
  • Cookie Settings
  • Do Not Share My Personal Information

© 2023 Snowflake Inc. All Rights Reserved |  If you’d rather not receive future emails from Snowflake, unsubscribe here or customize your communication preferences