Using the Query Acceleration Service¶

Eligible queries¶

In general, queries are eligible because they have a portion of the query plan that can be run in parallel using QAS compute resources. These queries fall into one of two patterns:

• Large scans with an aggregation or selective filter.

• Large scans that insert many new rows.

Snowflake doesn’t have a specific cutoff for what constitutes a “large enough” scan to be eligible. The threshold for eligibility depends on a variety of factors, including the query plan and warehouse size. Snowflake only marks a query as eligible if there is high confidence that the query would be accelerated if QAS was enabled.

Common reasons that queries are ineligible¶

Some queries are ineligible for query acceleration. The following are common reasons why a query cannot be accelerated:

• There are not enough partitions in the scan. If there are not enough partitions to scan, the benefits of query acceleration are offset by the latency in acquiring resources for the query acceleration service.

• Even if a query has a filter, the filters may not be selective enough. Alternatively, if the query has an aggregation with GROUP BY, the cardinality of the GROUP BY expression might be too high for eligibility.

• The query includes a LIMIT clause but does not have an ORDER BY clause.

• The query includes functions that return nondeterministic results (for example, SEQ or RANDOM).

Identifying queries with the SYSTEM$ESTIMATE_QUERY_ACCELERATION function¶

The SYSTEM$ESTIMATE_QUERY_ACCELERATION function can help determine if a previously executed query might benefit from the query acceleration service. If the query is eligible for query acceleration, the function returns the estimated query execution time for different query acceleration scale factors.

SELECT PARSE_JSON(SYSTEM$ESTIMATE_QUERY_ACCELERATION('8cd54bf0-1651-5b1c-ac9c-6a9582ebd20f'));

{
  "estimatedQueryTimes": {
    "1": 171,
    "10": 115,
    "2": 152,
    "4": 133,
    "8": 120
  },
  "originalQueryTime": 300.291,
  "queryUUID": "8cd54bf0-1651-5b1c-ac9c-6a9582ebd20f",
  "status": "eligible",
  "upperLimitScaleFactor": 10
}

SELECT PARSE_JSON(SYSTEM$ESTIMATE_QUERY_ACCELERATION('cf23522b-3b91-cf14-9fe0-988a292a4bfa'));

{
  "estimatedQueryTimes": {},
  "originalQueryTime": 20.291,
  "queryUUID": "cf23522b-3b91-cf14-9fe0-988a292a4bfa",
  "status": "ineligible",
  "upperLimitScaleFactor": 0
}

Identifying queries and warehouses with the QUERY_ACCELERATION_ELIGIBLE view¶

Query the QUERY_ACCELERATION_ELIGIBLE view to identify the queries and warehouses that might benefit the most from the query acceleration service. For each query, the view includes the amount of query execution time that is eligible for the query acceleration service.

USE ROLE ACCOUNTADMIN;

SELECT query_id, eligible_query_acceleration_time
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_ELIGIBLE
  WHERE start_time > DATEADD('day', -7, CURRENT_TIMESTAMP())
  ORDER BY eligible_query_acceleration_time DESC;

SELECT query_id, eligible_query_acceleration_time
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_ELIGIBLE
  WHERE warehouse_name = 'MYWH'
  AND start_time > DATEADD('day', -7, CURRENT_TIMESTAMP())
  ORDER BY eligible_query_acceleration_time DESC;

SELECT warehouse_name, COUNT(query_id) AS num_eligible_queries
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_ELIGIBLE
  WHERE start_time > DATEADD('day', -7, CURRENT_TIMESTAMP())
  GROUP BY warehouse_name
  ORDER BY num_eligible_queries DESC;

SELECT warehouse_name, SUM(eligible_query_acceleration_time) AS total_eligible_time
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_ELIGIBLE
  WHERE start_time > DATEADD('day', -7, CURRENT_TIMESTAMP())
  GROUP BY warehouse_name
  ORDER BY total_eligible_time DESC;

SELECT MAX(upper_limit_scale_factor)
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_ELIGIBLE
  WHERE warehouse_name = 'MYWH'
  AND start_time > DATEADD('day', -7, CURRENT_TIMESTAMP());

SELECT upper_limit_scale_factor, COUNT(upper_limit_scale_factor)
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_ELIGIBLE
  WHERE warehouse_name = 'MYWH'
  AND start_time > DATEADD('day', -7, CURRENT_TIMESTAMP())
  GROUP BY 1 ORDER BY 1;

Examples¶

The following example enables the query acceleration service for a warehouse named my_wh:

CREATE WAREHOUSE my_wh WITH
  ENABLE_QUERY_ACCELERATION = true;

Copy

Execute the SHOW WAREHOUSES command to display details about the my_wh warehouse.

SHOW WAREHOUSES LIKE 'my_wh';

