Realtime Query Monitor

About

Purpose of this app is to prevent runaway queries from consuming unexpected credits in your system. Once you set up the app first time, you will receive an email alert if there are queries running longer than the threshold you defined. You can then decide which actions to take, either leave the query running or kill the query if it is indeed problematic one.

If you like to receive EMAIL Alert, Remember to follow instruction below to set up email After Setting up the App.

Set Up

In Main Page, Click on "Turn On" Button. After that, set up you email by following instructions below. You don't need to open the App again. Monitor runs by itself in the background at the frequency you specified in the Configuration Section. If a long running query is detected, you will receive email alerts.

Cost to Run the App

Warehouse credit will be consumed when the App start monitoring your snowflake just like when runing any queries. The amount of credits consumed depends on how frequent you like the monitor to run.By default, the App wakes up every 10 mins to check if any long running queries exceeding the threshold you defined in the Main page, daily credit consumption will be 2.4 credits/day. If you change the configuration to run more frequently (say, every 1 min), then credit consumption will be increased to 24 credits/day. Please adjust frequency accordingly.

Tips: Please use the Xsmall warehouse and set warehouse auto suspend to 1 min to minimize the cost. This app is lightweight, each monitor run will last only a few seconds.
Tips: To minimize the cost of running this app, you can share the same warehouse with other workloads you may already have.

Support

If you run into problems or would like more advanced features, feel free to contact us at contact@ava-watch.com, subject line: monitor support. Or Visit us at: Ava-Watch.com

Setup Email Alert

Currently, Native App does NOT support Email Notification. Follow below steps to set it up outside Native App. You should be able to receive email alert like this once set up:

#1 In your own database/schema, create stream

CREATE OR REPLACE STREAM ALERT_LOG_STREAM  ON TABLE AVAWATCH_MONITOR.MONITOR.ALERT_LOG  APPEND_ONLY = TRUE;
					

#2 Create your email integration if not already exist. (Replace with your email address)

CREATE OR REPLACE NOTIFICATION INTEGRATION my_email_int
					    TYPE=EMAIL
					    ENABLED=TRUE
					    ALLOWED_RECIPIENTS=('name@youremail.com');
					

#3. Create the stored procedure to send email.

create or replace procedure email_alert(email string,email_int string)
					        returns String
					        language python
					        runtime_version = 3.8
					        packages =('snowflake-snowpark-python')
					        handler = 'main'
					        execute as caller
					        as $$

					import snowflake.snowpark as snowpark
					from snowflake.snowpark.functions import col,row_number
					from snowflake.snowpark.window import Window

					def main(session: snowpark.Session,email: str,email_int:str): 

					        df_alert=session.table('ALERT_LOG_STREAM')
					        if df_alert.count()>0:
					            df_alert=df_alert.withColumn("row_number",row_number().over(Window.partitionBy(col('query_id')).orderBy(col('current_ts').desc()))) \
					            .filter(col('row_number')==1)  

					            message_body=""
					            for row in df_alert.collect():
					                message_body=message_body+"query_id: "+row["QUERY_ID"] + " by "+row["USER_NAME"]+" on warehouse " + row["WAREHOUSE_NAME"]+ " has been running in the last " + str(row["RUN_TIME_IN_SECONDS"]) + " seconds\n\n"

					            email_query=f"""CALL SYSTEM$SEND_EMAIL(
					            '{email_int}',
					            '{email}',
					            'Email Alert: Long Running Queries Found.',
					            '{message_body}'
					            );  
					            """
					            try:
					                session.sql(email_query).collect()
					                session.sql("create temporary table temp as select * from ALERT_LOG_STREAM where 0=1").collect()
					            except Exception as err:
					                return err
					        else:
					            return "no records found"
					        return "success"$$;
					

#4. Create task to call email stored procedure if long queries are detected. Replace your warehouse name, email address and integration name if different

CREATE OR REPLACE TASK my_email_task
					WAREHOUSE = 'yourwarehouse'
					SCHEDULE = '1 MINUTES'
					WHEN system$stream_has_data('alert_log_stream')
					AS
					CALL email_alert('name1@youremail.com','my_email_int');

					alter task my_email_task resume; 
					

Snowflake Reference Link

Heartbeat

Since monitor runs in the background, you may wonder if it still running at your desired interval. Heartbeat table is provided for this purpose. A record will be inserted into this table regardless if a long query found or not. You can inquery heartbeat as below:

select * from AVAWATCH_MONITOR.MONITOR.HEARTBEAT order by current_ts desc;
					

If you don't see the most recent heartbeat data as you expected, try to turn off and turn back on the monitor from the Main page. if problem persists, Contact us at contact@ava-watch.com, subject line: monitor support

Data Clean Up

It is always good practice to clean up data no longer needed. This App automatically cleans up data periodically. It keeps 7 days' data in heartbeat table and 60 days in alertlog table. You can adjust it in the Main page.