In continuation of previous post i.e. Snowflake Parameters Insight, here is the addon list of the useful parameters.
5.USE_CACHED_RESULT: Specifies whether to reuse cache query results, if available, when a matching query is submits.
When a query gets submit, Snowflake checks for matching query results for previously-execute queries and, if a matching result exists, uses the result instead of executing the query. This can help reduce query time because Snowflake retrieves the result directly from the cache.
First Time Query execute : Fetches the data from Remote Disk
select * from INVOICELOAD.
Execute the Same query: This time query completes in few milliseconds. It fetch the data from Result cache instead of getting query on Virtual warehouse layer. So no processing cost involve.
Result Cache second run
6. STATEMENT_QUEUED_TIMEOUT_IN_SECONDS: How long a query should be allow to wait in a warehouse queue before the system cancels it. The most common condition is queuing due to load.
When the warehouse already busy, and execute another query ,this cause performance degradation, the query is queue and waits until the necessary resources become available. Once queued, a query waits for upwards of the amount of time specified by the STATEMENT_QUEUED_TIMEOUT_IN_SECONDS parameter in seconds. If the timeout length is exceed, the query gets abort.
7. MAX_CONCURRENCY_LEVEL: tells Snowflake how many SQL statements can run in parallel in a warehouse. The number of queries that can execute in parallel on a warehouse is configurable by setting the parameter MAX_CONCURRENCY_LEVEL.
When the number of submitted SQL statements reach this threshold, any new SQL statements may or may not be placed in a queue. This would control by STATEMENT_QUEUED_TIMEOUT_IN_SECONDS parameter. Once queue, a query waits for upwards of the amount of time specified by the STATEMENT_QUEUED_TIMEOUT_IN_SECONDS parameter in seconds. If the timeout length is exceeds, the query is aborts.
It is generally not recommended to increase this parameter, as an increased number of queries executing in parallel can lead to decreased performance.
This is a session type parameter. It can be set at the account level, for a user or for a session. As the name suggests, this parameter tells Snowflake how to display dates. Unless there is a need to change the display format for reporting, use the default format of YYYY-MM-DD.
9. CREATE RESOURCE MONITOR
This command executes by account administrators. Moreover, Resource monitors are not intend for strictly controlling consumption on an hourly basis; they intended for tracking and controlling credit consumption per interval (day, week, month, etc.).
create or replace resource monitor monitor_freq1 with credit_quota=120
frequency = monthly
start_timestamp = immediately
triggers on 50 percent do notify
on 75 percent do notify
on 100 percent do suspend
on 110 percent do suspend_immediate;
alter warehouse compute_wh set resource_monitor = monitor_freq1
To learn more parameters, please click here.