Snowflake queries to troubleshoot queries.

[David_Fergen]

Description:

The following are a few queries that are typically used to troubleshoot query patterns, warehouse usage, and credit consumption.

  

Solution:

Queries for the troubleshooting:

1) Query History:

  

USE ROLE ACCOUNTADMIN; 

 

SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY WHERE UPPER(QUERY_TEXT) LIKE '%ACDOCA%'; 

  

Note:  Make sure to filter on fields like ROWS_PRODUCED,ROWS_INSERTED,ROWS_UPDATED,ROWS_DELETED to detect query patterns 

  

Here is a complete guide on how to use the query_history view - https://docs.snowflake.com/en/sql-reference/account-usage/query_history.html 

  

Note: The queries below would not be helpful right away, but are definitely worth having in the back pocket -  

  

2) Warehouse Load History: You can look at load and consumption patterns across warehouses using the query below.  

  

Select START_TIME, 

END_TIME, 

WAREHOUSE_ID, 

WAREHOUSE_NAME, 

AVG_RUNNING, 

AVG_QUEUED_LOAD, 

AVG_QUEUED_PROVISIONING, 

AVG_BLOCKED 

from snowflake.account_usage.warehouse_load_history; 

  

  

Although the result for this query looks rather simple, in a visualization tool you can see overlaps in warehouse usage, combined peaks/valleys for usage. A sum of AVG_RUNNING is a good metric for combined usage. 

  

3) Warehouse Metering History:

  

SELECT 

START_TIME, 

END_TIME, 

WAREHOUSE_ID, 

WAREHOUSE_NAME, 

CREDITS_USED, 

CREDITS_USED_COMPUTE, 

CREDITS_USED_CLOUD_SERVICES 

from snowflake.account_usage.warehouse_metering_history; 

  

  

This is a similar view to the one above but is focused more on credit usage, so you could assess overall credit usage across warehouses. 

  

4) Query History 

  

SELECT USER_NAME, 

ROLE_NAME, 

WAREHOUSE_NAME, 

month(START_TIME) as MONTH, // you can change the granularity per your need 

count(QUERY_ID) 

from snowflake.account_usage.query_history GROUP BY 1,2,3,4; 

  

This query will give you an idea of the #queries run by users on different warehouses over a particular period (say a month, week, or year) 

  

  

  

5) Snowflake Usage dashboard in Qlik:

https://community.qlik.com/t5/Technology-Partners-Ecosystem-Documents/Qlik-amp-Snowflake-Usage-Dashb...