Query history and profiling in Snowflake - Commands & Configuration

Introduction

When you run queries in Snowflake, you may want to see what queries ran before and how long they took. Query history and profiling help you find slow queries and understand their details to improve performance.

When you want to check which queries used the most time or resources in your Snowflake account.

When you need to find errors or failed queries to fix issues quickly.

When you want to see who ran a query and when for auditing purposes.

When you want to analyze query performance to optimize your database usage.

When you want to track query trends over time to plan capacity.

Commands

This command fetches the last 5 queries run in the past day with details like who ran them, when, how long they took, and if they had errors. It helps you quickly see recent query activity.

Terminal

SELECT query_id, user_name, start_time, end_time, total_elapsed_time, query_text, error_message FROM snowflake.account_usage.query_history WHERE start_time > DATEADD(day, -1, CURRENT_TIMESTAMP()) ORDER BY start_time DESC LIMIT 5;

Expected Output

QUERY_ID                             USER_NAME   START_TIME                  END_TIME                    TOTAL_ELAPSED_TIME   QUERY_TEXT                             ERROR_MESSAGE

01a2b3c4-d5e6-789f-0123-456789abcdef   JOHN_DOE   2024-06-01 10:15:00.000   2024-06-01 10:15:05.000   5000                SELECT * FROM sales WHERE region = 'US'   

02b3c4d5-e6f7-890a-1234-56789abcdef0   JANE_SMITH 2024-06-01 09:50:00.000   2024-06-01 09:50:02.000   2000                UPDATE customers SET status = 'active' WHERE last_login > '2024-01-01'   

03c4d5e6-f7a8-901b-2345-6789abcdef01   JOHN_DOE   2024-06-01 09:30:00.000   2024-06-01 09:30:10.000   10000               SELECT COUNT(*) FROM orders   

04d5e6f7-a8b9-012c-3456-789abcdef012   ALICE_W   2024-06-01 09:00:00.000   2024-06-01 09:00:03.000   3000                DELETE FROM temp_data WHERE created_at < '2024-01-01'   

05e6f7a8-b9c0-123d-4567-89abcdef0123   BOB_K     2024-06-01 08:45:00.000   2024-06-01 08:45:01.500   1500                SELECT * FROM products WHERE price > 100   

This command shows detailed timing for a specific query by its ID, breaking down total time into compilation and execution. It helps you understand where time is spent.

Terminal

SELECT query_id, query_text, total_elapsed_time, compilation_time, execution_time FROM snowflake.account_usage.query_history WHERE query_id = '01a2b3c4-d5e6-789f-0123-456789abcdef';

Expected Output

QUERY_ID                             QUERY_TEXT                             TOTAL_ELAPSED_TIME   COMPILATION_TIME   EXECUTION_TIME

01a2b3c4-d5e6-789f-0123-456789abcdef   SELECT * FROM sales WHERE region = 'US'   5000                1000               4000

This command retrieves the query profile for the given query ID. It shows detailed steps and resource usage during query execution to help diagnose performance issues.

Terminal

SELECT * FROM table(information_schema.query_profile('01a2b3c4-d5e6-789f-0123-456789abcdef'));

Expected Output

OPERATION          START_TIME           END_TIME             ROWS_PRODUCED   BYTES_PRODUCED

Scan               2024-06-01 10:15:00 2024-06-01 10:15:02  10000           800000

Filter             2024-06-01 10:15:02 2024-06-01 10:15:04  5000            400000

Result             2024-06-01 10:15:04 2024-06-01 10:15:05  5000            400000

Key Concept

If you remember nothing else from this pattern, remember: query history shows what ran and when, and query profiling shows how the query used resources and time.

Common Mistakes

Trying to query query_history without specifying a recent time range.

The query_history table can be very large and slow to query without filters, causing timeouts or delays.

Always filter query_history by a recent time window like the last day or week.

Using query_profile without a valid query_id or for queries older than retention period.

Query profiles are only available for recent queries and require a correct query ID, otherwise the command returns no data.

Use a valid recent query_id from query_history to get the profile.

Summary

Use SELECT on snowflake.account_usage.query_history to see recent queries and their details.

Filter query_history by time to get fast and relevant results.

Use information_schema.query_profile with a query ID to see detailed execution steps and timings.