Finding Slow Snowflake Queries with Query History Telemetry

1.Start with Snowsight Query History

Before you can fix a slow query, you have to find it. People often blame a warehouse when one or two statements are really the problem, so the first job is to rank queries by how long they took. In Snowsight, go to Monitoring » Query History. The Duration column shows how long each statement took to execute. Sort on it and the longest-running queries come to the top.

On a busy account the full list is noisy, so filter it. The User drop-down limits the list to one person's queries. Filters » Warehouse limits it to one warehouse, which is what you want when one warehouse seems to be slowing down. You can also filter by Status (for example to find long-running, failed or queued queries), Statement Type, SQL Text, Query Tag, Session ID and Duration. The page covers the last 14 days. You can add optional columns such as Warehouse Size, Bytes Scanned and Rows, which give a first clue about whether a slow query was simply reading a lot of data.

What you can see depends on your role. You can always see your own queries. ACCOUNTADMIN sees all query history for the account. A role with MONITOR or OPERATE on a warehouse can see other users' queries on that warehouse. Two practical details: details for queries more than seven days old do not include User information, and once more results are loaded the table can no longer be sorted. If you sort and then select Load More, the new rows are appended at the end and the sort order no longer applies.

Sources12

2.Going beyond 14 days: QUERY_HISTORY in SQL

Snowsight is good for looking around. For repeatable reports, trends or anything older than two weeks, query the history with SQL. There are two places to do that, and they differ in ways the exam tests. The ACCOUNT_USAGE.QUERY_HISTORY view keeps a year of history but has a delay. The Information Schema QUERY_HISTORY table functions are updated faster but only cover the past 7 days. By default, ACCOUNT_USAGE is visible only to ACCOUNTADMIN. Users without that access, such as the person who ran the query or a warehouse administrator, can still use the Information Schema functions.

The documentation's starting query lists the 50 longest-running successful queries on one warehouse over the last day. It also returns partitions_scanned next to partitions_total, so a long runtime can be compared against how much of the table was read. Widen the DATEADD window to look at a longer period.

SELECT query_id,
  ROW_NUMBER() OVER(ORDER BY partitions_scanned DESC) AS query_id_int,
  query_text,
  total_elapsed_time/1000 AS query_execution_time_seconds,
  partitions_scanned,
  partitions_total,
FROM snowflake.account_usage.query_history Q
WHERE warehouse_name = 'my_warehouse' AND TO_DATE(Q.start_time) > DATEADD(day,-1,TO_DATE(CURRENT_TIMESTAMP()))
  AND total_elapsed_time > 0 --only get queries that actually used compute
  AND error_code IS NULL
  AND partitions_scanned IS NOT NULL
ORDER BY total_elapsed_time desc
LIMIT 50;

QUERY_HISTORY is one layer of the telemetry that surrounds a slow operation. Before choosing a fix, outline what each layer can tell you. The statement layer (QUERY_HISTORY in Snowsight, ACCOUNT_USAGE or the Information Schema) gives per-query totals: elapsed, compilation and execution time, queueing, bytes scanned, spill and partitions scanned. The operator layer is the Query Profile, opened from a query ID in Query History. Its Most Expensive Nodes pane shows which operators ran longest, and you can run GET_QUERY_OPERATOR_STATS to get the same statistics in SQL. The insight layer flags conditions that affect performance, such as a join with no join condition or remote spillage. The warehouse layer (the Warehouse Activity chart and WAREHOUSE_LOAD_HISTORY) shows whether queries were queued by load. The task layer (TASK_HISTORY) shows how long scheduled runs took. Work from the wide view to the narrow one: find the statement, check whether the time was waiting or work, then drill into the operators.

Sources13

3.Consistently slow or a one-off? Query hashes

A single slow run tells you little. The same statement might have run fine a hundred times and slowed down once because of contention. Or a query that takes three seconds might run ten thousand times a day and cost more than any big report. To see either case you need to group runs of the same query together, and QUERY_HISTORY provides two keys for this:

- query_hash is computed from the canonicalized SQL text, so identical statements share it. - query_parameterized_hash is computed from the parameterized query, so statements that differ only in literal values (for example, a different user ID) share it.

Grouping by query_hash and summing elapsed time puts the most expensive statements first, counting every run, which is often a better place to start optimizing than the single slowest run.

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;

To check whether one pattern has drifted, track its daily average elapsed time over a month. A flat line with one spike points to an outlier. A steady rise points to a lasting regression, such as data growth or a plan change. Snowsight shows the same view graphically: Monitoring » Query History » Grouped Queries groups executions by parameterized query hash and shows run count, failures, p50/p90/p99 latency and executions per minute. It is based on the AGGREGATE_QUERY_HISTORY view, so new queries can take up to three hours to appear.

SELECT
    DATE_TRUNC('day', start_time),
    AVG(total_elapsed_time),
    ANY_VALUE(query_id)
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
  WHERE  ?  = 'cbd58379a88c37ed6cc0ecfebb053b03'
    AND DATE_TRUNC('day', start_time) >= CURRENT_DATE() - 30
  GROUP BY DATE_TRUNC('day', start_time);

Sources2

4.Reading where the time went

total_elapsed_time is only the headline figure. QUERY_HISTORY splits it into parts, and each part points to a different kind of cause. Some columns describe *waiting* (a warehouse problem or a concurrency problem). Others describe *work* (a query or data-layout problem). Look at these columns before you rewrite any SQL.

For queueing, also check the warehouse as a whole. In Snowsight, Compute » Warehouses has a Warehouse Activity chart that shows load and whether queries were queued. In SQL, WAREHOUSE_LOAD_HISTORY (latency up to 3 hours) reports load as a ratio. For example, 276 seconds of query time in a 300-second interval gives a load of 0.92. The query below finds the days on which a warehouse had queued load. For pipelines, TASK_HISTORY gives the same kind of view for tasks: sorting successful runs by duration shows which task SQL is worth optimizing. Snowsight shows task timings under Transformation » Tasks.

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;

Sources1

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.The Information Schema QUERY_HISTORY table function returns the same year of history as the ACCOUNT_USAGE view, only faster.Why is that wrong?

   The table function covers only the past 7 days. The ACCOUNT_USAGE view keeps 365 days but can lag by up to 45 minutes.

   Covered in Going beyond 14 days: QUERY_HISTORY in SQL

2. 2.After sorting Query History by Duration and selecting Load More, the full list is still in duration order.Why is that wrong?

   Once more results are loaded the table can't be sorted. New rows are appended at the end and the earlier sort no longer applies.

   Covered in Start with Snowsight Query History

3. 3.The query to optimize first is always the one with the longest single run.Why is that wrong?

   A cheap query that runs very often can cost more in total. Grouping by query hash and summing elapsed time shows this.

   Covered in Consistently slow or a one-off? Query hashes