What you will be able to do
- Query SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY to find slow, repeated or spilling queries, knowing its retention and latency
- Use QUERY_ATTRIBUTION_HISTORY to attribute warehouse credits to queries, users and stored procedures, and know what it excludes
- Tell apart overload queuing and provisioning queuing, and say how to reduce each
- Explain why grouping similar workloads on a warehouse makes it easier to tune
1.QUERY_HISTORY: performance across the whole account
The Query Profile explains one query. To evaluate performance across a warehouse or the whole account, query SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY. It covers the last 365 days. The Information Schema table function also called QUERY_HISTORY covers only the past 7 days. The Account Usage view has latency of up to 45 minutes. To check a query you've just run, use Snowsight, or the Information Schema, which updates sooner. By default only the ACCOUNTADMIN role can read ACCOUNT_USAGE views.
The view records, per query, the same symptoms the profile shows.
| Column | What it records |
|---|---|
| partitions_scanned / partitions_total | Micro-partitions scanned against the total for all tables in the query (pruning) |
| bytes_spilled_to_local_storage / bytes_spilled_to_remote_storage | Volume of data spilled to local or remote disk |
| queued_overload_time | Time in the warehouse queue because the warehouse was overloaded |
| queued_provisioning_time | Time in the queue waiting for compute to provision after creation, resume or resize |
| query_hash / query_parameterized_hash | Hashes of the canonicalized or parameterized SQL, for grouping repeated queries |
| query_tag | Tag set through the QUERY_TAG session parameter |
The hash columns matter because a query that is cheap on each run can still be costly if it runs very often. Grouping by query_hash shows which repeated statements to optimize first:
SELECT
query_hash,
COUNT(*),
SUM(total_elapsed_time),
ANY_VALUE(query_id)
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE warehouse_name = 'MY_WAREHOUSE'
AND DATE_TRUNC('day', start_time) >= CURRENT_DATE() - 7
GROUP BY query_hash
ORDER BY SUM(total_elapsed_time) DESC
LIMIT 100;Checkpoint 1 of 6· Check yourself
You need every query that a user cancelled last month, using ACCOUNT_USAGE.QUERY_HISTORY. Which predicate finds them?
execution_status only takes the values success, fail and incident. Cancelled queries are identified by their error_message.
“Canceled queries are identified by their error_message text (SQL execution canceled), not by their execution_status value.”Source: docs.snowflake.com
2.QUERY_ATTRIBUTION_HISTORY: what each query cost
QUERY_HISTORY tells you how long a query took. SNOWFLAKE.ACCOUNT_USAGE.QUERY_ATTRIBUTION_HISTORY tells you how many warehouse credits it used, over the last 365 days. Its key column is CREDITS_ATTRIBUTED_COMPUTE. When queries run at the same time, Snowflake splits the warehouse's cost among them by the weighted average of their resource consumption. The figure includes any resizing or multi-cluster autoscaling.
The view leaves several costs out: - Warehouse idle time. This is time with no queries running, measured at the warehouse level. So summing attributed credits will not reproduce the warehouse's whole bill. - Other costs, such as data transfer, storage, cloud services, serverless features and AI token costs. - Very short queries (about 100 ms or less) are not included at all.
For an accelerated query, add CREDITS_USED_QUERY_ACCELERATION to get the total cost. Latency can be up to eight hours. Access is through the USAGE_VIEWER or GOVERNANCE_VIEWER database roles. The view has no records for Adaptive Warehouses; use QUERY_METERING_HISTORY for those.
SELECT user_name, SUM(credits_attributed_compute) AS credits
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ATTRIBUTION_HISTORY
WHERE user_name = CURRENT_USER()
AND start_time >= DATE_TRUNC('MONTH', CURRENT_DATE)
AND start_time < CURRENT_DATE
GROUP BY user_name;A stored procedure issues a chain of child queries. Each child records PARENT_QUERY_ID and ROOT_QUERY_ID. To cost the whole procedure, sum the attributed credits for every query whose root is the procedure's call, plus the call itself.
Checkpoint 2 of 6· Fill the gap
Which column completes this query that totals the attributed cost of a whole stored procedure?
SELECT SUM(credits_attributed_compute) AS total_attributed_credits FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ATTRIBUTION_HISTORY WHERE ( ? = $query_id OR query_id = $query_id);ROOT_QUERY_ID is the topmost query in the chain, so it catches children at every level. PARENT_QUERY_ID would catch only the direct children.
Source: docs.snowflake.comCheckpoint 3 of 6· Exam question
Two completed queries both show spilling in their Query Profile statistics pane. Query 1 shows only nonzero "Bytes spilled to local storage." Query 2 shows a large nonzero value under "Bytes spilled to remote storage." Which statement correctly compares their severity?
Correct answer: B — Query 2 carries the larger penalty, since remote spilling writes intermediate data to cloud storage, not local SSD cache.
- A. Spilling and the result cache are unrelated mechanisms; remote spilling reflects memory and local-disk pressure during execution and has no dependency on whether a result was previously cached.
- B. Remote spilling means the local disk cache on the warehouse nodes is also full, forcing intermediate data onto slower cloud storage over the network, which is far more expensive than local spilling and is treated as the more urgent tuning signal.
- C. Local spilling can occur on a warehouse of any size once the working set exceeds available memory and local disk cache; it is not gated on the warehouse already being at maximum size, so this reasoning is incorrect.
- D. Local and remote spilling are not equivalent in cost: remote spilling adds network round trips to cloud storage on top of any compression, so the two tiers do not perform the same.
Sources3
3.Queuing: overload or provisioning?
Queuing adds to response time because a query can't start until the warehouse has room for it. QUERY_HISTORY separates the causes. queued_overload_time is time queued because the current workload was overloading the warehouse. queued_provisioning_time is time waiting for compute during creation, resume or resize. queued_repair_time is time waiting for compute to be repaired. Only overload points to a capacity problem. It is also the condition behind the QUERY_INSIGHT_QUEUED_OVERLOAD insight, whose advice is a larger warehouse or one with fewer concurrent queries.
To see queuing over time, open the Warehouse Activity chart for a warehouse in Snowsight (Compute » Warehouses). In SQL, query WAREHOUSE_LOAD_HISTORY, which has latency of up to 3 hours. Its load values are the total execution time of queries in a given state during an interval, divided by the length of that interval. This query lists days on which any warehouse had queued load:
SELECT TO_DATE(start_time) AS date,
warehouse_name,
SUM(avg_running) AS sum_running,
SUM(avg_queued_load) AS sum_queued
FROM snowflake.account_usage.warehouse_load_history
WHERE TO_DATE(start_time) >= DATEADD(month,-1,CURRENT_TIMESTAMP())
GROUP BY 1,2
HAVING SUM(avg_queued_load) >0;Checkpoint 4 of 6· Check yourself
A query has a QUERY_INSIGHT_QUEUED_OVERLOAD insight. Which action does Snowflake suggest?
Overload queuing is a warehouse-capacity problem, so the fix is more capacity or less competition for it. The other options address scan, query-plan or memory problems.
“To avoid this problem, use a larger warehouse that has more capacity, or use a warehouse that has fewer concurrent queries.”Source: docs.snowflake.com
4.Grouping similar workloads
Every warehouse lever is a trade-off: more size, query acceleration, limits on concurrency, cache tuning. A lever only pays off if the queries on that warehouse benefit from it. If one warehouse runs very different queries, an improvement you pay for may be wasted on queries that don't need it. That is why Snowflake notes that tuning is more straightforward when a warehouse runs similar workloads.
QUERY_HISTORY gives you the evidence for splitting a warehouse. Bucketing a warehouse's queries by elapsed time shows whether it serves one kind of work or a mix of short and very long queries. That pattern can tell you whether to resize the warehouse or move some queries to another one.
SELECT
CASE
WHEN Q.total_elapsed_time <= 60000 THEN 'Less than 60 seconds'
WHEN Q.total_elapsed_time <= 300000 THEN '60 seconds to 5 minutes'
WHEN Q.total_elapsed_time <= 1800000 THEN '5 minutes to 30 minutes'
ELSE 'more than 30 minutes'
END AS BUCKETS,
COUNT(query_id) AS number_of_queries
FROM snowflake.account_usage.query_history Q
WHERE TO_DATE(Q.START_TIME) > DATEADD(month,-1,TO_DATE(CURRENT_TIMESTAMP()))
AND total_elapsed_time > 0
AND warehouse_name = 'my_warehouse'
GROUP BY 1;Checkpoint 5 of 6· Exam question
In the Query Profile for a slow report query, a TableScan node shows "Partitions scanned: 9,800" out of "Partitions total: 10,000" even though the query filters on a single date column. What does this indicate?
Correct answer: C — Pruning is largely ineffective, since nearly every micro-partition had to be read despite the date filter.
- A. Partitions scanned versus partitions total is a pruning metric on the TableScan node, not a spill metric; spilling is reported separately in the statistics pane under bytes spilled to local or remote storage.
- B. Queuing time is reported separately as time the query waited before it started executing; the scanned-partition count describes how much data a running scan touched, not resource contention.
- C. When partitions scanned is close to partitions total despite a selective filter, the filtered column's value ranges are spread across most micro-partitions, so pruning cannot eliminate them and the scan reads nearly the whole table.
- D. Snowflake's pruning is designed to skip micro-partitions whose min/max metadata rules out the filter predicate; scanning almost everything is a sign pruning failed for this data layout, not expected default behavior.
Checkpoint 6 of 6· Check yourself
One warehouse runs heavy ETL transformations alongside short, frequent lookups. The team upsizes it to cut ETL spillage. Why does Snowflake's guidance count against this?
A warehouse-level change applies to every query on it. When the workloads differ, the queries that don't need the change still incur its cost.
“the cost of a performance enhancement might be wasted on a query that does not benefit from the optimization.”Source: docs.snowflake.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Summing CREDITS_ATTRIBUTED_COMPUTE across all queries on a warehouse reproduces that warehouse's full credit bill.Why is that wrong?
Attributed credits cover query execution only. Warehouse idle time is excluded, as are very short queries and costs such as cloud services and data transfer.
2.To check how a query you just ran performed, query ACCOUNT_USAGE.QUERY_HISTORY straight away.Why is that wrong?
ACCOUNT_USAGE views lag behind, by up to 45 minutes for QUERY_HISTORY. Use Snowsight (or the Information Schema) to check a query you've just run.
Covered in QUERY_HISTORY: performance across the whole account
3.Any queued time in QUERY_HISTORY means the warehouse is too small.Why is that wrong?
Only queued_overload_time reflects an overloaded warehouse. queued_provisioning_time comes from the warehouse being created, resumed or resized.
Covered in Queuing: overload or provisioning?
Practise it for real
Use ACCOUNT_USAGE views to find your account's worst spilling queries, your most time-consuming repeated statements, and your own credit usage this month.
1.Using a role with access to the SNOWFLAKE database, run the top-10 spill query against snowflake.account_usage.query_history: filter for bytes_spilled_to_local_storage > 0 OR bytes_spilled_to_remote_storage > 0 over the last 45 days.
Why: Spillage means a warehouse ran out of memory, and remote spill is the most expensive kind.
You should see: Up to 10 rows with query_id, partial query text, user, warehouse and both spill columns. Note any rows with remote spill on warehouses where QAS is enabled.
2.Run the query_hash grouping query for one of your warehouses over the last 7 days.
Why: A statement that is cheap on each run can still dominate total time if it runs very often.
You should see: Up to 100 hashes ordered by SUM(total_elapsed_time), each with a run count and a sample query_id you can open in the Query Profile.
3.Run the per-user QUERY_ATTRIBUTION_HISTORY query for CURRENT_USER() for the current month.
Why: This turns elapsed time into credits attributed to your queries.
You should see: One row with your user_name and summed credits. Recent queries may be missing because of up to eight hours' latency, and queries of about 100 ms or less are never included.
Stuck? Get a nudge
If a view returns a privilege error, ACCOUNT_USAGE is restricted to ACCOUNTADMIN by default. QUERY_ATTRIBUTION_HISTORY also needs the USAGE_VIEWER or GOVERNANCE_VIEWER database role.
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“the table function restricts the results to activity over the past 7 days, versus 365 days for the Account Usage view.”
↩︎ QUERY_HISTORY: performance across the whole account“Latency for the view may be up to 45 minutes.”
↩︎ QUERY_HISTORY: performance across the whole account“Time (in milliseconds) spent in the warehouse queue, due to the warehouse being overloaded by the current query workload.”
↩︎ Exam trap 3“Canceled queries are identified by their error_message text (SQL execution canceled), not by their execution_status value.”
↩︎ Checkpoint“waiting for the warehouse compute resources to provision, due to warehouse creation, resume, or resize.”
↩︎ Prediction - 2.
“By default, only the account administrator (i.e. user with the ACCOUNTADMIN role) can access views in the ACCOUNT_USAGE schema.”
↩︎ QUERY_HISTORY: performance across the whole account“a frequently repeated query could lead to high costs, based on the number of times the query runs.”
↩︎ QUERY_HISTORY: performance across the whole account“Use the Warehouse Activity chart to visualize the load of the warehouse, including whether queries were queued.”
↩︎ Queuing: overload or provisioning?“These trends in query completion time can help inform decisions to resize warehouses or separate out some queries to another warehouse.”
↩︎ Grouping similar workloads“If you want to check the execution time of a query right after running it, use Snowsight to view its performance.”
↩︎ Exam trap 2 - 3.
“This Account Usage view can be used to determine the compute cost of a given query run on warehouses in your account”
↩︎ QUERY_ATTRIBUTION_HISTORY: what each query cost“the cost of the warehouse is attributed to individual queries based on the weighted average of their resource consumption”
↩︎ QUERY_ATTRIBUTION_HISTORY: what each query cost“Short-running queries (<= ~100ms) are currently too short for per query cost attribution and are not included in the view.”
↩︎ QUERY_ATTRIBUTION_HISTORY: what each query cost“The total cost for an accelerated query is the sum of this column and the CREDITS_ATTRIBUTED_COMPUTE column.”
↩︎ QUERY_ATTRIBUTION_HISTORY: what each query cost“you can compute the attributed query costs for the procedure by using the root query ID for the procedure.”
↩︎ QUERY_ATTRIBUTION_HISTORY: what each query cost“Includes only the credit usage for the query execution and doesn’t include any warehouse idle time.”
↩︎ Exam trap 1 - 4.
“the time between submitting a query and getting its results is longer when the query must wait in a queue before starting.”
↩︎ Queuing: overload or provisioning?“Optimizing a warehouse for query performance is more straightforward when the warehouse runs similar workloads.”
↩︎ Grouping similar workloads“the cost of a performance enhancement might be wasted on a query that does not benefit from the optimization.”
↩︎ Checkpoint
Also cited
“To avoid this problem, use a larger warehouse that has more capacity, or use a warehouse that has fewer concurrent queries.”
↩︎ Checkpoint