page109
91
From the previous result set, use
GET_QUERY_OPERATOR_STATS
to examine statistics
for individual operators within a single query.
SELECT *
FROM TABLE ( get_query_operator_stats('<your query_id here>'));
I will leave it to you to refine the previous queries.
I highly recommend monitoring for spills as this metric is a leading indicator of
future problems and may identify remediation opportunities well before OOMs occur.
Spills to Disk and OOM Costs
Earlier in this section I explained the root cause of spills to disk and OOMs; I now list
some additional factors here:
•
High workload concurrency where the warehouse cannot service all
queries at the same time
•
Unexpectedly high volumes of data processed
•
Incorrectly sized warehouse for workload
•
Large intermediate result sets
Remediating Spills to Disk and OOM Queries
Automatic clustering can remediate long execution times by re-ordering micro-partition
content into an optimally efficient form. Note that automatic clustering can invalidate
cached results. I will discuss automatic clustering in detail within the next chapter. You
can find further details at
https://docs.snowflake.com/en/user-guide/tables-auto-
reclustering
.
Reducing warehouse concurrency by segregating workloads into separate
warehouses may free sufficient resources to remediate disk spills. Alternatively,
increasing warehouse size will increase available resources, enabling more optimal in-
memory operations, and reduce both local and remote spills to disk.
The table join order can also be significant. Start with the smallest tables first as
this may eliminate the greatest number of micro-partitions early within the query
optimization stage and then in cardinality order up to the highest cardinality last. Also
check the filter criteria are sufficiently selective to improve micro-partition pruning.
ChapTer 3
The Query profiler