Bird
Raised Fist0
SnowflakeHow-ToBeginner · 4 min read

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_HISTORY by time or user can return too many results, making it hard to find relevant queries.
  • Using SYSTEM$QUERY_PROFILE without a valid query_id will cause errors.
  • Ignoring the TOTAL_ELAPSED_TIME and 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

CommandPurpose
QUERY_HISTORYView recent queries with execution details
SYSTEM$QUERY_PROFILE('query_id')Get detailed execution profile of a query
TOTAL_ELAPSED_TIMETime taken by the query to run
EXECUTION_STATUSStatus of the query (e.g., SUCCESS, FAILED)
START_TIME filterLimit 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.