+---------+---------+----------+---------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+-------------------------------+-------------------------------+-------------------------------+--------------+---------+---------------------------+-------------------------------------+------------------+---------+----------+--------+-----------+------------+
| name    | state   | type     | size    | running | queued | is_default | is_current | auto_suspend | auto_resume | available | provisioning | quiescing | other | created_on                    | resumed_on                    | updated_on                    | owner        | comment | enable_query_acceleration | query_acceleration_max_scale_factor | resource_monitor | actives | pendings | failed | suspended | uuid       |
|---------+---------+----------+---------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+-------------------------------+-------------------------------+-------------------------------+--------------+---------+---------------------------+-------------------------------------+------------------+---------+----------+--------+-----------+------------|
| MY_WH   | SUSPENDED | STANDARD | Medium |       0 |      0 | N          | N          |          600 | true        |           |              |           |       | 2023-01-20 14:31:49.283 -0800 | 2023-01-20 14:31:49.388 -0800 | 2023-01-20 16:34:28.583 -0800 | ACCOUNTADMIN |         | true                      |                                   8 | null             |       0 |        0 |      0 |         4 | 1132659053 |
+---------+---------+----------+---------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+-------------------------------+-------------------------------+-------------------------------+--------------+---------+---------------------------+-------------------------------------+------------------+---------+----------+--------+-----------+------------+

Copy

The query acceleration service may increase the credit consumption rate of a warehouse. The maximum scale factor can help limit the consumption rate. See CREATE WAREHOUSE or ALTER WAREHOUSE for more details about the QUERY_ACCELERATION_MAX_SCALE_FACTOR property.

The QUERY_ACCELERATION_ELIGIBLE view and the SYSTEM$ESTIMATE_QUERY_ACCELERATION function might be useful in determining an appropriate scale factor for a warehouse. See Identifying queries and warehouses that might benefit from query acceleration (in this topic) for details.

Example¶

The following example modifies the warehouse named my_wh to enable the query acceleration service with a maximum scale factor of 0.

ALTER WAREHOUSE my_wh SET
  ENABLE_QUERY_ACCELERATION = true
  QUERY_ACCELERATION_MAX_SCALE_FACTOR = 0;

Copy

Using the web interface to monitor query acceleration usage¶

Once you enable the query acceleration service, you can view the Profile Overview panel in the Query Profile tab to see the effects of the query acceleration results.

The following screenshot displays an example of the statistics displayed for the query overall. If multiple operations in a query were accelerated, the results are aggregated in this view so you can see the total amount of work done by the query acceleration service.

The Query Acceleration section of the Profile Overview panel includes the following statistics:

• Partitions scanned by service — number of files offloaded for scanning to the query acceleration service.

• Scans selected for acceleration — number of table scans being accelerated.

In the operator details (see Statistics), click on the operator to see detailed information. The following screenshot displays an example of the statistics displayed for a TableScan operation:

The Query Acceleration section of the TableScan details panel includes the following statistics:

• Partitions scanned by service — number of files offloaded for scanning to the query acceleration service.

Using the Account Usage QUERY_HISTORY view to monitor query acceleration usage¶

To see the effects of query acceleration on a query, you can use the following columns in the QUERY_HISTORY view.

• QUERY_ACCELERATION_BYTES_SCANNED

• QUERY_ACCELERATION_PARTITIONS_SCANNED

• QUERY_ACCELERATION_UPPER_LIMIT_SCALE_FACTOR

You can use these columns to identify the queries that benefited from the query acceleration service. For each query, you can also determine the total number of partitions and bytes scanned by the query acceleration service.

For descriptions of each of these columns, see QUERY_HISTORY view.

For example, to find the queries with the most bytes scanned by the query acceleration service in the past 24 hours:

SELECT query_id,
       query_text,
       warehouse_name,
       start_time,
       end_time,
       query_acceleration_bytes_scanned,
       query_acceleration_partitions_scanned,
       query_acceleration_upper_limit_scale_factor
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
  WHERE query_acceleration_partitions_scanned > 0 
  AND start_time >= DATEADD(hour, -24, CURRENT_TIMESTAMP())
  ORDER BY query_acceleration_bytes_scanned DESC;

To find the queries with the largest number of partitions scanned by the query acceleration service in the past 24 hours:

SELECT query_id,
       query_text,
       warehouse_name,
       start_time,
       end_time,
       query_acceleration_bytes_scanned,
       query_acceleration_partitions_scanned,
       query_acceleration_upper_limit_scale_factor
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
  WHERE query_acceleration_partitions_scanned > 0 
  AND start_time >= DATEADD(hour, -24, CURRENT_TIMESTAMP())
  ORDER BY query_acceleration_partitions_scanned DESC;

Viewing billing information in the Classic Console¶

If you have Query Acceleration enabled for your account, the billing page in the Classic Console includes a warehouse called QUERY_ACCELERATION that shows all credits used by the service across all warehouses in your account.

The screenshot below shows an example of the billing information displayed for the QUERY_ACCELERATION warehouse:

Viewing billing using the Account Usage QUERY_ACCELERATION_HISTORY view¶

You can view billing data in the Account Usage QUERY_ACCELERATION_HISTORY view.

SELECT warehouse_name,
       SUM(credits_used) AS total_credits_used
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_HISTORY
  WHERE start_time >= DATE_TRUNC(month, CURRENT_DATE)
  GROUP BY 1
  ORDER BY 2 DESC;

Viewing billing using the Organization Usage QUERY_ACCELERATION_HISTORY view¶

