Snowflake Query Acceleration Service 101—Guide to Query Performance (2024)

Snowflake Query Acceleration Service (QAS)—exclusively available for Enterprise Edition or above—is a powerful serverless service that can dramatically improve the performance of your Snowflake warehouse. Snowflake Query Acceleration Service (QAS) works by offloading parts of the query workload to shared compute resources, which can significantly reduce the impact of resource-hungry outlier queries. This, in turn, leads to order-of-magnitude performance gains for queries that scan large data volumes or have complex filters.

In this article, we will provide an in-depth overview of Snowflake query acceleration service, explain how it works to improve Snowflake warehouse performance, detail the types of workloads that benefit most, and provide step-by-step guide on enabling, monitoring, and using this feature.

What is Snowflake Query Acceleration Service?

Snowflake Query Acceleration Service (QAS) is a feature built into all Snowflake Virtual Warehouses. Its purpose is to significantly enhance query performance by automatically allocating additional compute resources to handle large table scans. Snowflake Query Acceleration Service acts as a performance booster specifically designed for queries that involve massive data volumes.

How does Snowflake Query Acceleration Service work

Whenever you execute a query in Snowflake, it first checks if there are sufficient resources available to process it. If the cluster is already too busy, the query gets queued and put on hold. This can be a problem when running massive workloads that include both short and long-running queries. Long-running queries can also hinder the execution of other queries. While upgrading to a larger Snowflake warehouse might seem like a viable solution, it is not the optimal approach since smaller queries do not fully utilize the available resources, leading to inflated overall query costs.

Thankfully, the Snowflake Query Acceleration Service (QAS) serves as an additional, powerful cluster that can be temporarily deployed alongside your existing warehouses. When required, it assists with handling resource-intensive tasks. Snowflake Query Acceleration Service resources are billed based on actual CPU usage, ensuring that you only pay for the compute resources utilized.

Snowflake Query Acceleration Service (QAS) can benefit a wide variety of workloads, including:

• Ad hoc analytics
• Workloads with unpredictable data volume per query
• Queries with large scans and selective filters

Source: Snowflake Documentation

Snowflake Query Acceleration Service can help to improve performance for these types of workloads by offloading portions of the query processing work to shared compute resources that are provided by the service. This can help to reduce the wallclock time spent in scanning and filtering, which can lead to significant improvements in overall Snowflake query performance.

TLDR;

As you can see in the illustration, a small warehouse equipped with a Query Acceleration System (QAS) has the capability to scale up by a factor of 8X. Upon detecting a substantial query, the QAS allocates additional resources, enabling quicker performance.

Now, before we dive into the implementation of the Snowflake Query Acceleration Service, it is essential to understand which types of queries and warehouses are eligible for this service.

What types of queries and warehouses are eligible for Snowflake Query Acceleration Service (QAS)?

Snowflake Query Acceleration Service (QAS) is activated at the warehouse level, yet not all queries are eligible to leverage it. As of now, Snowflake QAS supports the following types of queries:

• SELECT
• INSERT (only when the statement includes a SELECT statement)
• CREATE TABLE AS SELECT

These queries must also involve scanning or processing a large amount of data. Keep in mind that the Snowflake Query Acceleration Service is designed to assist a struggling warehouse. If the Snowflake warehouse is not overwhelmed by the query, there is no point in using the Snowflake Query Acceleration Service.

Certain queries fall outside the eligibility bracket for Snowflake Query Acceleration Service due to various reasons, such as:

• Having no filters or aggregation.
• Having filters that do not prune many rows.
• Not having enough partitions to scan.
• Having a LIMIT clause without an ORDER BY clause.

To check whether queries are eligible for Query Acceleration and if they will benefit from enabling it, use the following methods.

Check eligibility using:

1) SYSTEM$ESTIMATE_QUERY_ACCELERATION function

This built-in Snowflake function can be used to determine if a query is eligible for Snowflake Query Acceleration Service. The function returns a JSON object with the following information:

