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.
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 |
Specify the name of the user who has any of the following privileges to the specified. |
Password |
The password of the specified USER. |
Confirm Password |
Confirm the password by retyping it here. |
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. |