You can view billing data for the query acceleration service for all accounts in your organization in the Organization Usage QUERY_ACCELERATION_HISTORY view.

SELECT account_name,
       warehouse_name,
       SUM(credits_used) AS total_credits_used
  FROM SNOWFLAKE.ORGANIZATION_USAGE.QUERY_ACCELERATION_HISTORY
  WHERE usage_date >= DATE_TRUNC(month, CURRENT_DATE)
  GROUP BY 1, 2
  ORDER BY 3 DESC;

Viewing billing using the QUERY_ACCELERATION_HISTORY function¶

You can also view billing data using the Information Schema QUERY_ACCELERATION_HISTORY function.

Viewing warehouse and query acceleration service costs¶

The following query computes the costs of the warehouse and the query acceleration service for a specific warehouse. You can execute this query after enabling the query acceleration service for a warehouse to compare costs before and after enabling query acceleration. The date range for the query begins 8 weeks prior to the first credit usage for the query acceleration service to 8 weeks after the last incurred cost for query acceleration service (or up to the current date).

This example query returns the warehouse and query acceleration service costs for my_warehouse:

WITH credits AS (
  SELECT 'QC' AS credit_type,
         TO_DATE(end_time) AS credit_date,
         SUM(credits_used) AS num_credits
    FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_HISTORY
    WHERE warehouse_name = 'my_warehouse'
    AND credit_date BETWEEN
           DATEADD(WEEK, -8, (
             SELECT TO_DATE(MIN(end_time))
               FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_HISTORY
               WHERE warehouse_name = 'my_warehouse'
           ))
           AND
           DATEADD(WEEK, +8, (
             SELECT TO_DATE(MAX(end_time))
               FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_HISTORY
               WHERE warehouse_name = 'my_warehouse'
           ))
  GROUP BY credit_date
  UNION ALL
  SELECT 'WC' AS credit_type,
         TO_DATE(end_time) AS credit_date,
         SUM(credits_used) AS num_credits
    FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
    WHERE warehouse_name = 'my_warehouse'
    AND credit_date BETWEEN
           DATEADD(WEEK, -8, (
             SELECT TO_DATE(MIN(end_time))
               FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_HISTORY
               WHERE warehouse_name = 'my_warehouse'
           ))
           AND
           DATEADD(WEEK, +8, (
             SELECT TO_DATE(MAX(end_time))
               FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_HISTORY
               WHERE warehouse_name = 'my_warehouse'
           ))
  GROUP BY credit_date
)
SELECT credit_date,
       SUM(IFF(credit_type = 'QC', num_credits, 0)) AS qas_credits,
       SUM(IFF(credit_type = 'WC', num_credits, 0)) AS compute_credits,
       compute_credits + qas_credits AS total_credits,
       AVG(total_credits) OVER (
         PARTITION BY NULL ORDER BY credit_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
         AS avg_total_credits_7days
  FROM credits
  GROUP BY credit_date
  ORDER BY credit_date;

Viewing query performance¶

This query returns the average execution time for query acceleration eligible queries for a given warehouse. The date range for the query begins 8 weeks prior to the first credit usage for the query acceleration service to 8 weeks after the last incurred cost for query acceleration service (or up to the current date). The results might help you evaluate how the average query performance changed after enabling the query acceleration service.

This example query returns the query execution time by day and computes the 7 day average for the week prior for queries that are eligible for acceleration in the warehouse my_warehouse:

WITH qas_eligible_or_accelerated AS (
  SELECT TO_DATE(qh.end_time) AS exec_date,
        COUNT(*) AS num_execs,
        SUM(qh.execution_time) AS exec_time,
        MAX(IFF(qh.query_acceleration_bytes_scanned > 0, 1, NULL)) AS qas_accel_flag
    FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY AS qh
    WHERE qh.warehouse_name = 'my_warehouse'
    AND TO_DATE(qh.end_time) BETWEEN
           DATEADD(WEEK, -8, (
             SELECT TO_DATE(MIN(end_time))
               FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_HISTORY
              WHERE warehouse_name = 'my_warehouse'
           ))
           AND
           DATEADD(WEEK, +8, (
             SELECT TO_DATE(MAX(end_time))
               FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_HISTORY
              WHERE warehouse_name = 'my_warehouse'
           ))
    AND (qh.query_acceleration_bytes_scanned > 0
          OR
          EXISTS (
            SELECT 1
              FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_ELIGIBLE AS qae
               WHERE qae.query_id = qh.query_id
               AND qae.warehouse_name = qh.warehouse_name
          )
         )
    GROUP BY exec_date
)
SELECT exec_date,
       SUM(exec_time) OVER (
         PARTITION BY NULL ORDER BY exec_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) /
       NULLIFZERO(SUM(num_execs) OVER (
         PARTITION BY NULL ORDER BY exec_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
       ) AS avg_exec_time_7days,
      exec_time / NULLIFZERO(num_execs) AS avg_exec_time,
      qas_accel_flag,
      num_execs,
      exec_time
  FROM qas_eligible_or_accelerated;

The output from the statement includes the following columns: