Schema:: ACCOUNT_USAGE

AGGREGATE_QUERY_HISTORY View¶

This Account Usage view enables you to monitor and track execution of statements over time. It contains similar data to the QUERY_HISTORY view but is aggregated in one-minute intervals for repeated SQL statements. You can use this view to monitor your workload and analyze performance.

In addition to queries against hybrid tables, all queries that you execute in Snowflake are included in AGGREGATE_QUERY_HISTORY. However, AGGREGATE_QUERY_HISTORY is particularly useful for monitoring and analyzing Unistore workloads that execute a small number of distinct statements repeatedly at high throughput.

Columns¶

Column Name	Data Type	Description
CALLS	NUMBER	Number of times the statement (query + query plan) was executed in the aggregation interval.
INTERVAL_START_TIME	TIMESTAMP_LTZ	Start time of the window of measurement (in the local time zone).
INTERVAL_END_TIME	TIMESTAMP_LTZ	End time of the window of measurement (in the local time zone).
QUERY_PARAMETERIZED_HASH	TEXT	Unique ID to identify identical parameterized queries. See QUERY_PARAMETERIZED_HASH Column.
QUERY_TEXT	TEXT	Sample text of the SQL statement.
DATABASE_ID	NUMBER	Internal/system-generated identifier for the database that was in use.
DATABASE_NAME	TEXT	Database that was in use at the time of the query.
SCHEMA_ID	NUMBER	Internal/system-generated identifier for the schema that was in use.
SCHEMA_NAME	TEXT	Schema that was in use at the time of the query.
QUERY_TYPE	TEXT	DML, query, etc. If the query failed, then the query type may be UNKNOWN.
SESSION_ID	NUMBER	Session that executed the statement.
USER_NAME	TEXT	User who issued the query.
ROLE_NAME	TEXT	Role that was active in the session at the time of the query.
ROLE_TYPE	TEXT	Specifies whether an `APPLICATION`, `DATABASE_ROLE`, or `ROLE` that executed the query.
WAREHOUSE_ID	NUMBER	Internal/system-generated identifier for the warehouse that was used.
WAREHOUSE_NAME	TEXT	Warehouse that the query executed on, if any.
WAREHOUSE_SIZE	TEXT	Size of the warehouse when this statement executed.
WAREHOUSE_TYPE	TEXT	Type of the warehouse when this statement executed.
QUERY_TAG	TEXT	Query tag set for this statement through the QUERY_TAG session parameter.
IS_CLIENT_GENERATED_STATEMENT	BOOLEAN	Indicates whether the query was client-generated.
RELEASE_VERSION	TEXT	Release version in the format of `major_release.minor_release.patch_release`.
ERRORS	ARRAY	List of error codes and messages that occurred during the aggregation interval. Each error is in the format of `{"code": "code1", "message": "msg1", "count": 10}`.
TOTAL_ELAPSED_TIME	OBJECT	Elapsed time (in milliseconds).
BYTES_SCANNED	OBJECT	Number of bytes scanned by this statement.
PERCENTAGE_SCANNED_FROM_CACHE	OBJECT	The percentage of data scanned from the local disk cache. The value ranges from 0.0 to 1.0. Multiply by 100 to get a true percentage.
BYTES_WRITTEN	OBJECT	Number of bytes written (e.g. when loading into a table).
BYTES_WRITTEN_TO_RESULT	OBJECT	Number of bytes written to a result object. For example, `select * from . . .` would produce a set of results in tabular format representing each field in the selection. . . In general, the results object represents whatever is produced as a result of the query, and `BYTES_WRITTEN_TO_RESULT` represents the size of the returned result.
BYTES_READ_FROM_RESULT	OBJECT	Number of bytes read from a result object.
ROWS_PRODUCED	OBJECT	Number of rows produced by this statement.
ROWS_INSERTED	OBJECT	Number of rows inserted by the query.
ROWS_UPDATED	OBJECT	Number of rows updated by the query.
ROWS_DELETED	OBJECT	Number of rows deleted by the query.
ROWS_UNLOADED	OBJECT	Number of rows unloaded during data export.
BYTES_DELETED	OBJECT	Number of bytes deleted by the query.
PARTITIONS_SCANNED	OBJECT	Number of micro-partitions scanned.
PARTITIONS_TOTAL	OBJECT	Total micro-partitions of all tables included in this query.
BYTES_SPILLED_TO_LOCAL_STORAGE	OBJECT	Volume of data spilled to local disk.
BYTES_SPILLED_TO_REMOTE_STORAGE	OBJECT	Volume of data spilled to remote disk.
BYTES_SENT_OVER_THE_NETWORK	OBJECT	Volume of data sent over the network.
COMPILATION_TIME	OBJECT	Compilation time (in milliseconds).
EXECUTION_TIME	OBJECT	Execution time (in milliseconds).
QUEUED_PROVISIONING_TIME	OBJECT	Time (in milliseconds) spent in the warehouse queue, waiting for the warehouse compute resources to provision, due to warehouse creation, resume, or resize.
QUEUED_REPAIR_TIME	OBJECT	Time (in milliseconds) spent in the warehouse queue, waiting for compute resources in the warehouse to be repaired.
QUEUED_OVERLOAD_TIME	OBJECT	Time (in milliseconds) spent in the warehouse queue, due to the warehouse being overloaded by the current query workload.
TRANSACTION_BLOCKED_TIME	OBJECT	Time (in milliseconds) spent blocked by a concurrent DML.
OUTBOUND_DATA_TRANSFER_CLOUD	TEXT	Target cloud provider for statements that unload data to another region and/or cloud.
OUTBOUND_DATA_TRANSFER_REGION	TEXT	Target region for statements that unload data to another region and/or cloud.
OUTBOUND_DATA_TRANSFER_BYTES	OBJECT	Number of bytes transferred in statements that unload data to another region and/or cloud.
INBOUND_DATA_TRANSFER_CLOUD	TEXT	Source cloud provider for statements that load data from another region and/or cloud.
INBOUND_DATA_TRANSFER_REGION	TEXT	Source region for statements that load data from another region and/or cloud.
INBOUND_DATA_TRANSFER_BYTES	OBJECT	Number of bytes transferred in a replication operation from another account. The source account could be in the same region or a different region than the current account.
LIST_EXTERNAL_FILES_TIME	OBJECT	Time (in milliseconds) spent listing external files.
CREDITS_USED_CLOUD_SERVICES	OBJECT	Number of credits used for cloud services.
EXTERNAL_FUNCTION_TOTAL_INVOCATIONS	OBJECT	Aggregate number of times that this query called remote services. For important details, see the Usage Notes.
EXTERNAL_FUNCTION_TOTAL_SENT_ROWS	OBJECT	Total number of rows that this query sent in all calls to all remote services.
EXTERNAL_FUNCTION_TOTAL_RECEIVED_ROWS	OBJECT	Total number of rows that this query received from all calls to all remote services.
EXTERNAL_FUNCTION_TOTAL_SENT_BYTES	OBJECT	Total number of bytes that this query sent in all calls to all remote services.
EXTERNAL_FUNCTION_TOTAL_RECEIVED_BYTES	OBJECT	Total number of bytes that this query received from all calls to all remote services.
QUERY_LOAD_PERCENT	OBJECT	The approximate percentage of active compute resources in the warehouse for this query execution.
QUERY_ACCELERATION_BYTES_SCANNED	OBJECT	Number of bytes scanned by the query acceleration service.
QUERY_ACCELERATION_PARTITIONS_SCANNED	OBJECT	Number of partitions scanned by the query acceleration service.
QUERY_ACCELERATION_UPPER_LIMIT_SCALE_FACTOR	OBJECT	Upper limit scale factor that a query would have benefited from.
CHILD_QUERIES_WAIT_TIME	OBJECT	Time (in milliseconds) to complete the cached lookup when calling a memoizable function.
HYBRID_TABLE_REQUESTS_THROTTLED_COUNT	NUMBER	Number of hybrid table queries that were throttled.
OWNER_ROLE_TYPE	TEXT	The type of role that owns the object, either `ROLE` or `DATABASE_ROLE`. . If a Snowflake Native App owns the object, the value is `APPLICATION`. . Snowflake returns NULL if you delete the object because a deleted object does not have an owner role.

