How to Monitor Query Performance in Snowflake Efficiently
To monitor query performance in Snowflake, use the
QUERY_HISTORY view to see execution details and the SYSTEM$QUERY_PROFILE function to analyze query steps visually. These tools help identify slow queries and optimize resource usage.Syntax
The main ways to monitor query performance in Snowflake are:
QUERY_HISTORY: A view that shows details about queries run in your account.SYSTEM$QUERY_PROFILE(query_id): A function that returns a detailed profile of a specific query's execution.
QUERY_HISTORY syntax example:
SELECT * FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) WHERE QUERY_TEXT ILIKE '%your_query%';
SYSTEM$QUERY_PROFILE syntax example:
SELECT SYSTEM$QUERY_PROFILE('query_id');Replace query_id with the actual query identifier.
sql
SELECT * FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) WHERE QUERY_TEXT ILIKE '%your_query%'; SELECT SYSTEM$QUERY_PROFILE('query_id');
Example
This example shows how to find recent queries containing the word 'sales' and then get the profile of one query to analyze its performance.
sql
SELECT QUERY_ID, QUERY_TEXT, EXECUTION_STATUS, TOTAL_ELAPSED_TIME FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) WHERE QUERY_TEXT ILIKE '%sales%' ORDER BY START_TIME DESC LIMIT 3; -- Suppose the first query_id returned is '01a2b3c4-5678-90de-f123-4567890abcde' SELECT SYSTEM$QUERY_PROFILE('01a2b3c4-5678-90de-f123-4567890abcde');
Output
QUERY_ID | QUERY_TEXT | EXECUTION_STATUS | TOTAL_ELAPSED_TIME
------------------------------------|--------------------------------|------------------|-------------------
01a2b3c4-5678-90de-f123-4567890abcde | SELECT * FROM sales WHERE ... | SUCCESS | 1200
...
-- The SYSTEM$QUERY_PROFILE returns a JSON object with detailed steps and timings of the query execution.
Common Pitfalls
- Not filtering
QUERY_HISTORYby time or user can return too many results, making it hard to find relevant queries. - Using
SYSTEM$QUERY_PROFILEwithout a validquery_idwill cause errors. - Ignoring the
TOTAL_ELAPSED_TIMEand other timing columns can miss slow queries. - Relying only on query text without checking execution status may include failed queries.
sql
/* Wrong: No filter, too many results */ SELECT * FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()); /* Right: Filter by recent time and user */ SELECT * FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) WHERE START_TIME > DATEADD(hour, -1, CURRENT_TIMESTAMP()) AND USER_NAME = CURRENT_USER();
Quick Reference
| Command | Purpose |
|---|---|
| QUERY_HISTORY | View recent queries with execution details |
| SYSTEM$QUERY_PROFILE('query_id') | Get detailed execution profile of a query |
| TOTAL_ELAPSED_TIME | Time taken by the query to run |
| EXECUTION_STATUS | Status of the query (e.g., SUCCESS, FAILED) |
| START_TIME filter | Limit queries to recent time frame for relevance |
Key Takeaways
Use QUERY_HISTORY to list and filter recent queries by text, time, and user.
Use SYSTEM$QUERY_PROFILE with a query ID to see detailed execution steps and timings.
Always filter QUERY_HISTORY to avoid overwhelming results and focus on relevant queries.
Check execution status and elapsed time to identify slow or failed queries.
Combine these tools to optimize query performance effectively in Snowflake.
