1 0
Read Time:4 Minute, 17 Second

During this post we will discuss one of the important Snowflake Capability i.e. Query Acceleration Service a.k.a. (QAS). When statement is submitted to a warehouse, Snowflake allocates resources for executing the statement. If there aren’t enough resources available, the statement is queued or additional warehouses are started, depending on the warehouse. Consider the scenario where a long Runner has been submitted on WH which has eaten up almost all WH resources.Hence, this has blocked other short running queries as well.

Therefore, To handle such scenario we can opt below two approaches:

  • Scale up your current Warehouse.
  • Enable Multiclustering in your account.

Scaling up improves performance for long runner but what about in case of small queries. In other words, we are wasting WH credits as smaller queries not fully utilizing the Large Warehouse capabilities. Instead, XS Warehouse is sufficient to handle other query loads.

Therefore, Query Acceleration Service plays a very critical role by bumping up the additional database resources temporarily to complete the query execution. Once the query gets complete, additional resources gets release. QAS uses the large/bigger warehouse based on the Scale factor we define and apply to the scanning of large tables. So in other words you would be running your query on your current WH i.e. X-SMALL. But QAS, Serverless service based on the scale factor will include more database resources and complete the table scan. Once the Query execution over, resources are released automatically without any manual intervention.

“The query acceleration service might reallocate resources dynamically at any time. Performance improvements can vary for the same query executed at different times.”

Technical implementation:

Technical implementation:

However,To identify the queries that might benefit from the query acceleration service, you can use the SYSTEM$ESTIMATE_QUERY_ACCELERATION function or query the QUERY_ACCELERATION_ELIGIBLE View.

  • SYSTEM$ESTIMATE_QUERY_ACCELERATION:

select parse_json(system$estimate_query_acceleration(’01a6be40-0000-bc71-0002-a75a0007c4c2′));

{
"estimatedQueryTimes": {
"1": 80,

"2": 70,


"4": 62,


"8": 57

},
"originalQueryTime": 103.672,
"queryUUID": "01a6be40-0000-bc71-0002-a75a0007c4c2",
"status": "eligible",
"upperLimitScaleFactor": 8
}

Note: Query acceleration might benefit a specific query. In above output QAS can improve the performance depends on the query_acceleration_max_scale_factor value.

While it is not always true and sometimes query is not eligible for query acceleration service.

select parse_json(system$estimate_query_acceleration(’01a6beaf-0000-bc71-0002-a75a0007c72a’));

{
"estimatedQueryTimes": {},
"originalQueryTime": 0.879,
"queryUUID": "01a6beaf-0000-bc71-0002-a75a0007c72a",
"status": "ineligible",
"upperLimitScaleFactor": 0
}

  • QUERY_ACCELERATION_ELIGIBLE View:

Moreover, Query the ACCELERATION_ELIGIBLE view to identify the queries and warehouses that might benefit the most from the query acceleration service.

select query_id, eligible_query_acceleration_time
from snowflake.account_usage.query_acceleration_eligible
where warehouse_name = 'XTRA_SMALL_WH'
order by eligible_query_acceleration_time desc;

ACCELERATION_ELIGIBLE
How to Enable Query Acceleration:

a) How to Enable Query Acceleration:

Firstly, Enable the query acceleration service by specifying ENABLE_QUERY_ACCELERATION = TRUE when creating a warehouse (using CREATE WAREHOUSE) or later (using ALTER WAREHOUSE).

alter warehouse XTRA_SMALL_WH set enable_query_acceleration = true query_acceleration_max_scale_factor = 16;

Hence,this will allow database server can automatically scale up to 16 servers.

b) How To Monitoring Query Acceleration Service Usage:

Secondly, We will execute the below query without enabling the QAS on Warehouse and observe the timings.

select d_year AS ss_sold_year, i.i_brand_id ,i.i_brand ,
sum(ss_quantity) ss_qty,sum(ss_wholesale_cost) ss_wc,sum(ss_sales_price) ss_sp
from store_sales s,store_returns sr,item i,date_dim d
where
s.ss_ticket_number = sr.sr_ticket_number and s.ss_sold_date_sk = d.d_date_sk and
s.ss_item_sk = i.i_item_sk and i.i_manufact_id = 939 and d_qoy = 4
group by d_year, i_brand_id, i_brand
order by 1,4,5,2
limit 200;

Profile overview

Hence, we can see the Query took more than 7 minutes to execute. In addition,we will run  estimate_query_acceleration function to see if Query will be benefitted with QAS.

select parse_json(system$estimate_query_acceleration('01a6bea7-0000-bcaa-0002-a75a0007b792'));

QAS Estimation

We can clearly see the Query is eligible for acceleration service and hence will enable it on Warehouse.

c) Thirdly,Enable the QAS Service:

ALTER SESSION SET use_cached_result = FALSE;


alter warehouse XTRA_SMALL_WH set enable_query_acceleration = true query_acceleration_max_scale_factor = 16;

Acceleration Scale Factor

Later, Now Run query and analyze the Query Profile:

QAS Service Profile

d) Above all, Viewing Billing Using the QUERY_ACCELERATION_HISTORY Function:

select * from
table(information_schema.query_acceleration_history(
date_range_start=>dateadd(h, -12, current_timestamp)));

QAS Billing

In conclusion, view the usage about the Warehouses.

QAS Profile

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

Leave a Reply

Your email address will not be published. Required fields are marked *