-
Notifications
You must be signed in to change notification settings - Fork 1
/
0020. Resource monitor.sql
87 lines (78 loc) · 8.18 KB
/
0020. Resource monitor.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
-- creating resource monitor
use role accountadmin;
use warehouse compute_wh;
use database snowflake;
use schema account_usage;
Select * from warehouse_metering_history; -- this has some latency as it is from account_usage schema
-- +-------------------------------+-------------------------------+--------------+---------------------+--------------+----------------------+-----------------------------+
-- | START_TIME | END_TIME | WAREHOUSE_ID | WAREHOUSE_NAME | CREDITS_USED | CREDITS_USED_COMPUTE | CREDITS_USED_CLOUD_SERVICES |
-- |-------------------------------+-------------------------------+--------------+---------------------+--------------+----------------------+-----------------------------|
-- | 2023-07-05 16:00:00.000 -0700 | 2023-07-05 17:00:00.000 -0700 | 1 | COMPUTE_WH | 0.573346111 | 0.567500000 | 0.005846111 |
-- | 2023-07-05 21:00:00.000 -0700 | 2023-07-05 22:00:00.000 -0700 | 1 | COMPUTE_WH | 0.509592778 | 0.508611111 | 0.000981667 |
-- | 2023-07-05 17:00:00.000 -0700 | 2023-07-05 18:00:00.000 -0700 | 1 | COMPUTE_WH | 0.440836944 | 0.439722222 | 0.001114722 |
-- | 2023-07-05 19:00:00.000 -0700 | 2023-07-05 20:00:00.000 -0700 | 1 | COMPUTE_WH | 0.270516667 | 0.269166667 | 0.001350000 |
-- | 2023-07-05 16:00:00.000 -0700 | 2023-07-05 17:00:00.000 -0700 | 0 | CLOUD_SERVICES_ONLY | 0.000183056 | 0.000000000 | 0.000183056 |
-- | 2023-07-05 21:00:00.000 -0700 | 2023-07-05 22:00:00.000 -0700 | 0 | CLOUD_SERVICES_ONLY | 0.000479167 | 0.000000000 | 0.000479167 |
-- | 2023-07-05 02:00:00.000 -0700 | 2023-07-05 03:00:00.000 -0700 | 1 | COMPUTE_WH | 0.167650833 | 0.167500000 | 0.000150833 |
-- | 2023-07-05 02:00:00.000 -0700 | 2023-07-05 03:00:00.000 -0700 | 0 | CLOUD_SERVICES_ONLY | 0.000098889 | 0.000000000 | 0.000098889 |
-- | 2023-07-05 19:00:00.000 -0700 | 2023-07-05 20:00:00.000 -0700 | 0 | CLOUD_SERVICES_ONLY | 0.000400556 | 0.000000000 | 0.000400556 |
-- | 2023-07-05 17:00:00.000 -0700 | 2023-07-05 18:00:00.000 -0700 | 0 | CLOUD_SERVICES_ONLY | 0.000045556 | 0.000000000 | 0.000045556 |
-- | 2023-07-05 20:00:00.000 -0700 | 2023-07-05 21:00:00.000 -0700 | 1 | COMPUTE_WH | 0.334340555 | 0.333333333 | 0.001007222 |
-- | 2023-07-05 18:00:00.000 -0700 | 2023-07-05 19:00:00.000 -0700 | 1 | COMPUTE_WH | 0.354158889 | 0.353055556 | 0.001103333 |
-- | 2023-07-06 16:00:00.000 -0700 | 2023-07-06 17:00:00.000 -0700 | 1 | COMPUTE_WH | 0.000103611 | 0.000000000 | 0.000103611 |
-- | 2023-07-05 20:00:00.000 -0700 | 2023-07-05 21:00:00.000 -0700 | 0 | CLOUD_SERVICES_ONLY | 0.000203056 | 0.000000000 | 0.000203056 |
-- +-------------------------------+-------------------------------+--------------+---------------------+--------------+----------------------+-----------------------------+
-- 14 Row(s) produced. Time Elapsed: 0.567s
-- Total credits grouped by warehouse;
SELECT WAREHOUSE_NAME,
SUM(CREDITS_USED) AS TOTAL_CREDITS_USED
FROM WAREHOUSE_METERING_HISTORY
WHERE START_TIME >= DATE_TRUNC(MONTH, CURRENT_DATE)
GROUP BY 1
ORDER BY 2 DESC;
-- +---------------------+--------------------+
-- | WAREHOUSE_NAME | TOTAL_CREDITS_USED |
-- |---------------------+--------------------|
-- | COMPUTE_WH | 2.650546388 |
-- | CLOUD_SERVICES_ONLY | 0.001410280 |
-- +---------------------+--------------------+
-- Warehouse metering history using the Information Schema; this has no latency as it is coming from information_schema
SELECT *
FROM TABLE(INFORMATION_SCHEMA.WAREHOUSE_METERING_HISTORY(dateadd('days',-7,current_date())));
-- +-------------------------------+-------------------------------+-------------------------+--------------+----------------------+-----------------------------+
-- | START_TIME | END_TIME | WAREHOUSE_NAME | CREDITS_USED | CREDITS_USED_COMPUTE | CREDITS_USED_CLOUD_SERVICES |
-- |-------------------------------+-------------------------------+-------------------------+--------------+----------------------+-----------------------------|
-- | 2023-07-06 17:00:00.000 -0700 | 2023-07-06 18:00:00.000 -0700 | DATA_ANALYSIS_WAREHOUSE | 0.068487222 | 0.067777778 | 0.000709444 |
-- | 2023-07-05 02:00:00.000 -0700 | 2023-07-05 03:00:00.000 -0700 | COMPUTE_WH | 0.167650833 | 0.167500000 | 0.000150833 |
-- | 2023-07-05 16:00:00.000 -0700 | 2023-07-05 17:00:00.000 -0700 | COMPUTE_WH | 0.573346111 | 0.567500000 | 0.005846111 |
-- | 2023-07-05 17:00:00.000 -0700 | 2023-07-05 18:00:00.000 -0700 | COMPUTE_WH | 0.440836944 | 0.439722222 | 0.001114722 |
-- | 2023-07-05 18:00:00.000 -0700 | 2023-07-05 19:00:00.000 -0700 | COMPUTE_WH | 0.354158889 | 0.353055556 | 0.001103333 |
-- | 2023-07-05 19:00:00.000 -0700 | 2023-07-05 20:00:00.000 -0700 | COMPUTE_WH | 0.270516667 | 0.269166667 | 0.001350000 |
-- | 2023-07-05 20:00:00.000 -0700 | 2023-07-05 21:00:00.000 -0700 | COMPUTE_WH | 0.334340555 | 0.333333333 | 0.001007222 |
-- | 2023-07-05 21:00:00.000 -0700 | 2023-07-05 22:00:00.000 -0700 | COMPUTE_WH | 0.509592778 | 0.508611111 | 0.000981667 |
-- | 2023-07-06 16:00:00.000 -0700 | 2023-07-06 17:00:00.000 -0700 | COMPUTE_WH | 0.000103611 | 0.000000000 | 0.000103611 |
-- | 2023-07-06 17:00:00.000 -0700 | 2023-07-06 18:00:00.000 -0700 | COMPUTE_WH | 0.167104166 | 0.166944444 | 0.000159722 |
-- | 2023-07-05 02:00:00.000 -0700 | 2023-07-05 03:00:00.000 -0700 | CLOUD_SERVICES_ONLY | 0.000098889 | 0.000000000 | 0.000098889 |
-- | 2023-07-05 16:00:00.000 -0700 | 2023-07-05 17:00:00.000 -0700 | CLOUD_SERVICES_ONLY | 0.000183056 | 0.000000000 | 0.000183056 |
-- | 2023-07-05 17:00:00.000 -0700 | 2023-07-05 18:00:00.000 -0700 | CLOUD_SERVICES_ONLY | 0.000045556 | 0.000000000 | 0.000045556 |
-- | 2023-07-05 19:00:00.000 -0700 | 2023-07-05 20:00:00.000 -0700 | CLOUD_SERVICES_ONLY | 0.000400556 | 0.000000000 | 0.000400556 |
-- | 2023-07-05 20:00:00.000 -0700 | 2023-07-05 21:00:00.000 -0700 | CLOUD_SERVICES_ONLY | 0.000203056 | 0.000000000 | 0.000203056 |
-- | 2023-07-05 21:00:00.000 -0700 | 2023-07-05 22:00:00.000 -0700 | CLOUD_SERVICES_ONLY | 0.000479167 | 0.000000000 | 0.000479167 |
-- | 2023-07-06 17:00:00.000 -0700 | 2023-07-06 18:00:00.000 -0700 | CLOUD_SERVICES_ONLY | 0.000009167 | 0.000000000 | 0.000009167 |
-- +-------------------------------+-------------------------------+-------------------------+--------------+----------------------+-----------------------------+
-- 17 Row(s) produced. Time Elapsed: 0.814s
-- Set context
USE DATABASE SNOWFLAKE_SAMPLE_DATA;
USE SCHEMA TPCH_SF1000;
-- creating a resource monitor with 10 credits to reset every week with appropriate actions and notifications
create resource monitor "VW_10" with Credit_quota = 10, frequency ='WEEKLY'
start_timestamp = 'IMMEDIATELY' end_timestamp = NULL
triggers
on 95 percent do suspend_immediate
on 50 percent do notify;
-- set the resource monitor at warehouse level
alter warehouse "COMPUTE_WH" SET REsource_monitor = 'VW_10';
-- we can set the resource monitor at account level as below
-- alter account set resource_monitor = 'VW_10';
-- adding additional roles to view and alter the configuration of above resource monitor
grant monitor, modify on resource monitor "VW_10" to role sysadmin;