0
0
Evaluating Query Acceleration Service Before You Enable: Query Acceleration Service (QAS) is one of Snowflake’s powerful features designed to accelerate large, scan-intensive queries by offloading portions of query processing to shared compute resources. However, the common question every Snowflake administrator faces is: “Should I enable QAS on my warehouse, and if so, what scale factor should I use?”
The Challenge
The Challenge
Before enabling QAS, organizations typically struggle with several questions:
- How many of our long-running queries are actually eligible for QAS?
- What performance improvement can we realistically expect?
- What is the optimal scale factor that balances performance and cost?
The Solution: QAS Evaluation Process
This Process leverages Snowflake’s built-in SYSTEM$ESTIMATE_QUERY_ACCELERATION function to systematically evaluate your historical queries and provide actionable insights.
The framework consists of three components:
- Results Table: Stores QAS estimates with query metadata for analysis
- Evaluation Procedure: Iterates through long-running queries and captures QAS estimates
- Analysis Queries: Provides recommendations based on collected data.
Implementation
Implementation
Step 1: Create the Results Table
First, create a table to store the QAS evaluation results:
CREATE TABLE IF NOT EXISTS ADMIN_DB.PUBLIC.QAS_ESTIMATE_RESULTS (
RUN_TS TIMESTAMP_NTZ,
WAREHOUSE_NAME STRING,
QUERY_ID STRING,
START_TIME TIMESTAMP_TZ,
USER_NAME STRING,
ROLE_NAME STRING,
QUERY_TYPE STRING,
EXECUTION_STATUS STRING,
ELAPSED_MIN NUMBER(18,2),
ESTIMATE_JSON VARIANT,
ERROR_MSG STRING
);
Step 2: Create the Evaluation Procedure
The stored procedure queries ACCOUNT_USAGE.QUERY_HISTORY to find long-running queries, then calls SYSTEM$ESTIMATE_QUERY_ACCELERATION for each query:
Now execute the procedure:
This procedure will:
- Looking query back to last 30 days
- Capture Query which are running more than 10 minutes into QAS_ESTIMATE_RESULTS table.
CALL ADMIN_DB.PUBLIC.RUN_QAS_ESTIMATES_FOR_LONG_RUNNERS_JS('COMPUTE_WH', 30, 10);
Once the procedure executed, QAS_ESTIMATE_RESULTS table got populated.
Understanding the QAS Estimate Output
The SYSTEM$ESTIMATE_QUERY_ACCELERATION function returns a JSON object with valuable information:
{
"status": "eligible",
"originalQueryTime": 320.103,
"upperLimitScaleFactor": 22,
"estimatedQueryTimes": {
"1": 175,
"2": 123,
"4": 89,
"8": 65,
"22": 48
},
"ineligibleReason": null
}
Now once we capture all the desired queries, next to identify which queries are QAS eligible i.e. “status”: “eligible”.
We have parsed the JSON value and extracted
- MAX_SCALE_FACTOR
- Time taken to complete query with scale factor 4
- Time taken to complete query with scale factor 8
SELECT
distinct QUERY_ID,
WAREHOUSE_NAME,
USER_NAME,
ELAPSED_MIN,
ESTIMATE_JSON:status::STRING AS QAS_STATUS,
ESTIMATE_JSON:originalQueryTime::NUMBER(10,2) AS ORIGINAL_TIME_MS,
ESTIMATE_JSON:upperLimitScaleFactor::NUMBER AS MAX_SCALE_FACTOR,
— Time savings at different scale factors
ESTIMATE_JSON:estimatedQueryTimes:"1"::NUMBER AS TIME_AT_SF_1,
ESTIMATE_JSON:estimatedQueryTimes:"2"::NUMBER AS TIME_AT_SF_2,
ESTIMATE_JSON:estimatedQueryTimes:"4"::NUMBER AS TIME_AT_SF_4,
ESTIMATE_JSON:estimatedQueryTimes:"8"::NUMBER AS TIME_AT_SF_8,
— Calculate percentage improvement at max scale factor
ROUND(
(1 - (ESTIMATE_JSON:estimatedQueryTimes:"8"::NUMBER /
ESTIMATE_JSON:originalQueryTime::NUMBER)) * 100,
2
) AS PCT_IMPROVEMENT_SF_8,
ESTIMATE_JSON:ineligibleReason::STRING AS INELIGIBLE_REASON
FROM ADMIN_DB.PUBLIC.QAS_ESTIMATE_RESULTS
WHERE ESTIMATE_JSON:status::STRING = 'eligible'
ORDER BY ELAPSED_MIN DESC;
Understanding the Improvement Formula
Understanding the Improvement Formula
To calculate the percentage improvement at any scale factor, we use:
| Improvement % = (1 – (Estimated Time / Original Time)) × 100 |
Step-by-Step Calculation Example
Given: Original Time = 320ms, Estimated Time at SF=4 = 89ms
1: Calculate ratio: 89 / 320 = 0.278 (query takes 27.8% of original time)
2: Subtract from 1: 1 – 0.278 = 0.722 (72.2% time saved)
3: Convert to percentage: 0.722 × 100 = 72.2% improvement in case of Scale factor 4.
With Same calculation we can see approx 80% improvement in case of Scale factor 8.
Scale Factor Recommendation Query
Use this query to get actionable recommendations for your warehouse:
WITH analysis AS (
SELECT DISTINCT
WAREHOUSE_NAME,
QUERY_ID,
ESTIMATE_JSON:originalQueryTime::NUMBER AS ORIG,
ESTIMATE_JSON:estimatedQueryTimes:"4"::NUMBER AS SF4,
ESTIMATE_JSON:estimatedQueryTimes:"8"::NUMBER AS SF8,
ROUND((1 - ESTIMATE_JSON:estimatedQueryTimes:"4"::NUMBER /
ESTIMATE_JSON:originalQueryTime::NUMBER) * 100, 0) AS PCT_AT_4,
ROUND((1 - ESTIMATE_JSON:estimatedQueryTimes:"8"::NUMBER /
ESTIMATE_JSON:originalQueryTime::NUMBER) * 100, 0) AS PCT_AT_8
FROM ADMIN_DB.PUBLIC.QAS_ESTIMATE_RESULTS
WHERE ESTIMATE_JSON:status::STRING = 'eligible'
)
SELECT
WAREHOUSE_NAME,
COUNT(*) AS ELIGIBLE_QUERIES,
ROUND(AVG(PCT_AT_4), 0) || '%' AS AVG_IMPROVEMENT_SF4,
ROUND(AVG(PCT_AT_8), 0) || '%' AS AVG_IMPROVEMENT_SF8,
ROUND(AVG(PCT_AT_8) - AVG(PCT_AT_4), 0) || '%' AS EXTRA_GAIN_4_TO_8,
CASE
WHEN AVG(PCT_AT_8) - AVG(PCT_AT_4) > 10 THEN 8
WHEN AVG(PCT_AT_4) >= 50 THEN 4
ELSE 4
END AS SUGGESTED_SCALE_FACTOR,
'ALTER WAREHOUSE ' || WAREHOUSE_NAME ||
' SET ENABLE_QUERY_ACCELERATION = TRUE QUERY_ACCELERATION_MAX_SCALE_FACTOR = ' ||
CASE
WHEN AVG(PCT_AT_8) - AVG(PCT_AT_4) > 10 THEN '8'
ELSE '4'
END || ';' AS ENABLE_QAS_SQL
FROM analysis
GROUP BY WAREHOUSE_NAME;
Decision Logic Explained
| Condition | Recommendation |
| Extra gain SF8 vs SF4 > 10% | Use Scale Factor = 8 |
| SF4 improvement >= 50% | Use Scale Factor = 4 |
| Otherwise | Default to Scale Factor = 4 |
Query Acceleration Service is a powerful feature, but enabling it without data-driven analysis is like driving blindfolded. This framework provides the visibility you need to make informed decisions about QAS configuration.
