page298
386
File Size Optimization
The recommended file size for Snowpipe and
COPY
commands is 100MB to 250MB
compressed. Ingesting smaller files leads to both increased cost and longer warehouse
runtimes.
Check All Tasks
Tasks may fail for a variety of reasons, many of which can be diagnosed from information
found here:
https://docs.snowflake.com/en/user-guide/tasks-ts
. Tasks may auto-
suspend according to the value of the parameter
SUSPEND_TASK_AFTER_NUM_FAILURES
for
which further information can be found here:
https://docs.snowflake.com/en/sql-
reference/parameters#suspend-task-after-num-failures
.
For tasks dependent upon streams, when the stream goes stale, the task will fail. The
parameter
MAX_DATA_EXTENSION_TIME_IN_DAYS
can be set independent of the parent
table; see
https://docs.snowflake.com/en/sql-reference/parameters#max-data-
extension-time-in-days
.
Periodically check tasks to see if any can be disabled or, better still, removed.
Check task start times to determine whether tasks can be retimed and/or
consolidated into fewer warehouses to parallelize processing.
Session Settings
This section illustrates some useful SQL statements to aid your testing.
Statement Timeout
While testing code, you may prefer to set
statement_timeout_in_seconds
in the
current session to avoid overspend. In this example, I set the timeout to 600 seconds (10
minutes), but note the smaller of the user or warehouse setting applies:
ALTER SESSION SET statement_timeout_in_seconds = 600;
Statement Queueing
Except when set to zero, Snowflake automatically cancels individual queries queued in
excess of
statement_queued_timeout_in_seconds
.
ChapTer 10
OpTimizing perfOrmanCe