Query Acceleration Service

Query Acceleration Service

Overview

Snowflake’s Query Acceleration Service (QAS) enhances the efficiency of a warehouse by expediting specific segments of its query workload for accounts on Enterprise Edition (or higher). Once enabled for a warehouse, it holds the potential to boost overall performance by mitigating the impact of outlier queries – those demanding more resources than the typical query. This is achieved through delegation of certain aspects of query processing tasks to the service’s shared compute resources, effectively streamlining and optimizing the overall query handling process.

Workloads that might benefit from QAS include:

  • ad hoc analytics
  • workloads with unpredictable volume per query
  • queries with large scans and selective filters

QAS makes these workloads more efficient by

  • maximizing parallel work which reduces time spent sorting and organizing information
  • offloading tasks involved in handling queries to the service’s shared compute resources

QAS depends on server availability which may cause performance fluctuations over time. What that means for you: on Monday the query is accelerated and on Wednesday that may not happen because the server isn’t available at the desired time.

Slingshot allows Warehouse Owners to easily identify and manage QAS enabled warehouses. The QAS column shows which warehouses have QAS enabled or disabled in the My Warehouses list.

Managed warehouses also display QAS info in Warehouse details, Approvals list, and Recommendations.

When creating, managing, or modifying a warehouse, toggle QAS to enable or disable. Set the Scale factor to manage compute resource allocations for QAS to use when it’s enabled.

Here’s what Snowflake says about adjusting the Scale Factor.

Slingshot’s schedule empowers users to dynamically change warehouse size and other parameters by hour and day of the week, while QAS scale factor acts as a multiplier based on warehouse size and credits and sets an upper bound on the amount of compute a warehouse can lease for query acceleration. QAS’s scale factor is based on the warehouse schedule block.
Here’s an example:

  • QAS scale factor is 8
  • Midnight - 7am Warehouse Size: XS (1 credit*8 = 8 credits)
  • 7am - 5pm Warehouse Size: M (4 credits*8 = 32 credits)
  • 5pm - 11:59pm Warehouse Size: S (2 credits*8 = 16 credits)

Notice the QAS scale factor 8 multiplying different warehouse sizes trends towards the upper ranges of compute resources being leased. Here’s a Warehouse Size x Scale Factor table for reference.

Let’s jump into how to use QAS with Slingshot Recommendations.

QAS and Recommendations

ℹ️
When applying recommendations, the QAS toggle is disabled. To adjust QAS, apply the recommendation, then modify or manage the warehouse to turn QAS on or off.

QAS and Slingshot work as complementary partners. Slingshot’s recommendation engine scans more than 2160 hours of usage to give a recommendation. It has a good understanding of the warehouse’s schedule and finds patterns that are difficult to analyze manually for each warehouse. Each month it keeps analyzing the data to provide newer recommendations.

Slingshot recommends the best schedule by suggesting an increase or decrease by one level for warehouse size, cluster count, or it suggests decreasing warehouse size and increasing cluster count. QAS’s scale factor, as noted previously, is based on the warehouse schedule block.

Consider the following when deciding how to leverage QAS and Recommendations:

Query Acceleration ServiceRecommendations
Increasing clusterIncrease size
Query-based
(Insert, Select or CTAS SQL)
Warehouse-based, timeblock-based in Slingshot’s scheduleWarehouse-based; timeblock-based in Slingshot’s schedule
Best for adhoc loads which require scanning a billion of rows - Larger workloads run on the smaller warehouse but let Snowflake automatically and temporarily scale up when it needs extra horsepowerBest for concurrent - multi-clusters scale out to provide more compute resources for concurrent workloadsBest for huge load or frequent scanning of huge dataset - scaling up improves performance for long running queries all the time regardless of query loads
When a warehouse handles both big and small queries, it wastes WH credits as smaller queries aren’t fully utilizing the capabilities of a Large Warehouse

Slingshot provides the visibility and suggestions based on warehouse usage patterns, but you always have the power to tweak recommendations and adjust scale factors to suit your business needs.