• originalQueryTime: The estimated execution time of the query without Snowflake Query Acceleration service enabled (original query).
• upperLimitScaleFactor: The maximum scale factor that can be used for the query.
• queryUUID: Query ID.
• Status: The status of the query, which can be either eligible or ineligible.
• estimatedQueryTimes: A map of scale factors to estimated execution times.

The following example shows how to use the SYSTEM$ESTIMATE_QUERY_ACCELERATION function to check the eligibility of a query:

select parse_json(system$estimate_query_acceleration('<query_id>'));

If the query is ineligible for Snowflake Query Acceleration service, the status will be ineligible and the upperLimitScaleFactor will be 0. The estimatedQueryExecutionTime field will be null.

For example,

{
  "status": "ineligible",
  "upperLimitScaleFactor": 0,
  "estimatedQueryExecutionTime": null
}

And, if the query is eligible for Snowflake Query Acceleration service, the function will return a status of eligible and the upperLimitScaleFactor will be a positive integer. The estimatedQueryExecutionTime field will include the estimated query execution time for various scale factors.

For example,

{
  "status": "eligible",
  "upperLimitScaleFactor": 10,
  "estimatedQueryExecutionTime": {
    "1": 100 seconds,
    "2": 50 seconds,
    "3": 33.33 seconds,
    "4": 25 seconds,
    "5": 20 seconds,
    "6": 16.67 seconds,
    "7": 14.28 seconds,
    "8": 12.5 seconds,
    "9": 11.11 seconds,
    "10": 10 seconds
  }
}

2) QUERY_ACCELERATION_ELIGIBLE view

QUERY_ACCELERATION_ELIGIBLE view identifies the queries and warehouses that might benefit the most from the Snowflake query acceleration service. The following examples assume the usage of the ACCOUNTADMIN role or a role granted IMPORTED PRIVILEGES on the shared SNOWFLAKE database. If these roles are not in use, execute the following command before running the queries:

USE ROLE ACCOUNTADMIN;

To identify the queries that can benefit the most from the service based on the amount of eligible query execution time, execute the following query:

SELECT query_id, eligible_query_acceleration_time
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_ELIGIBLE
  ORDER BY eligible_query_acceleration_time DESC;

Since Snowflake query acceleration service operates at the warehouse level, it is helpful to determine which warehouses have the highest acceleration opportunities.

This query identifies the warehouses with the most queries eligible for the query acceleration service within a given period:

SELECT warehouse_name, COUNT(query_id) AS num_eligible_queries
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_ELIGIBLE
  WHERE start_time > 'Mon, 29 May 2023 00:00:00'::timestamp
  AND end_time < 'Tue, 30 May 2023 00:00:00'::timestamp
  GROUP BY warehouse_name
  ORDER BY num_eligible_queries DESC;

To identify the warehouses with the most eligible time for the query acceleration service, execute the following query:

SELECT warehouse_name, SUM(eligible_query_acceleration_time) AS total_eligible_time
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_ELIGIBLE
  GROUP BY warehouse_name
  ORDER BY total_eligible_time DESC;

Check out the Snowflake documentation for more!

Before we dive into how to enable Snowflake's Query Acceleration services, let's first understand what Scale Factor is and how it works in QAS.

What is Scale Factor and how it works in Snowflake Query Acceleration (QAS)?

Scale Factor is a cost control mechanism that sets an upper bound limit on the amount of compute resources that a virtual warehouse can use for query acceleration. It acts as a multiplier based on the size and hourly cost of the warehouse.

The Scale Factor sets the maximum capacity, but the actual QAS resources used will vary based on factors like query eligibility, data processed—and resource availability. QAS is billed by the second, only when in use.

The default Scale Factor is 8 if not explicitly set. A value of 0 (Scale factor) removes the upper limit and allows QAS to leverage as many resources as needed and available to accelerate Snowflake query.

Enabling Snowflake Query Acceleration Service

To enable the Snowflake Query Acceleration Service (QAS) in Snowflake, you can use the following steps:

Step1—Enabling Query Acceleration Service during Snowflake warehouse creation:

-- Upon warehouse creation
CREATE WAREHOUSE my_wh
  ENABLE_QUERY_ACCELERATION = true;

This will create a Snowflake warehouse named my_warehouse with Snowflake Query Acceleration Service enabled.

