Snowflake QUERY_HISTORY for Performance Analysis: INFORMATION_SCHEMA vs ACCOUNT_USAGE

Snowflake's QUERY_HISTORY exposes detailed execution statistics for every query, making it essential for performance analysis, bottleneck identification, and cost optimization. There are two ways to retrieve it — the INFORMATION_SCHEMA table function and the ACCOUNT_USAGE view — and they differ in latency, retention, and permission model.

Two Data Sources

QUERY_HISTORY has two retrieval points. The exam asks about the differences precisely, so make sure the comparison below is locked in.

Using INFORMATION_SCHEMA.QUERY_HISTORY()

Call it as a table function and filter by time range or user with named parameters.

-- Query history from the last hour
SELECT *
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
  DATE_RANGE_START => DATEADD('HOUR', -1, CURRENT_TIMESTAMP()),
  DATE_RANGE_END => CURRENT_TIMESTAMP(),
  RESULT_LIMIT => 100
))
ORDER BY START_TIME DESC;

-- Query history for a specific user
SELECT
  QUERY_ID,
  QUERY_TEXT,
  TOTAL_ELAPSED_TIME,
  BYTES_SCANNED,
  ROWS_PRODUCED,
  WAREHOUSE_NAME
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY_BY_USER(
  USER_NAME => 'ANALYST_USER',
  RESULT_LIMIT => 50
))
ORDER BY TOTAL_ELAPSED_TIME DESC;

-- Query history for a specific warehouse
SELECT *
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY_BY_WAREHOUSE(
  WAREHOUSE_NAME => 'ANALYTICS_WH',
  RESULT_LIMIT => 50
));

Using ACCOUNT_USAGE.QUERY_HISTORY

Use the ACCOUNT_USAGE view for long-range analysis or an account-wide overview. Remember the up-to-45-minute delay and reach for INFORMATION_SCHEMA when you need real-time monitoring.

-- 30-day query summary
SELECT
  USER_NAME,
  WAREHOUSE_NAME,
  COUNT(*) AS query_count,
  AVG(TOTAL_ELAPSED_TIME) / 1000 AS avg_elapsed_sec,
  SUM(BYTES_SCANNED) / POWER(1024, 3) AS total_scanned_tb,
  AVG(PARTITIONS_SCANNED / NULLIF(PARTITIONS_TOTAL, 0)) AS avg_scan_ratio
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME >= DATEADD('DAY', -30, CURRENT_TIMESTAMP())
  AND EXECUTION_STATUS = 'SUCCESS'
GROUP BY USER_NAME, WAREHOUSE_NAME
ORDER BY total_scanned_tb DESC;

Bottleneck Analysis SQL

Three angles to triage performance bottlenecks:

1. Find the longest-running queries

-- Top 20 longest-running queries in the last 7 days
SELECT
  QUERY_ID,
  SUBSTR(QUERY_TEXT, 1, 200) AS query_preview,
  USER_NAME,
  WAREHOUSE_NAME,
  WAREHOUSE_SIZE,
  TOTAL_ELAPSED_TIME / 1000 AS elapsed_sec,
  COMPILATION_TIME / 1000 AS compile_sec,
  EXECUTION_TIME / 1000 AS exec_sec,
  QUEUED_OVERLOAD_TIME / 1000 AS queue_sec,
  BYTES_SCANNED / POWER(1024, 3) AS scanned_tb
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME >= DATEADD('DAY', -7, CURRENT_TIMESTAMP())
  AND EXECUTION_STATUS = 'SUCCESS'
  AND QUERY_TYPE = 'SELECT'
ORDER BY TOTAL_ELAPSED_TIME DESC
LIMIT 20;

2. Queries with poor pruning efficiency

-- Queries with pruning under 50% (scanning more than 50% of partitions)
SELECT
  QUERY_ID,
  SUBSTR(QUERY_TEXT, 1, 200) AS query_preview,
  PARTITIONS_SCANNED,
  PARTITIONS_TOTAL,
  ROUND(PARTITIONS_SCANNED / NULLIF(PARTITIONS_TOTAL, 0) * 100, 1) AS scan_pct,
  BYTES_SCANNED / POWER(1024, 2) AS scanned_mb
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME >= DATEADD('DAY', -7, CURRENT_TIMESTAMP())
  AND PARTITIONS_TOTAL > 100
  AND PARTITIONS_SCANNED / NULLIF(PARTITIONS_TOTAL, 0) > 0.5
  AND EXECUTION_STATUS = 'SUCCESS'
ORDER BY scan_pct DESC
LIMIT 20;

3. Queries stuck in the queue

-- Queries with more than 5 seconds in the queue (a sign of warehouse shortage)
SELECT
  QUERY_ID,
  WAREHOUSE_NAME,
  QUEUED_OVERLOAD_TIME / 1000 AS queue_sec,
  QUEUED_PROVISIONING_TIME / 1000 AS provisioning_sec,
  TOTAL_ELAPSED_TIME / 1000 AS elapsed_sec,
  START_TIME
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME >= DATEADD('DAY', -7, CURRENT_TIMESTAMP())
  AND QUEUED_OVERLOAD_TIME > 5000
ORDER BY QUEUED_OVERLOAD_TIME DESC
LIMIT 20;

Key Columns for Performance Analysis

What the Exam Tests

• INFORMATION_SCHEMA = 7 days / real-time / 10,000-row cap. ACCOUNT_USAGE = 365 days / up to 45-minute delay / no row limit.
• ACCOUNT_USAGE requires IMPORTED PRIVILEGES on the SNOWFLAKE database.
• Large QUEUED_OVERLOAD_TIME → warehouse resource shortage (consider multi-cluster or sizing up).
• BYTES_SPILLED_TO_REMOTE_STORAGE > 0 → warehouse size needs to increase.
• Know when to use QUERY_HISTORY_BY_USER vs. QUERY_HISTORY_BY_WAREHOUSE.

Check with a Sample Question

Frequently Asked Questions