page314
402
Long Table Scan
A long table scan occurs where most of the processing time is spent servicing remote
disk I/O. This query references
partition…
information available only from the Account
Usage Store; note that latency of up to 45 minutes applies. Entries are only inserted in
snowflake.account_usage.query_history
after the statement runs completely or is
cancelled.
SELECT query_id,
warehouse_name,
warehouse_size,
partitions_scanned / partitions_total AS partition_scan_ratio,
partitions_scanned,
partitions_total
FROM snowflake.account_usage.query_history
WHERE warehouse_name IS NOT NULL
AND execution_status = 'SUCCESS'
AND bytes_scanned > 0
AND total_elapsed_time > 1000
AND ( partitions_scanned / partitions_total ) > 0.5;
Guidance on resolving long table scan is provided in Chapter 3.
Spills to Disk and Out of Memory
Spills to disk are identified by examining the
bytes_spilled…
attributes. This query
references
bytes_spilled…
information available from the Account Usage Store; note
that latency of up to 45 minutes applies.
SELECT query_id,
warehouse_name,
warehouse_size,
bytes_spilled_to_local_storage,
bytes_spilled_to_remote_storage,
bytes_sent_over_the_network
ChapTer 10
OpTimizing perfOrmanCe