Step2—Enabling Query Acceleration Service on an existing Snowflake warehouse:

-- Enable on an existing warehouse
ALTER WAREHOUSE COMPUTE_WH
  SET ENABLE_QUERY_ACCELERATION = true;

This enables Query Acceleration Service for the existing Snowflake warehouse named my_warehouse.

When using Snowflake Query acceleration service, it's important to consider the cost implications. Snowflake Query acceleration service can increase costs depending on the amount of resources leased by the warehouse.

To control the resource allocation, you can set the maximum scale factor using the QUERY_ACCELERATION_MAX_SCALE_FACTOR warehouse parameter. This parameter determines the multiplier of the current Snowflake warehouse size for leasing compute resources.

To illustrate the scaling capabilities of Snowflake's Query Acceleration Service (QAS), let's consider an example scenario. Suppose let's consider you setup the scale factor to 4 for a medium warehouse.

ALTER WAREHOUSE COMPUTE_WH
  SET ENABLE_QUERY_ACCELERATION = true,
      QUERY_ACCELERATION_MAX_SCALE_FACTOR = 4;

Here's how it would work:

The scale factor determines the maximum compute resources that can be allocated to the warehouse. In this case, the warehouse can lease compute resources up to 4 times the size of a medium warehouse.

Since a medium Snowflake warehouse costs 4 credits per hour, enabling the scale factor of 4 would allow the warehouse to consume additional resources, resulting in a potential cost increase of up to 16 credits per hour (4 credits per Snowflake warehouse x 4 times its size = 16).

Also, you can set the scale factor to 0, which gives Snowflake complete freedom to dynamically scale up Snowflake Query acceleration service based on the query's requirements. This approach provides even greater flexibility in handling queries of any size, allowing Snowflake to allocate resources as needed to optimize query execution.

Monitoring Snowflake query acceleration service

To monitor the usage of Snowflake query acceleration service, you can use the following options:

1) QUERY_HISTORY view:

This view provides information about the queries that have benefited from query acceleration.

It includes the following columns:

• QUERY_ACCELERATION_BYTES_SCANNED: The number of bytes scanned by the query acceleration service.
• QUERY_ACCELERATION_PARTITIONS_SCANNED: The number of partitions scanned by the query acceleration service.
• QUERY_ACCELERATION_UPPER_LIMIT_SCALE_FACTOR: The maximum scale factor that was used for the query.

2) QUERY_ACCELERATION_HISTORY view:

This view provides information about the warehouse-level credit consumption for query acceleration. You can use this view to track the additional cost per Snowflake warehouse for using Snowflake query acceleration service.

The QUERY_ACCELERATION_HISTORY view provides the following columns:

• START_TIME: The start time of the query acceleration session.
• END_TIME: The end time of the query acceleration session.
• CREDITS_USED: The number of credits used for the query acceleration session.
• WAREHOUSE_ID: The ID of the Snowflake warehouse where the query acceleration session occurred.
• WAREHOUSE_NAME: The name of the Snowflake warehouse where the query acceleration session occurred.

Here is an example of a query that you can use to get the Snowflake query acceleration service cost, credits usage, bytes scanned—and more for each Snowflake warehouse :

SELECT
  START_TIME,
  END_TIME,
  CREDITS_USED,
  WAREHOUSE_NAME,
  NUM_FILES_SCANNED,
  NUM_BYTES_SCANNED
FROM
  TABLE(INFORMATION_SCHEMA.QUERY_ACCELERATION_HISTORY())
ORDER BY
  START_TIME DESC;

3) Using Chaos Genius to monitor Snowflake Query Acceleration:

Chaos Genius's advanced Snowflake Cost Explorer allows you to effectively monitor and optimize the Snowflake Query Acceleration Service (QAS). With the help of Chaos Genius, you can:

• Get detailed insights into the costs associated with Snowflake Query Acceleration Service (QAS).
• Identify the factors driving costs for Snowflake Query Acceleration Service QAS.
• Make informed decisions regarding resource allocation and spending for QAS.

Chaos Genius helps monitor and optimize Snowflake Query Acceleration Service (QAS) in the following ways:

