Monitoring Warehouse Load¶
The web interface provides a query load chart that depicts concurrent queries processed by a warehouse over a two-week period. Warehouse query load measures the average number of queries that were running or queued within a specific interval.
In this Topic:
Viewing the Load Monitoring Chart¶
To view the load monitoring chart, you must be using a role that has the MONITOR privilege on the warehouse.
To view the chart:
The Warehouse Load Over Time page appears with a bar chart and a slider for selecting the window of time to view in the chart. By default, the chart displays the past 8 hours in 5-minute intervals.
Using the Slider¶
To use the chart, first use the slider to select the range of time to view in the chart. You can select a range from 8 hours (minimum) to 14 days (maximum). The chart displays the total query load in intervals of 5 minutes to 1 hour, depending on the range you selected. As you change the size of the range, the intervals change dynamically to maintain the appropriate scale.
You can also drag the slider to any position within the 14-day range to display the load for that period of time.
Understanding the Bar Chart¶
Hover over a bar to view the average number of queries processed by the warehouse during the time period represented. The bar displays the individual load for each query status that occurred within the interval:
|Running||Queries that were actively running during the interval. Note that they may have started running before and continued running after the interval.|
|Queued||Queries that were waiting to run due to warehouse overload (i.e. waiting for other queries to finish running and free compute resources).|
|Queued (Provisioning||Queries that were waiting while the warehouse provisioned servers. Typically only occurs in the first few minutes after a warehouse resumes.|
|Queued (Repairing||Queries that were waiting while the warehouse repaired servers. Occurs only rarely and usually does not last for longer than a few minutes.|
How Query Load is Calculated¶
Query load is calculated by dividing the execution time (in seconds) of all queries in an interval by the total time (in seconds) for the interval.
Consider the following example:
The chart shows that, during the five-minute interval, the load from running queries was .92 and queued queries (due to warehouse overload) was .08. The following table illustrates how these values were calculated based on the 5 queries that contributed to the warehouse load during the interval:
|Query||Status||Execution Time / Interval (in Seconds)||Query Load|
|Query 1||Running||30 / 300||0.10|
|Query 2||Running||201 / 300||0.67|
|Query 3||Running||15 / 300||0.05|
|Query 4||Running||30 / 300||0.10|
|Query 5||Queued||24 / 300||0.08|
|TOTAL WAREHOUSE LOAD||1.00|
To determine the actual number of running queries (and the duration of each query) during a specific interval, consult the History page. On the page, filter the query history by warehouse, then scroll down to the interval you specified in the load monitoring chart.
Using the Load Monitoring Chart to Make Decisions¶
The load monitoring chart can help you make decisions for managing your warehouses by showing current and historic usage patterns.
Slow Query Performance¶
When you notice that a query is running slowly, check whether an overloaded warehouse is causing the query to compete for resources or get queued:
If the running query load is high or there’s queuing, consider starting a separate warehouse and moving queued queries to that warehouse. Alternatively, if you are using multi-cluster warehouses, you could change your multi-cluster settings to add additional clusters to handle higher concurrency going forward.
If the running query load is low, then you could resize the warehouse to provide more compute resources. You would need to restart the query to take advantage of the added resources.
Peak Query Performance¶
Analyze the daily workload on the warehouse over the previous two weeks. If you see recurring usage spikes, consider moving some of the peak workload to its own warehouse and potentially running the remaining workload on a smaller cluster. Alternatively, you could change your multi-cluster settings to add additional clusters to handle higher concurrency going forward.
Excessive Credit Usage¶
Analyze the daily workload on the warehouse over the previous two weeks. If the chart shows recurring time periods when the warehouse was running and consuming credits, but the total query load was less than 1 for substantial periods of time, the warehouse use is inefficient. You might consider any of the following actions:
- Decrease the warehouse size. Note that decreasing the warehouse size generally increases the query execution time.
- For a multi-cluster warehouse, decrease the MIN_CLUSTER_COUNT parameter value.