Snowflake Query Workload Test

The query performance of the Snowflake service depends a lot on the query workload on the target database. High workload can affect the query performance in opposite direction, which can in turn lead to disruption of user services. It is important to monitors each database under the client’s Snowflake service account to provide insights into query workload.

This test monitors the Snowflake database and provides vital statistics related to query execution like average and maximum time taken for query execution, how long the queries have to be queued before execution, for how long transactions were blocked, etc. Administrators can look at these metrics and get to the root cause of degradation in query performance and clear out the blocked queries and transactions.

Target of the test : Snowflake Database server

Agent deploying the test : A remote agent

Outputs of the test : One set of results for each database in the Snowflake service account being monitored.

Configurable parameters for the test

Parameter	Description
Test period	How often should the test be executed.
Host	The IP address of the Snowflake.
Port	The port number through which the snowflake database server communicates. The default port is 443.
Warehouse Name	In this text box, enter the name of a virtual warehouse that needs to be monitored.
Database Name	In this text box, enter the name of a default database that will connect the snowflake server.
User Name, Password and Confirm Password	In order to monitor a Snowflake account which hosts the Snowflake instances to be monitored, you need a special user with privileges of an account administrator who can access the Snowflake instances and execute the required commands to pull out the performance metrics. To know how to create such a user and assign a role, refer to Pre-requisites for Monitoring Snowflake. Specify credentials of such user in the User Name and Password text boxes. Confirm the password by retyping it in the Confirm Password text box.

Measurements made by the test

Measurement	Description	Measurement Unit	Interpretation
Maximum execution time	Indicates the total number of query executions that are in resuming status during the last measurement period.	Seconds	Detailed diagnosis of the measure indicates the session id, start time, username, schema name, query id, query text for top n execution times.
Maximum compilation time	Indicates the maximum query compilation time during the last measurement period.	Seconds	Detailed diagnosis of the measure indicates the session id, start time, username, schema name, query id, query text for top n compilation times.
Maximum queued provisioning time	Indicates the maximum query queued provisioning time during the last measurement period.	Seconds
Maximum queued overload time	Indicates the maximum query queued overload time during the last measurement period.	Seconds
Maximum queued repair	Indicates the maximum query queued repair time during the last measurement period.	Seconds
Maximum transaction blocked time	Indicates the maximum transaction locked time during the last measurement period.	Seconds	Ideally, the value of this measure should be zero. A gradual increase in the value of this measure is a cause of concern, as it indicates a consistent rise in blocked transactions. This calls for immediate action. The detailed diagnosis capability, if enabled lists all the blocked transactions and the users who have initiated those transactions. Using this detailed diagnosis, you can figure out the root cause of the blocking.
Recently executed queries	Indicates the number of queries recently executed on this database server.	Number	If the number of queries executed recently is very high, it might be a indication of high workload on database server.
Average elapsed time	Indicates the average running time of the queries on this database server.	Seconds	If the average execution time of the queries is too high it might be ana indication of high workload.