Query history and performance analysis are essential components of Snowflake's monitoring capabilities that help organizations optimize both performance and costs. The Query History feature provides detailed insights into all SQL statements executed within your Snowflake account, accessible through…Query history and performance analysis are essential components of Snowflake's monitoring capabilities that help organizations optimize both performance and costs. The Query History feature provides detailed insights into all SQL statements executed within your Snowflake account, accessible through the Snowflake web interface or programmatically via the QUERY_HISTORY table function and ACCOUNT_USAGE schema.
The Query History captures critical metadata including query ID, SQL text, execution status, start and end times, total elapsed time, bytes scanned, rows produced, warehouse name, and user information. This data is retained for 14 days in the Information Schema and up to 365 days in Account Usage views, enabling both real-time monitoring and historical trend analysis.
Performance analysis involves examining several key metrics. Execution time breakdown shows time spent in compilation, queuing, and execution phases. Understanding these phases helps identify bottlenecks - long queue times suggest warehouse sizing issues, while extended execution times may indicate query optimization opportunities. Bytes scanned and partition pruning efficiency reveal how effectively queries leverage clustering and filtering.
The Query Profile provides visual execution plans showing operators, their relationships, and resource consumption. Analysts can identify expensive operations like large table scans, inefficient joins, or data spillage to local or remote storage. Spillage occurs when operations exceed memory capacity, significantly impacting performance.
Best practices for leveraging query history include regularly reviewing long-running queries, identifying frequently executed queries for optimization, monitoring warehouse utilization patterns, and tracking credit consumption trends. Setting up Resource Monitors helps control costs by alerting or suspending warehouses when credit thresholds are reached.
By systematically analyzing query history, organizations can right-size virtual warehouses, implement appropriate clustering keys, optimize query patterns, and ultimately achieve better performance while managing Snowflake consumption costs effectively.
Query History and Performance Analysis in Snowflake
Why Query History and Performance Analysis Matters
Understanding query history and performance analysis is crucial for Snowflake professionals because it enables you to identify bottlenecks, optimize resource usage, reduce costs, and improve overall system efficiency. For the SnowPro Core exam, this topic tests your ability to monitor, troubleshoot, and optimize Snowflake workloads effectively.
What is Query History?
Query History in Snowflake is a comprehensive record of all queries executed within your account. It captures detailed information about each query including:
- Query ID: Unique identifier for each query - SQL Text: The actual query statement - User: Who executed the query - Warehouse: Which virtual warehouse processed the query - Execution Time: Total time taken to complete - Bytes Scanned: Amount of data processed - Status: Success, failure, or other states
How to Access Query History
There are multiple ways to access query history in Snowflake:
1. Snowsight UI: Navigate to Activity > Query History for a visual interface 2. QUERY_HISTORY Table Function: Use INFORMATION_SCHEMA.QUERY_HISTORY() for programmatic access 3. ACCOUNT_USAGE.QUERY_HISTORY View: Access historical data up to 365 days with a 45-minute latency
Key Performance Metrics
When analyzing query performance, focus on these critical metrics:
- Total Elapsed Time: Overall query duration - Execution Time: Time spent actually running the query - Queuing Time: Time waiting for warehouse resources - Compilation Time: Time spent parsing and optimizing - Bytes Scanned vs Bytes Sent: Indicates pruning efficiency - Partitions Scanned vs Total Partitions: Shows micro-partition pruning effectiveness
Query Profile
The Query Profile provides a visual representation of query execution. It shows:
- Operator tree: How Snowflake processes the query - Statistics per operator: Time and resources used at each step - Spillage indicators: When queries spill to local or remote storage - Pruning statistics: Effectiveness of partition elimination
Common Performance Issues to Identify
1. Queuing: Queries waiting due to insufficient warehouse capacity 2. Spillage: Data spilling to disk due to insufficient memory 3. Poor Pruning: Too many partitions being scanned 4. Exploding Joins: Cartesian products or inefficient join conditions 5. Full Table Scans: Missing filters or clustering
Data Retention for Query History
- INFORMATION_SCHEMA: 7 days of history, no latency - ACCOUNT_USAGE: 365 days of history, 45-minute latency
Exam Tips: Answering Questions on Query History and Performance Analysis
1. Know the difference between INFORMATION_SCHEMA and ACCOUNT_USAGE: Exam questions often test retention periods and latency differences. Remember 7 days versus 365 days.
2. Understand Query Profile components: Be familiar with what each section of the Query Profile reveals about execution patterns.
3. Recognize spillage indicators: Questions may ask what causes spillage and how to resolve it. The solution typically involves using a larger warehouse.
4. Remember pruning metrics: Partitions scanned versus total partitions indicates clustering and filter effectiveness.
5. Identify warehouse sizing issues: High queuing times suggest the need for larger warehouses or multi-cluster configurations.
6. Know the required privileges: ACCOUNTADMIN or specific monitoring roles are needed for certain query history views.
7. Focus on practical scenarios: Expect questions asking which tool or view to use for specific troubleshooting scenarios.
8. Understand time components: Be able to distinguish between compilation, queuing, and execution time and what each indicates about performance issues.