During this post we will discuss about some important queries which we can use in our day-to-day task to determine the list of warehouses where Data spill to the Remote. Queries to find out the Active Roles in your particular Database or Schema. In addition to it we will see the queries to find out the full table scan queries approx. 90%.
Moreover will discuss the Queries to find the Privilege’s assign to the Role and in turn Roles assign to the Users. In addition we will see warehouse_load_history to identify how much work was done over a period of time (load) and Warehouse_Metering_History view is used to return the hourly credit usage for one or more warehouses in your account.
1: Firstly, Query to Find Active Roles in Database:
Select * From Snowflake.Account_Usage.Roles
Where DELETED_ON IS NULL;
2: Query to find Privileges in Database:
Object_Catalog as DB_NAME,
Object_Schema as DB_SCHEMA,
Object_Name as OBJ_NAME,
3: Query to Find Privileges assigned Role:
NAME AS OBJECT_NAME,
Table_Catalog as DB_NAME, Table_Schema as SCHEMA_NAME,
Grantee_Name as ROLE_ASSIGNED_PRIV,
4: Roles Assigned to Users:
Select * from Snowflake.Account_Usage.Grants_To_Users
5: Users who have not logged in with 90 days of account creation
Where "last_success_login" IS NULL
And DateDiff('Day',"created_on",Current_Date) > 90;
6: List of Warehouse where data spilled to Remote disk
SELECT * FROM "SNOWFLAKE"."ACCOUNT_USAGE"."QUERY_HISTORY"
WHERE BYTES_SPILLED_TO_REMOTE_STORAGE > 0;
7: Query to find out the full table scan queries approx. 95%
WHERE PARTITIONS_SCANNED > (PARTITIONS_TOTAL*0.95)
8: The Warehouse_Metering_History view is use to return the hourly credit usage for on or more warehouses in your account, for a specified date range
Credits_Used as Credits_Billed,
Credits_Used_Compute as Credits_Used,
Credits_Used_Cloud_Services as Credits_Cloud_Services
Order By Start_Time;
9. Warehouse Profiling
To profile your current warehouse usage, use the WAREHOUSE_LOAD_HISTORY and WAREHOUSE_METERING_HISTORY functions. A good way to think about the relationship between these two functions is that the first one shows how much work was done over a period of time (load) and the second one shows the cost for doing the work (metering).
select * from table(information_schema.warehouse_load_history(date_range_start=>dateadd('hour',-1,current_timestamp())));
select * from table(information_schema.warehouse_metering_history(dateadd('hour',-1,current_date()),current_date()));
10.Long Running Query :Retrieve all currently running queries that have exceeded a certain amount of time.
select * From Snowflake.Account_Usage.Query_History
Execution_Status ilike 'RUNNING' And
DateDiff(Second, Start_Time, Current_Timestamp) >= 300;