Query Acceleration Service in Snowflake

1. Introduction

In our previous article on Virtual Warehouses, we have discussed about Multi-cluster warehouses which enables you to automatically scale out compute resources to manage concurrent queries during peak hours.

Similar to multi-cluster warehouses which lets you automatically scale out your compute resources horizontally (i.e. add more resources of same size), there is no feature in Snowflake which lets you automatically scale up your compute resources vertically (i.e. resizing of warehouse) to improve performance of slow-running queries.

Snowflake’s solution to dynamically improve performance of slow-running queries is Query Acceleration Service (QAS). Let us understand more about this service in this article.

2. What is Query Acceleration Service in Snowflake?

The Query Acceleration Service is a feature that can be enabled for a Virtual Warehouse which accelerates parts of query workload automatically by offloading portions of query processing to the additional compute resources provided by the service. These additional resources provisioned by QAS work in parallel reducing the overall time spent in scanning and filtering.

The queries that benefit from Query Acceleration Service are:

• Queries with large scans and selective filters.
• Workloads with unpredictable data volume per query.
• Adhoc analytics.

Note that Query Acceleration Service is an Enterprise Edition (or higher) feature.

3. How Query Acceleration Service in Snowflake works?

Consider a huge query is running on an SMALL warehouse (2 credits per hour), it results in a poor query performance and also results in blocking all other queries from running.

While huge queries running on an X-LARGE warehouse will undoubtedly run faster, this is not an ideal solution when you have only a small number of large queries to run and many small queries. This leads to higher overall running costs as the smaller queries don’t make full use of the compute resources, but the cluster is still charged at a rate of 16 credits per hour.

The Query Acceleration Service enabled on an SMALL warehouse can automatically detect when a huge query needs more resources and leases additional compute resources to complete the query, and then release the resources when the query is finished. There by reduces the overall cost compared to using a warehouse of bigger size.

The number of resources that the QAS can lease can be controlled using Scale Factor defined for QAS.

The above illustration shows that an SMALL warehouse with QAS enabled up to 8X scale factor detects a huge query. Then the QAS deploys additional resources which helps in extracting data faster.

The Query Acceleration Service effectively functions as a group of resources that are temporarily deployable alongside your current warehouse and when needed, takes on some of the heavy lifting.

4. How to enable Query Acceleration Service in Snowflake?

The Query Acceleration Service can be enabled while creating the Virtual Warehouse from Snowsight as Shown below.

The following is the SQL statement that enables Query Acceleration Service while creating the Virtual Warehouse.

CREATE WAREHOUSE <warehouse_name> WITH
  WAREHOUSE_SIZE='<warehouse_size>'
  ENABLE_QUERY_ACCELERATION = true
  QUERY_ACCELERATION_MAX_SCALE_FACTOR = <upper_limit_scale_factor>
  INITIALLY_SUSPENDED = true
  AUTO_SUSPEND = 60;

The Query Acceleration Service can also be enabled for an existing Virtual Warehouse using the below ALTER statement.

ALTER WAREHOUSE <warehouse_name>
   ENABLE_QUERY_ACCELERATION = true
   QUERY_ACCELERATION_MAX_SCALE_FACTOR = <upper_limit_scale_factor>;

5. What is Scale Factor in Query Acceleration Service?

The Scale Factor in Query Acceleration Service is a control mechanism which lets you select the maximum number of the compute resources a warehouse can lease for Query Acceleration. The scale factor is a multiplier value for number of compute resources of same warehouse size and cost.

For example, if you set a scale factor to 8 for a SMALL warehouse.

• The warehouse can lease compute resources up to 8 times the size of SMALL warehouse.
• As the SMALL warehouse costs 2 credits per hour, leasing these resources would cost up to 16 credits per hour ( 2 credits per hour X 8 times compute resources)

Few key points related to Scale Factor

• The default value of Scale Factor is 8 if it is not explicitly set.
• Setting the scale factor to 0 eliminates the upper bound limit and allows queries to lease as many resources as necessary and available to execute the query.
• The Query Acceleration Service is billed by the second, only when the service is in use. These credits are billed separately from warehouse usage.
• Snowflake automatically determines whether the query would benefit from using the Query Acceleration Service, and will only deploy this if it’s estimated to improve query performance and overall throughput.

6. Demonstration of Query Acceleration Service

