Using the Query Acceleration Service

The query acceleration service can accelerate parts of the query workload in a warehouse. When it is enabled for a warehouse, it can improve overall warehouse performance by reducing the impact of outlier queries, which are queries that use more resources than the typical query. The query acceleration service does this by offloading portions of the query processing work to shared compute resources that are provided by the service.

Examples of the types of workloads that might benefit from the query acceleration service include:

  • Ad hoc analytics.

  • Workloads with unpredictable data volume per query.

  • Queries with large scans and selective filters.

The query acceleration service can handle these types of workloads more efficiently by performing more work in parallel and reducing the wallclock time spent in scanning and filtering.

The query acceleration service is currently available in the following regions:

  • AWS - US West (Oregon)

  • AWS - US East (N. Virginia)

  • AZURE - East US 2 (Virginia)

Note

The query acceleration service does not guarantee predictability around performance improvements. In order to benefit queries equitably, the query acceleration service might reallocate resources dynamically at any time. Performance improvements can vary for the same query executed at different times.

In this Topic:

Identifying Queries and Warehouses That Might Benefit From Query Acceleration

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. The QUERY_ACCELERATION_ELIGIBLE view also identifies warehouses that might benefit from the query acceleration service.

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.

Example

Execute the following statement to help determine if query acceleration might benefit a specific query:

select parse_json(system$estimate_query_acceleration('8cd54bf0-1651-5b1c-ac9c-6a9582ebd20f'));

In this example, the query is eligible for the query acceleration service and includes estimated query times using the service:

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

The following example shows the results for a query that is not eligible for query acceleration service:

select parse_json(system$estimate_query_acceleration('cf23522b-3b91-cf14-9fe0-988a292a4bfa'));

The statement above produces the following output:

{
  "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.

Examples

Note

These examples assume the SNOWFLAKE database and the ACCOUNT_USAGE schema are in use for the current session. The examples also assume the ACCOUNTADMIN role (or a role granted IMPORTED PRIVILEGES on the database) is in use. If they are not in use, execute the following commands before running the queries in the examples:

USE ROLE ACCOUNTADMIN;

USE SCHEMA snowflake.account_usage;

Identify the queries that would most benefit from the service by the amount of query execution time that is eligible for acceleration:

SELECT query_id, eligible_query_acceleration_time
FROM QUERY_ACCELERATION_ELIGIBLE
ORDER BY eligible_query_acceleration_time DESC;

Identify the queries that would most benefit from the service in a specific warehouse mywh:

SELECT query_id, eligible_query_acceleration_time
FROM QUERY_ACCELERATION_ELIGIBLE
WHERE warehouse_name = 'mywh'
ORDER BY eligible_query_acceleration_time DESC;

Identify the warehouses in a given time period with the most queries eligible for the query acceleration service:

SELECT warehouse_name, COUNT(query_id) AS num_eligible_queries
FROM query_acceleration_eligible
WHERE start_time > 'Tue, 18 January 2022 12:00:00'::timestamp
AND end_time < 'Tue, 19 January 2022 00:00:00'::timestamp
GROUP BY warehouse_name
ORDER BY num_eligible_queries DESC;

Identify the warehouses that would benefit the most from the query acceleration service. For each warehouse, calculate the total query execution time eligible for acceleration:

SELECT warehouse_name, SUM(eligible_query_acceleration_time) AS total_eligible_time
FROM QUERY_ACCELERATION_ELIGIBLE
GROUP BY warehouse_name
ORDER BY total_eligible_time DESC;

Identify the upper limit scale factor for the query acceleration service for a given warehouse:

SELECT MAX(upper_limit_scale_factor)
FROM QUERY_ACCELERATION_ELIGIBLE
WHERE warehouse_name = 'mywh';

Enabling Query Acceleration

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

Note

  • The query acceleration service does not accelerate queries on tables that have search optimization enabled.

  • The query acceleration service does not accelerate queries that can be read from the warehouse cache.

Examples

The following example creates a warehouse named my_wh that has the query acceleration service enabled with a maximum scale factor of 2:

create warehouse my_wh with
  ENABLE_QUERY_ACCELERATION = true
  QUERY_ACCELERATION_MAX_SCALE_FACTOR = 2;

The following example enables the query acceleration service for a warehouse named my_wh with a maximum scale factor of 8:

alter warehouse my_wh set
  ENABLE_QUERY_ACCELERATION = true
  QUERY_ACCELERATION_MAX_SCALE_FACTOR = 8;

Execute the SHOW WAREHOUSES command to display details about the my_wh warehouse. Verify that the query acceleration service is enabled and that the maximum scale factor for the query acceleration service is 8.

show warehouses like 'my_wh';

+---------+-----------+----------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+-------------------------------+-------------------------------+-------------------------------+--------------+---------+---------------------------+-------------------------------------+------------------+---------+----------+--------+-----------+-----------+----------------+
| name    | state     | type     | size    | min_cluster_count | max_cluster_count | started_clusters | 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      | scaling_policy |
|---------+-----------+----------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+-------------------------------+-------------------------------+-------------------------------+--------------+---------+---------------------------+-------------------------------------+------------------+---------+----------+--------+-----------+-----------+----------------|
| MY_WH   | STARTED   | STANDARD | X-Small |                 1 |                 1 |                0 |       0 |      0 | N          | N          |          600 | true        |           |              |           |       | 2022-02-17 22:41:03.119 +0000 | 2022-05-13 14:10:57.994 +0000 | 2022-05-13 14:10:57.994 +0000 | ACCOUNTADMIN |         | true                      |                                   8 | null             |       0 |        0 |      0 |         1 | 158403873 | STANDARD       |
+---------+-----------+----------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+-------------------------------+-------------------------------+-------------------------------+--------------+---------+---------------------------+-------------------------------------+------------------+---------+----------+--------+-----------+-----------+----------------+

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 may 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.

Monitoring Query Acceleration Service Usage

Using the Web Interface to Monitor Query Acceleration Usage

Once you enable the query acceleration service, you can view the Profile Overview panel in Query Profile 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.

../_images/query-acceleration-profile-overview.png

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 Profile Overview / Operator Details for more information), click on the operator to see detailed information. The following screenshot displays an example of the statistics displayed for a TableScan operation:

../_images/query-acceleration-table-scan.png

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 * 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 * 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 for the Query Acceleration Service

Query Acceleration is billed like other serverless features in Snowflake in that you pay by the second for the resources used.

Feature

Snowflake Credits per Compute-Hour

Snowflake-managed compute

Cloud Services

Query Acceleration

1

1

Viewing Billing Information in the Classic Web Interface

If you have Query Acceleration enabled for your account, the billing page in the web interface 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:

../_images/query-acceleration-billing-ui.png

Viewing Billing Using the QUERY_ACCELERATION_HISTORY Function

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

Example

The following examples uses the QUERY_ACCELERATION_HISTORY function to return information about the queries accelerated by this service within the past 12 hours:

select * from
table(information_schema.query_acceleration_history(
  date_range_start=>dateadd(H, -12, current_timestamp)));
Back to top