page105
87
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 leave it to you to refine the previous queries.
Long table scan monitoring for trends is worth considering as this metric may be a
leading indicator of future performance issues. I suggest this metric can provide early
warning of data skewing.
Long Table Scan Costs
Assuming you can exclude long table scans due to known and expected behaviors, the
remaining long table scan candidates can be caused by many factors.
•
Micro-partition fragmentation due to a high number of low-volume
inserts and updates
•
Query predicates not matching table cluster key
•
Range scan filtering occurring when using
BETWEEN
,
LIKE
,
<>
, and
similar operators
Remediating Long Table Scan Queries
Automatic clustering can remediate long execution times by re-ordering micro-partition
content into optimally efficient form. Note that automatic clustering can invalidate
cached results. I discuss automatic clustering in detail in the next chapter. You can
find further details at
https://docs.snowflake.com/en/user-guide/tables-auto-
reclustering
.
For existing tables where the cluster key does not match the query predicates,
consider adding a materialized view. Consider using dynamic tables to offload work onto
serverless compute. Note the refresh time may be a factor in your decision-making.
Improve query selectivity to match the cluster key definition on the target table.
Consider using a query acceleration service to dynamically scale and parallelize
portions of the query plan leading to overall reduced runtime. I discuss query
acceleration services in detail later within this book. You can find further details at
https://docs.snowflake.com/en/user-guide/query-acceleration-service
.
ChapTer 3
The Query profiler