Using Snowflake Query Profiler for Monitoring
1️⃣ What is the Query Profiler?
The Query Profiler is a powerful tool in Snowflake’s Web UI that provides a visual representation of query execution.
It breaks down a query into stages and shows execution metrics (e.g., time, rows processed, data spillage).
🔹 Key Features
- ✅ Graphical execution plan: Visualizes how the query was processed.
- ✅ Detailed metrics: Time spent per stage, rows processed, partition info.
- ✅ Insights for optimization: Pinpoints expensive steps (e.g., joins, scans).
2️⃣ Why is it required?
- ✅ To identify bottlenecks in queries (e.g., joins, filters, table scans).
- ✅ To understand execution strategies used by Snowflake’s optimizer.
- ✅ To optimize and rewrite queries for better performance.
- ✅ To monitor query health and proactively troubleshoot slow performance.
3️⃣ Accessing the Query Profiler
The Query Profiler is available in the History tab of the Snowflake Web UI:
1️⃣ Go to the History page.
2️⃣ Click on a query’s Query ID.
3️⃣ Open the Query Profile tab.
🔹 Associated SQL
There’s no direct SQL command to generate a Query Profiler; it’s UI-based.
However, you can fetch Query IDs from QUERY_HISTORY to locate slow queries:
SELECT QUERY_ID, USER_NAME, EXECUTION_STATUS, TOTAL_ELAPSED_TIME
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME > DATEADD('DAY', -1, CURRENT_TIMESTAMP())
ORDER BY TOTAL_ELAPSED_TIME DESC;
Use the Query ID from this output to locate and open the profiler in the Web UI.
4️⃣ Use Case
A data engineer notices a dashboard query is running slowly. They open the Query Profiler to identify:
- If a full table scan is occurring.
- If a join is causing excessive data shuffling.
- If intermediate results are spilling to local storage (common cause of slowness).
Using this insight, they rewrite the query to use better joins, filters, or clustering.
5️⃣ Best Practices
- ✅ Regularly profile expensive queries to spot optimization opportunities.
- ✅ Use partitioning or clustering to avoid full scans.
- ✅ Ensure joins use indexed/clustered columns to reduce data shuffle.
- ✅ Combine profiler insights with Query History data for trends.
6️⃣ Limitations
❌ Only available in the Web UI (not exposed via SQL or API). ❌ Does not directly show table-level metadata (like clustering keys). ❌ Requires manual analysis—no automatic suggestions.
7️⃣ Advantages
- ✅ Intuitive visual insights—easy to spot issues.
- ✅ Helps fine-tune queries to reduce compute costs.
- ✅ Complements other monitoring tools (like Resource Monitors).
8️⃣ Disadvantages
❌ Not suitable for bulk analysis across many queries—one at a time. ❌ May not show low-level execution details (e.g., hardware-level tuning).
9️⃣ Required Roles
- MONITOR privileges or higher.
- Typically used by data engineers or DBAs.
🔟 Impact on Performance Tuning
- ✅ Helps identify stages causing delays (e.g., large joins, disk spillage).
- ✅ Supports right-sizing of warehouses by showing resource usage.
- ✅ Informs indexing/clustering strategies to improve performance.
- ✅ Promotes better query design (e.g., avoid cartesian joins).
- ✅ Facilitates data pruning by visualizing scans vs. pruned partitions.
🚀 Summary Table
| Aspect | Details |
|---|---|
| 📌 Tool | Query Profiler (UI) |
| 📌 Role | MONITOR, ACCOUNTADMIN, or DBAs |
| 📌 Purpose | Visualize and analyze query execution to tune performance |
| 📌 Limitation | Only in UI; requires manual step-by-step review |
| 📌 Benefit | Pinpoints costly query stages, helping you reduce costs & improve speed |