top of page

Resource Monitoring Using Snowsight

Updated: Mar 15

Author: Ramkrishna Ojha & Vinay Pratap Singh


Prerequisites:

Having an active account with Snowflake.


Introduction:

In Snowsight, for resource monitoring, we created a Dashboard in which different tiles monitor the total credit usage, credit usage per warehouse, storage usage, User login, and also Query execution in the Snowflake account.


1) Sign in to Snowflake Snowsight interface:



2) Go to dashboards and create a new Dashboard:



3) Inside the newly created Dashboard, create a Tile.



4) In this project (Cinemalytics), we created eight different tiles :


1. Total Credit Used: This tile tells about the total credit used by the account.


select

sum(Credits_used),

warehouse_name

from

"SNOWFLAKE"."ACCOUNT_USAGE"."WAREHOUSE_METERING_HISTORY"

group by

Warehouse_name



2. Credit Used Per Warehouse: This tile tells about the total credit used by a warehouse for different warehouse's no. of credits used by them.


select

sum(Credits_used),

warehouse_name

from

"SNOWFLAKE"."ACCOUNT_USAGE"."WAREHOUSE_METERING_HISTORY"

group by

Warehouse_name



3. Credit Used Per week: This tile tells about the total credit used per week.


select

date_trunc('WEEK', usage_date) as USAGE_WEEK,

sum(CREDITS_BILLED)

from

account_usage.metering_daily_history

group by

USAGE_WEEK;



4. User Login: This tile tells about how many logins there are like by the Python Driver, Snowflake UI, JDBC, and ODBC Connector.


select

*

from

table(information_schema.login_history_by_user())

order by

Event_timestamp;



5. Total Storage Used: This tile tells about the total storage used by the account.


select

div0( sum (AVERAGE_DATABASE_BYTES) , 1048576)

from

(

select

*

from

table(

information_schema.database_storage_usage_history(

dateadd('days', -30, current_date()),

current_date()

)

)

)



6. Storage Used(Byte): This tile tells about the total storage used by the different databases in Bytes.


select

*

from

table(

information_schema.database_storage_usage_history(

dateadd('days', -10, current_date()),

current_date()

)

);



7. Query Execution time per user: This tile tells about the time taken per user for query execution in descending order.


select

user_name,

(avg(execution_time)) / 1000 as average_execution_time

from

account_usage.query_history

where

start_time = :daterange

group by

1

order by

2 desc;



8. No. of Jobs Executed: This tile tells about the total number of jobs executed in the account.


select

count(*) as number_of_jobs

from

query_history

where

start_time >= date_trunc(month, current_date);



Conclusion:

For monitoring the credit usage in your account, Logins, query execution time, and Jobs in your Snowflake account, we use the Snowsight feature of Snowflake to create a dashboard by combining the different tiles.


Final Dashboard:


307 views0 comments

Recent Posts

See All
bottom of page