For the demonstration, let us use two SMALL warehouses to run a same query, one with query acceleration service enabled (DEMO_QAS_WH) and the other disabled (DEMO_WH).

Below is the query used for demonstration which would be run using two different warehouses mentioned above.

USE SCHEMA snowflake_sample_data.tpcds_sf10tcl;

select store.s_store_id, item.i_item_id, sum(ss_sales_price) ss_sales_price
from store_sales
    ,item
    ,time_dim, store
where ss_sold_time_sk = time_dim.t_time_sk
    and ss_item_sk = item.i_item_sk
    and ss_store_sk = s_store_sk
    and time_dim.t_hour = 8
    and time_dim.t_minute >= 30
    and store.s_store_name = 'ese'
group by store.s_store_id, item.i_item_id
;

6.1. Running the Query without QAS

The below image shows that the query took almost 7 minutes to execute using DEMO_WH warehouse without QAS enabled.

6.2. Running the Query with QAS

The below image shows that the query took only 1 minute to execute using DEMO_QAS_WH warehouse with QAS enabled.

The below images shows the query profile of both queries that ran without and with QAS enabled.

• Both the queries took most of the time in TableScan, extracting data from database storage.
• There is a huge improvement in the query runtime from 7 minutes to 1 minute with QAS enabled.
• In the query with QAS enabled, out of 86804 partitions, 78146 partitions are scanned by the resources provisioned by QAS service and only 8786 partitions are scanned by the actual resources of the warehouse.
  This led to massive improvement in the performance and execution time with same warehouse and size.

Query Without QAS

Query with QAS Enabled

7. Which queries are supported by Query Acceleration Service?

Currently the Query Acceleration Service can only accelerate certain parts of query which includes fetching data and filtering out the results. However, these are the parts of the query which take most of the execution time. Also the amount of data that is being extracted must be huge for the query to be eligible for query acceleration.

The queries which include the following SQL commands are supported by QAS.

• SELECT
• INSERT (when the statement contains a SELECT statement)
• CREATE TABLE AS SELECT (CTAS)

The parts of query which does aggregation, sorting and other steps are not supported by Query Acceleration Service. Although this may be supported in upcoming releases.  

8. How to identify Queries that take advantage of Query Acceleration Service?

To identify if a query is eligible for Query Acceleration Service, you can use the SYSTEM$ESTIMATE_QUERY_ACCELERATION function as shown below.

SELECT PARSE_JSON(SYSTEM$ESTIMATE_QUERY_ACCELERATION('<query_id>'));

Below is the output using the query_id of the query which we ran using the DEMO_WH in step 6.1. which shows that the query is eligible for enabling Query Acceleration service. It also suggests that setting the scale factor to 9 for better performance. It also provides estimated query times for different scale factors.

SELECT PARSE_JSON(SYSTEM$ESTIMATE_QUERY_ACCELERATION('01ad34dc-3200-c556-0004-cb9a0001907e'));

The QUERY_ACCELERATION_ELIGIBLE view also identifies queries along with warehouses that might benefit from the query acceleration service.

SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_ELIGIBLE;

9. How to find the Billing cost of Query Acceleration?

Query Acceleration in Snowflake consumes credits for the amount of time the additional resources are leased.

The billing data of Query Acceleration Service can be found using the QUERY_ACCELEARTION_HISTORY view in ACCOUNT_USAGE schema as shown in below query.

SELECT warehouse_name,
       SUM(credits_used) AS total_credits_used
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_HISTORY
  WHERE start_time >= DATEADD(hour, -24, CURRENT_TIMESTAMP())
  GROUP BY 1
  ORDER BY 2 DESC;

The billing data of Query Acceleration Service can also be found using the QUERY_ACCELEARTION_HISTORY function in INFORMATION_SCHEMA as shown in below query.

SELECT start_time, end_time, credits_used, warehouse_name
  FROM TABLE(INFORMATION_SCHEMA.QUERY_ACCELERATION_HISTORY(
    date_range_start=>DATEADD(H, -24, CURRENT_TIMESTAMP)));

10. Conclusion

QAS is a great feature to improve the query performance of long running queries on the existing warehouse without the need of scaling up the warehouse size.

QAS is enabled only if Snowflake identifies that the query would be benefitted from additional compute resources. The QAS feature also comes with additional cost. Adjust the scale factor to control the amount of resources that a warehouse can lease.

Subscribe to our Newsletter !!

Related Articles: