Query statistics

As explained in Snowflake Data Engineering

Query statistics are measurements describing the execution behavior and resource consumption of a database query.

Query statistics measure execution behavior and resource consumption. In Snowflake, the query profile combines an execution-plan graphic with statistics selected for the query’s type and complexity. section 8.2.3

Use the query profile to inspect performance signals such as:

• Micro-partition pruning, which shows whether unnecessary storage partitions can be excluded. section 8.2.3
• Data caching and data spilling, which indicate additional execution behaviors affecting performance. section 8.2.3
• Total execution time and bytes spilled to local storage, useful when comparing warehouse performance. section 9.2

Comparing warehouse performance

After running a sales aggregation on the BAKERY_WH_XSMALL warehouse, inspect the Query ID, open View Query Profile, and record Total Execution Time and Bytes spilled to local storage. One observed run took 22 seconds and spilled 36.86 MB. section 9.2

Figure 8.4 Query Profile showing the graphical representation of the query execution plan, the Most Expensive Nodes pane, the Profile Overview pane, and the Statistics pane

Inspect the query profile’s execution-plan graphic, Most Expensive Nodes, Profile Overview, and Statistics panes. section 8.2.3

How Snowflake Data Engineering covers it

How deep it goes

Explains profile panes and common statistics, then stops short of detailing every available metric.

Examples

Uses a bakery sales aggregation, Snowsight Query ID navigation, 22-second execution, and 36.86 MB of local spill.

What it assumes

Assumes the ability to execute a Snowflake query and access its Query ID in Snowsight.

What you can do after

Diagnose query performance by opening a profile and interpreting execution time, pruning, caching, and spilling indicators.

How it connects

Connects query statistics with query profiles, query execution plans, micro-partition pruning, data caching, data spilling, and virtual warehouses.

liveBookmanning.com

Read Snowflake Data Engineering on liveBook Get the book at manning.com

Related concepts

• bytes spilled to local storagehas part · Bytes spilled to local storage is a query resource metric measuring data written to local storage during execution when it cannot remain in memory.
• meanhas part · A mean is a measure of central tendency, calculated as the arithmetic average of values or, for a random variable, as the probability-weighted average of its possible values.
• query profileproduced by · A query profile is a Snowflake tool that presents details about a query's execution plan, operators, timing, and resource consumption.
• statisticskind of · Statistics is the discipline and set of methods for collecting, summarizing, analyzing, and interpreting data, including numerical descriptions used to characterize observations and support inference.

This explainer was generated by Manning's knowledge graph from the text of Snowflake Data Engineering. The section links point to the parts of the book it draws on.