Snowflake provides tools and resources that can help you diagnose the cause of a slow query:
- History Page
- QUERY_HISTORY Functions
- Query Profile
History Page (Web Interface)
If the Status column displays a status of Queued for the query, the cause of the slowness is most likely due to insufficient compute resources. If the running query load is high or there’s queuing, consider starting a separate warehouse and moving queued queries to that warehouse. Alternatively, you could change your multi-cluster settings to add additional clusters to handle higher concurrency going forward.
The Monitoring Warehouse Load topic in the documentation provides additional information on monitoring your warehouse load.
If the Status column displays a status of Blocked for the query, the query is likely blocked by a locked resource. You can execute SHOW LOCKS and SHOW TRANSACTIONS to figure out what other transaction are holding the locks etc. For more information on resource locking, see this topic.
Alternatively, use the QUERY_HISTORY functions to view similar query history information.
select * from table(information_schema.query_history())
order by start_time;
Query Profile (Web Interface)
Query Profile provides execution details for a query. For the selected query, it provides a graphical representation of the main components of the processing plan for the query, with statistics for each component, along with details and statistics for the overall query.
Below is an example of the query plan for the following query:
select sum(v) from fact join dim using (k) where d < 100 and v > ( select avg(v) from fact );
If you need help with diagnosing a specific query, please reach out to Snowflake Support. We can help you tune your queries and analyze your resource usage, and provide recommendations.