Snowflake monitoring queries
This document describes some of the useful queries which helps in monitoring Snowflake account. There are two monitoring schemas in a Snowflake account which has the functions and views to monitor the Snowflake account
information_schema
Which provides a set of system defined views and metadata with real time statistics about queries. This data is typically retained for up to 7/14 days and is most useful to monitor ongoing queries.
account_usage
Provides a rich set of historical metadata which is held for up to a year and will be the primary source of usage analysis over time.
check current running queries
select * from table(information_schema.query_history()) where execution_status in ('RESUMING_WAREHOUSE','RUNNING','QUEUED','BLOCKED');
queries running by a user
select * from table(information_schema.QUERY_HISTORY_BY_USER(USER_NAME => '<usr name>')) where execution_status in ('RESUMING_WAREHOUSE','RUNNING','QUEUED','BLOCKED');
Time consuming queries
Find top 10 time consuming queries
--- Queries taking more than 10 sec
SET long_running_threshold_seconds = 10;
SELECT * FROM TABLE(information_schema.query_history()) WHERE (execution_status = 'RUNNING' AND datediff('seconds',start_time,current_timestamp()) > $long_running_threshold_seconds);
--- To get the history , list top 10 queries
use schema snowflake.account_usage;
select QUERY_TEXT,DATABASE_NAME,SCHEMA_NAME,sum(TOTAL_ELAPSED_TIME)/1000 as Execution_time_sec
from query_history
group by QUERY_TEXT,DATABASE_NAME,SCHEMA_NAME
order by sum(TOTAL_ELAPSED_TIME) desc
limit 10;
Queries consuming more credits for cloud services
use schema snowflake.account_usage;
select QUERY_TEXT,DATABASE_NAME,SCHEMA_NAME,USER_NAME,sum(CREDITS_USED_CLOUD_SERVICES) as credits_used
from query_history
group by QUERY_TEXT,DATABASE_NAME,SCHEMA_NAME,USER_NAME
order by sum(CREDITS_USED_CLOUD_SERVICES) desc
limit 10;
Queries doing more full partition scans
use schema snowflake.account_usage;
select QUERY_TEXT,DATABASE_NAME,SCHEMA_NAME,USER_NAME,PARTITIONS_SCANNED,PARTITIONS_TOTAL
from query_history
where PARTITIONS_SCANNED=PARTITIONS_TOTAL
and PARTITIONS_TOTAL > 0
order by PARTITIONS_TOTAL desc;
Lock Monitoring in Snowflake
select * from table(information_schema.query_history()) where execution_status in ('BLOCKED');
To see more information about the blocked sessions execute “show locks in account” command. It will show the sessions who are blocking each other with status as “HOLDING” and “WAITING”.
“show transactions in account” command will show the current running transactions account wise with status.
To cancel any of the transaction (either blocking and blocked)
select system$abort_transaction(<transaction id>);
select system$abort_transaction(1594638696404);
Check query execution plan
If you find there is a long running query and you want to know the execution plan for the query, you can get that in two ways
- using explain command
- using query profile
EXPLAIN Command
The explain command will give the logical plan of a query. EXPLAIN compiles the SQL statement, but does not execute it, so EXPLAIN does not require a running warehouse. Although EXPLAIN does not consume any compute credits, the compilation of the query does consume Cloud Service credits, just as other metadata operations do.
use schema snowflake_sample_data.tpch_sf1;
--- Check the query execution plan in text format
explain using text select
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice * (1-l_discount)) as sum_disc_price,
sum(l_extendedprice * (1-l_discount) * (1+l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count(*) as count_order
from
lineitem
where
l_shipdate <= dateadd(day, -90, to_date('1998-12-01'))
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus;
GlobalStats:
partitionsTotal=10
partitionsAssigned=10
bytesAssigned=165278208
Operations:
1:0 ->Result LINEITEM.L_RETURNFLAG, LINEITEM.L_LINESTATUS, SUM(LINEITEM.L_QUANTITY), SUM(LINEITEM.L_EXTENDEDPRICE), SUM(LINEITEM.L_EXTENDEDPRICE * (1 - LINEITEM.L_DISCOUNT)), SUM((LINEITEM.L_EXTENDEDPRICE * (1 - LINEITEM.L_DISCOUNT)) * (1 + LINEITEM.L_TAX)), (SUM(LINEITEM.L_QUANTITY)) / (COUNT(LINEITEM.L_QUANTITY)), (SUM(LINEITEM.L_EXTENDEDPRICE)) / (COUNT(LINEITEM.L_EXTENDEDPRICE)), (SUM(LINEITEM.L_DISCOUNT)) / (COUNT(LINEITEM.L_DISCOUNT)), COUNT(*)
1:1 ->Sort LINEITEM.L_RETURNFLAG ASC NULLS LAST, LINEITEM.L_LINESTATUS ASC NULLS LAST
1:2 ->Aggregate aggExprs: [SUM(LINEITEM.L_QUANTITY), SUM(LINEITEM.L_EXTENDEDPRICE), SUM(LINEITEM.L_EXTENDEDPRICE * (1 - LINEITEM.L_DISCOUNT)), SUM((LINEITEM.L_EXTENDEDPRICE * (1 - LINEITEM.L_DISCOUNT)) * (1 + LINEITEM.L_TAX)), COUNT(LINEITEM.L_QUANTITY), COUNT(LINEITEM.L_EXTENDEDPRICE), SUM(LINEITEM.L_DISCOUNT), COUNT(LINEITEM.L_DISCOUNT), COUNT(*)], groupKeys: [LINEITEM.L_RETURNFLAG, LINEITEM.L_LINESTATUS]
1:3 ->Filter LINEITEM.L_SHIPDATE <= '1998-09-02'
1:4 ->TableScan SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.LINEITEM L_QUANTITY, L_EXTENDEDPRICE, L_DISCOUNT, L_TAX, L_RETURNFLAG, L_LINESTATUS, L_SHIPDATE {partitionsTotal=10, partitionsAssigned=10, bytesAssigned=165278208}
NOTE: The EXPLAIN plan is the “logical” explain plan. It shows the operations that will be performed, and their logical relationship to each other. The actual execution order of the operations in the plan does not necessarily match the logical order shown by the plan.
Query profile
Query Profile, available through the Snowflake web interface, provides execution details for a query.
Go to history => Click on Query ID => This will show the query details and execution plan along with time.
Get query plan using query_id
You can get the query id from query history view or history from GUI
--- To get the plan in text format
select SYSTEM$EXPLAIN_JSON_TO_TEXT(system$explain_plan_json('01958e99-0187-c08e-0000-00214103b041')) as explain_plan;
GlobalStats:
bytesAssigned=0
partitionsAssigned=0
partitionsTotal=0
Operations:
1:0 ->Result TEST_123.ID
1:1 ->TableScan LOCK_TEST.PUBLIC.TEST_123 ID {partitionsTotal=0, partitionsAssigned=0, bytesAssigned=0}
--- To get the plan in json format
select system$explain_plan_json('01958e99-0187-c08e-0000-00214103b041') as explain_plan;{"GlobalStats":{"partitionsTotal":0,"partitionsAssigned":0,"bytesAssigned":0},"Operations":[[{"id":0,"operation":"Result","expressions":["TEST_123.ID"]},{"id":1,"parent":0,"operation":"TableScan","objects":["LOCK_TEST.PUBLIC.TEST_123"],"expressions":["ID"],"partitionsAssigned":0,"partitionsTotal":0,"bytesAssigned":0}]]}
Login history
Snowflake maintain the user login history for 1 year and it also contain the failed login attempts by the users. you have two methods to read the login history
- login_history function in information_schema
- login_history in account_usage schema
information_schema maintain the data for last 7 days
--- login history for last 24 hours
select * from table(information_schema.login_history(dateadd('hours',-24,current_timestamp()),current_timestamp())) order by event_timestamp;
--- login history of a user in last 24 hours
select * from table(information_schema.login_history_by_user('<USERNAME>',dateadd('hours',-1,current_timestamp()),current_timestamp())) order by event_timestamp;
If the requirement to get the login history is beyond 7 days use the snowflake.account_usage.login_history view. There is a delay of 2 hrs to get the data into this view.
select * from snowflake.account_usage.login_history order by event_timestamp;
NOTE: Query_id in query_history is unique for each run. Same query can have different query_id’s.
Viswamitra's blog 