The OBJECT data type contains the following fields:

Field Name	Description
sum	Sum across all executions within the aggregation interval.
avg	Average across all executions within the aggregation interval.
stddev	Standard deviation across all executions within the aggregation interval.
min	Minimum across all executions within the aggregation interval.
median	Median across all executions within the aggregation interval.
p90	90th percentile across all executions within the aggregation interval.
p99	99th percentile across all executions within the aggregation interval.
p99.9	99.9th percentile across all executions within the aggregation interval.
max	Maximum across all executions within the aggregation interval.

Note

The following columns of the type OBJECT do not contain a sum field:

PERCENTAGE_SCANNED_FROM_CACHE
QUERY_LOAD_PERCENT
QUERY_ACCELERATION_UPPER_LIMIT_SCALE_FACTOR

QUERY_PARAMETERIZED_HASH Column¶

The QUERY_PARAMETERIZED_HASH column contains a hash value that is computed based on the parameterized query, which means the version of the query after parameterizing all literals.

For example, the following queries have the same QUERY_PARAMETERIZED_HASH value:

SELECT * FROM table1 WHERE table1.name = 'TIM'

Copy

SELECT * FROM table1 WHERE table1.name = 'AIHUA'

Copy

The QUERY_PARAMETERIZED_HASH value has the following restrictions:

The constant literal must be in the following binary functions on predicates: equal, not equal, greater (or equal) than, smaller (or equal) than.

