2
1
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 = 'COMPUTE_WH'
order by eligible_query_acceleration_time desc;
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 COMPUTE_WH
set enable_query_acceleration = true
query_acceleration_max_scale_factor = <<2,4,8,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;
Hence, we can see the Query took around 5 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('01a8df6e-0201-40ce-0001-121200026192');
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 COMPUTE_WH
set enable_query_acceleration = true
query_acceleration_max_scale_factor = 16;
Later, Now Run query and analyze the Query 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)));
To Conclude, if we see adhoc load on the warehouse which requires to scan billion of Rows then QAS would be option. Or in case we see huge load or frequent scanning of huge dataset then scale up is best option.
