page108
90
bytes_spilled_to_local_storage NUMBER,
bytes_spilled_to_remote_storage NUMBER,
bytes_sent_over_the_network NUMBER
);
The latency for
QUERY_HISTORY
may be up to 45 minutes.
You can now insert the candidate records from
QUERY_HISTORY
; in this example, you
will identify those records where both local and remote spills to storage have occurred.
In other words, both values are greater than zero. You may want to change the predicates
to suit your requirements.
INSERT INTO spill_and_OOM
SELECT query_id,
warehouse_name,
warehouse_size,
bytes_spilled_to_local_storage,
bytes_spilled_to_remote_storage,
bytes_sent_over_the_network
FROM snowflake.account_usage.query_history
WHERE warehouse_name IS NOT NULL //Exclude cached results
AND bytes_spilled_to_local_storage > 0;
You can find more information on
QUERY_HISTORY
at
https://docs.snowflake.com/
en/sql-reference/account-usage/query_history
.
You can examine summary information for queries where spills and potential OOMs
have occurred.
SELECT query_id,
warehouse_name,
warehouse_size,
bytes_spilled_to_local_storage,
bytes_spilled_to_remote_storage,
bytes_sent_over_the_network
FROM spill_and_OOM;
ChapTer 3
The Query profiler