The aliases must be the same.

As long as there are difference in the SQL text, the QUERY_HASH and QUERY_PARAMETERIZED_HASH values will be different, with the following exceptions:

Identifier/session variable/stage name are case insensitive.

White space differences are ignored.

Literals satisfying the binary predicate rule mentioned above.

Usage Notes¶

Latency for the view may be up to 180 minutes (3 hours).

Examples¶

You can use the AGGREGATE_QUERY_HISTORY view to monitor for potential problems with errors, queueing, lock blocking, or hybrid table throttling. You typically want these metrics to be consistently low. If you see a spike in any of these metrics, it may indicate a problem:

SET (START_DATE, END_DATE) = ('2023-11-01', '2023-11-08');

WITH time_issues AS
(
    SELECT
        interval_start_time
        , SUM(transaction_blocked_time:"sum") AS transaction_blocked_time
        , SUM(queued_provisioning_time:"sum") AS queued_provisioning_time
        , SUM(queued_repair_time:"sum") AS queued_repair_time
        , SUM(queued_overload_time:"sum") AS queued_overload_time
        , SUM(hybrid_table_requests_throttled_count) AS hybrid_table_requests_throttled_count
    FROM snowflake.account_usage.aggregate_query_history
    WHERE TRUE
        AND interval_start_time > $START_DATE
        AND interval_start_time < $END_DATE
    GROUP BY ALL
),
errors AS
(
    SELECT
        interval_start_time
        , SUM(value:"count") as error_count
    FROM
    (
        SELECT
            a.interval_start_time
            , e.*
        FROM
            snowflake.account_usage.aggregate_query_history a,
            TABLE(FLATTEN(input => errors)) e
        WHERE TRUE
            AND interval_start_time > $START_DATE
            AND interval_start_time < $END_DATE
    )
    GROUP BY ALL
)
SELECT
    time_issues.interval_start_time
    , error_count
    , transaction_blocked_time
    , queued_provisioning_time
    , queued_repair_time
    , queued_overload_time
    , hybrid_table_requests_throttled_count
FROM
    time_issues FULL JOIN errors ON errors.interval_start_time = time_issues.interval_start_time
;

Copy

You can query the view to monitor your overall workload throughput and concurrency. Many workloads have a regular cyclical pattern. Any unexpected spikes or drops may be worth investigating.

For example, monitor throughput and concurrency for warehouse my_warehouse in the first week of November:

SELECT
    interval_start_time
    , SUM(calls) AS execution_count
    , SUM(calls) / 60 AS queries_per_second
    , COUNT(DISTINCT session_id) AS unique_sessions
    , COUNT(user_name) AS unique_users
FROM snowflake.account_usage.aggregate_query_history
WHERE TRUE
    AND warehouse_name = 'MY_WAREHOUSE'
    AND interval_start_time > '2023-11-01'
    AND interval_start_time < '2023-11-08'
GROUP BY
    interval_start_time
;

Copy

The most common and heavily repeated queries can be a good place to focus any efforts to optimize or improve the efficiency of your workload. You can query the view to identify top queries for a workload by execution count.

For example, identify the top queries by execution count for warehouse my_warehouse:

SELECT
    query_parameterized_hash
    , ANY_VALUE(query_text)
    , SUM(calls) AS execution_count
FROM snowflake.account_usage.aggregate_query_history
WHERE TRUE
    AND warehouse_name = 'MY_WAREHOUSE'
    AND interval_start_time > '2023-11-01'
    AND interval_start_time < '2023-11-08'
GROUP BY
    query_parameterized_hash
ORDER BY execution_count DESC
;

Copy

To identify slowest queries by average total latency:

SELECT
    query_parameterized_hash
    , any_value(query_text)
    , SUM(total_elapsed_time:"sum"::NUMBER) / SUM (calls) as avg_latency
FROM snowflake.account_usage.aggregate_query_history
WHERE TRUE
    AND warehouse_name = 'MY_WAREHOUSE'
    AND interval_start_time > '2023-07-01'
    AND interval_start_time < '2023-07-08'
GROUP BY
    query_parameterized_hash
ORDER BY avg_latency DESC
;

Copy

To analyze performance over time for a specific query of interest:

SELECT
    interval_start_time
    , total_elapsed_time:"avg"::number avg_elapsed_time
    , total_elapsed_time:"min"::number min_elapsed_time
    , total_elapsed_time:"p90"::number p90_elapsed_time
    , total_elapsed_time:"p99"::number p99_elapsed_time
    , total_elapsed_time:"max"::number max_elapsed_time
FROM snowflake.account_usage.aggregate_query_history
WHERE TRUE
    AND query_parameterized_hash = '<123456>'
    AND interval_start_time > '2023-07-01'
    AND interval_start_time < '2023-07-08'
ORDER BY interval_start_time DESC
;

Copy