page96
78
You can now insert candidate records from
QUERY_HISTORY
. Note that you only want
records where the compilation time exceeds the execution time.
INSERT INTO long_compilation_time
SELECT query_id,
warehouse_name,
warehouse_size,
compilation_time,
CASE execution_time
WHEN 0 THEN 1
ELSE execution_time
END AS execution_time_1,
compilation_time / execution_time_1
FROM snowflake.account_usage.query_history
WHERE ( compilation_time / execution_time_1 ) > 1;
You can find further information on
QUERY_HISTORY
at
https://docs.snowflake.
com/en/sql-reference/account-usage/query_history
.
You can examine summary information for queries where the compilation time
exceeds the execution time.
SELECT query_id,
warehouse_name,
warehouse_size,
compilation_time_ms,
execution_time_ms,
time_multiple
FROM long_compilation_time;
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 compilation time monitoring for trends is worth considering as this metric may
be a leading indicator of future performance issues.
ChapTer 3
The Query profiler