• Monitoring QAS costs: Chaos Genius can track the costs incurred by Snowflake QAS usage. It provides detailed costs broken down into simpler manner, which helps gain visibility into how much is being spent on QAS and identify any cost spikes.
• Tracking QAS usage by warehouse: Chaos Genius provide detailed breakdown on which Snowflake warehouses have QAS enabled. This helps to make sure that QAS is enabled only in warehouses where it is useful and providing performance benefits. QAS can be disabled on warehouses where it is not needed to optimize costs.
• Recommendations on enabling/disabling QAS: Based on the query load and performance metrics for Snowflake, Chaos Genius can determine if QAS will help accelerate queries on that warehouse or not. It can then provide recommendations to enable or disable QAS for optimal cost and performance.

Conclusion

In a nutshell, Snowflake Query Acceleration Service (QAS) is like a turbocharger for your SQL queries. It dynamically allocates extra compute power to tackle large table scans, boosting Snowflake query performance without breaking the bank. Ideal for mixed workloads, QAS ensures efficient resource usage and faster query execution. In this article, we covered everything about Snowflake query acceleration service, explain how it works to improve Snowflake warehouse performance, detail the types of workloads that benefit most, and provide step-by-step guide on enabling, monitoring and using this feature

Picture Snowflake Query Acceleration Service as a skilled pit crew in F1 race, seamlessly stepping in to supercharge your car's engine when needed, and then stepping back when the job is done. With the help of Snowflake Query Acceleration Service, you'll be able to speed through data processing and reach the finish line faster than ever before.

FAQs

What is Snowflake Query Acceleration Service (QAS)?

Snowflake Query Acceleration Service (QAS) accelerates query performance by offloading compute-intensive work to dedicated Snowflake servers. It's ideal for queries that scan large datasets.

How does Snowflake Query Acceleration Service (QAS) work?

Snowflake Query Acceleration Service (QAS) automatically detects eligible queries then allocates extra resources to accelerate data scanning and filtering.

What are the benefits of using Snowflake Query Acceleration Service (QAS)?

• Snowflake Query Acceleration Service (QAS) can significantly improve the Snowflake query performance, especially for queries that scan large amounts of data.
• Snowflake Query Acceleration Service (QAS) is a pay-as-you-go feature, so you only pay for the resources that you use. This can help you to reduce your Snowflake costs.
• Snowflake Query Acceleration Service (QAS) can be used to accelerate a wide variety of queries, including ad hoc queries, batch queries, and analytical queries. This gives you more flexibility in how you use Snowflake.

How do I enable Snowflake Query Acceleration Service (QAS) ?

To enable Snowflake Query Acceleration Service (QAS), you first need to do the following:

CREATE WAREHOUSE my_warehouse
ENABLE_QUERY_ACCELERATION = TRUE;

Or, if you already have a warehouse created, you can enable QAS by using the ALTER WAREHOUSE statement:

ALTER WAREHOUSE my_warehouse
ENABLE_QUERY_ACCELERATION = TRUE;

Once Snowflake Query Acceleration Service (QAS) is enabled, it will be automatically used to accelerate eligible queries.

What are the limitations of Snowflake Query Acceleration Service (QAS)?

• Doesn't accelerate queries that don't scan data
• Can't accelerate ineligible queries
• Adds minor overhead

Is Snowflake Query Acceleration Service (QAS) right for me?

Yes, Snowflake Query Acceleration Service (QAS) is a great option for users who want to improve the performance and reduce the costs of their Snowflake queries. If you are looking for ways to improve the performance of your Snowflake queries, then Snowflake Query Acceleration Service (QAS) is a great option to consider.

What is the difference between Snowflake Query Acceleration Service (QAS) and Multi Cluster Warehouse (MCW)?

Snowflake QAS accelerates individual queries by temporarily provisioning additional virtual warehouses to provide extra compute resources for just those specific queries needing it. In contrast, a Multi-Cluster Warehouse MCW isolates different workloads across multiple permanent clusters optimized specifically for those workloads. This enables concurrency, workload isolation, and optimization across diverse workloads with differing performance needs.