Monitoring service class resource entitlement and usage

Service classes are entitled to a percentage of resources based on their configured resource shares. The adaptive workload manager schedules and admits based on these configured entitlements, while respecting any reserved resources. Over time, it is useful to monitor resource usage and entitlement to ensure an optimal configuration.

The following monitor elements are available to observe the resource entitlement and actual resource usage over time:

RESOURCE_ENTITLEMENT: Percentage of resources that a service class is entitled to based on the configured resource shares for the service class. The minimum resource share percentage from the syscat.serviceclasses catalog view can be used to derive the minimum resource share based on the reported entitlement.
AGENT_LOAD_TRGT_UTILIZATION_AVG: Average utilization of threading resources by work running in the service class, expressed as a percentage of the total threading resources (wlm_agent_load_trgt x number of physical cores).
AGENT_LOAD_TRGT_UTILIZATION_TOP: Peak utilization of threading resources by work running in the service class, expressed as a percentage of the total threading resources (wlm_agent_load_trgt x number of physical cores).
SORT_SHRHEAP_UTILIZATION_AVG: Average utilization of shared sort memory by work running in the service class, expressed as a percentage of the configured shared sort memory (sheapthres_shr).
SORT_SHRHEAP_UTILIZATION_TOP: Peak utilization of shared sort memory by work running in a service class, expressed as a percentage of the configured share sort memory (sheapthres_shr).

The utilization monitor elements all report resource usage as a percentage which is useful when comparing utilization vs entitlement which is also expressed as a percentage. The following monitor elements also exist that show the absolute values for the resource usage:

EFF_PARALLELISM_TOP: Peak combined effective parallelism (query degree) for all queries concurrently executing in a service class.
EFF_PARALLELISM_AVG: Average combined effective parallelism (query degree) for all queries concurrently executing in a service class.
SORT_SHRHEAP_TOP: Peak shared sort memory usage for all queries concurrently executing in a service class, reported in 4 KB pages.
SORT_SHRHEAP_AVG: Average shared sort memory usage for all queries concurrently executing in a service class, reported in 4 KB pages.

All the above elements are reported by the various service class statistics interfaces including:

Statistics Event monitor (superclassstats and scstats logical data groups)
MON_GET_SERVICE_SUBCLASS_STATS table function
MON_GET_SERVICE_SUPERCLASS_STATS table function

The prior monitor elements report the historic peak and average resource usage observed over time. The following monitor elements report the current resource usage. These monitor elements are available in the MON_GET_SERVICE_CLASS and MON_GET_SERVICE_SUPERCLASS table functions:

EFF_PARALLELISM: Current combined effective parallelism (query degree) for all queries concurrently executing in a service class.
AGENT_LOAD_TRGT_UTILIZATION: Current utilization of threading resources by work concurrently executing in the service class, expressed as a percentage of the total threading resources (wlm_agent_load_trgt x number of physical cores).
SORT_SHRHEAP_ALLOCATED: Current shared sort memory usage for all queries concurrently executing in the service class, reported in 4 KB pages.
SORT_SHRHEAP_UTILIZATION: Current utilization of shared sort memory by all queries concurrently executing in the service class, expressed as a percentage of the configured shared sort memory (sheapthres_shr).

When working resource usage monitor elements in a DPF environment, it is important to remember that the most constrained resource on the most constrained partition is the limiting factor for admission of new work. For example, if sort memory is fully utilized on one partition, and an incoming query requires sort memory on that partition, the query is queued. Consequently, in such an environment it is generally necessary to aggregate values across partitions in monitor queries and consider the max utilization (most constrained resource) between threads and sort memory.

Examples

Example 1: Monitor average and peak resource usage vs entitlement and minimum share over time for the following 2 service classes to determine how resource utilization compares to entitlement, and which resources are the most heavily contended:

S1, which has a soft 25% resource entitlement
S2,which has a soft 75% resource entitlement

Use a statistics event monitor named S to capture service class usage information on a regular interval. Issue the following query to view the service class resource entitlements, resource usage and most constrained resource over time. Note that this aggregates usage across partitions to view the most constrained resource.

SELECT STATISTICS_TIMESTAMP,                                                                                                                                                                 
       SUBSTR(SERVICE_SUPERCLASS_NAME, 1, 20) as SUPERCLASS,                                                                                                                               
       DECIMAL(MAX(RESOURCE_ENTITLEMENT), 5, 2) ENTITLEMENT,                                                                                                                                 
       DECIMAL(MAX(RESOURCE_ENTITLEMENT * (MINRESOURCESHAREPCT) / 100), 5, 2) AS MINIMUM_ENTITLEMENT,                                                                                        
       CASE WHEN MAX(AGENT_LOAD_TRGT_UTILIZATION_AVG) > MAX(SORT_SHRHEAP_UTILIZATION_AVG) THEN                                                                                               
          'THREADS'                                                                                                                                                                          
       ELSE                                                                                                                                                                                  
          'SORT MEMORY'                                                                                                                                                                      
       END AS CONSTRAINED_RES,                                                                                                                                                               
       DECIMAL(MAX( CASE WHEN AGENT_LOAD_TRGT_UTILIZATION_AVG > SORT_SHRHEAP_UTILIZATION_AVG THEN                                                                                            
                     AGENT_LOAD_TRGT_UTILIZATION_AVG                                                                                                                                         
                    ELSE                                                                                                                                                                     
                     SORT_SHRHEAP_UTILIZATION_AVG                                                                                                                                            
                    END ), 5, 2) AS CONSTRAINED_RES_PCT,                                                                                                                                     
       DECIMAL(MAX(AGENT_LOAD_TRGT_UTILIZATION_AVG), 5, 2) as AGENT_LOAD_UTIL_AVG,                                                                                                           
       DECIMAL(MAX(SORT_SHRHEAP_UTILIZATION_AVG), 5, 2) AS SORT_SHRHEAP_UTIL_AVG                                                                                                             
FROM SUPERCLASSSTATS_EVMONSTATISTICSU1 A,                                                                                                                                                    
     SYSCAT.SERVICECLASSES B                                                                                                                                                                 
WHERE A.SERVICE_CLASS_ID = B.SERVICECLASSID AND                                                                                                                                              
      A.SERVICE_SUPERCLASS_NAME IN (‘S1','S2')                                                                                                         
GROUP BY STATISTICS_TIMESTAMP,                                                                                                                                                               
         SERVICE_SUPERCLASS_NAME,                                                                                                                                                            
         MINRESOURCESHAREPCT

The output shows that, for this workload, threads are the most heavily contended resource. Consequently, observed concurrency is dictated by the wlm_agent_load_trgt configuration parameter (that is, by the configured thread resources). Note that the resource utilization average fluctuates above and below the entitlement. The resource requirements for queries rarely fit perfectly into the entitled resources. When soft resource entitlements are used, it is expected that the resource usage is sometimes higher and sometimes lower than the entitlement, but that over time the average resource utilization trends towards the entitlement. When hard resource entitlements are in effect, resource utilization usually remains below the entitlement, although exceptions might occur when resource estimates are inaccurate. The average utilization tends to be closer to the entitlement over longer monitoring intervals. The query captures data every 30 seconds, so swings in the average are more pronounced.

Note: the statistics timestamp has been removed to improve readability.

SUPERCLASS ENTITLEMENT MINIMUM_ENTITLEMENT CONSTRAINED_RES CONSTRAINED_RES_PCT AGENT_LOAD_UTIL_AVG SORT_SHRHEAP_UTIL_AVG
---------- ----------- ------------------- --------------- ------------------- ------------------- ---------------------
S1               24.99                0.00 THREADS                       20.26               20.26                  3.17
S2               74.99                0.00 THREADS                       77.42               77.42                 28.30
S1               24.99                0.00 THREADS                       31.34               31.34                  5.66
S2               74.99                0.00 THREADS                       66.46               66.46                 16.89
S1               24.99                0.00 THREADS                       20.75               20.75                  3.73
S2               74.99                0.00 THREADS                       77.95               77.95                 34.28
S1               24.99                0.00 THREADS                       18.76               18.76                  6.63
S2               74.99                0.00 THREADS                       75.44               75.44                 23.83
…

Use the following query to view the overall average for the resource utilization data collected in the statistics event monitor:

WITH MAX_RESOURCES(STAISTICS_TIMESTAMP,
                   SUPERCLASS,
                   RESOURCE_ENTITLEMENT,
                   CONSTRAINED) AS
     (SELECT STATISTICS_TIMESTAMP,
             SUBSTR(SERVICE_SUPERCLASS_NAME, 1, 20) as SUPERCLASS,    
             RESOURCE_ENTITLEMENT,
             DECIMAL(MAX( CASE WHEN AGENT_LOAD_TRGT_UTILIZATION_AVG > SORT_SHRHEAP_UTILIZATION_AVG THEN 
                     AGENT_LOAD_TRGT_UTILIZATION_AVG
                    ELSE
                     SORT_SHRHEAP_UTILIZATION_AVG
                    END ), 5, 2)
       FROM SUPERCLASSSTATS_EVMONSTATISTICSU1
       GROUP BY STATISTICS_TIMESTAMP, 
                SERVICE_SUPERCLASS_NAME,
                RESOURCE_ENTITLEMENT)
SELECT SUPERCLASS,
       DECIMAL(RESOURCE_ENTITLEMENT, 5,2) AS RESOURCE_ENTITLEMENT,
       DECIMAL(AVG(CONSTRAINED), 5, 2) AS AVG_RESOURCE_USAGE
FROM MAX_RESOURCES
GROUP BY SUPERCLASS,
         RESOURCE_ENTITLEMENT

Sample output:

SUPERCLASS RESOURCE_ENTITLEMENT AVG_RESOURCE_USAGE
---------- -------------------- ------------------
S1                        24.99              22.21
S2                        74.99              74.42

Example 2: Two service classes, S1 and S2, are created with an equal share of resources. Service class S2 has a hard resource share (that is, S2 cannot exceed its resource entitlement), to protect the more important work running in service class S1. You monitor the ADM_QUEUED_ACT_LOAD and ADM_RUNNING_ACT_LOAD metrics over time and notice the following trends:
- For S1, ADM_QUEUED_ACT_LOAD is zero and ADM_RUNNING_ACT_LOAD is a low value, This indicates that service class S1 is frequently empty. You speak with the business units whose work is routed to this service class, and determine that S1 is meeting its response time objectives.
- For S2, the ADM_QUEUED_ACT_LOAD value is nonzero. This indicates that work is frequently queued in service class S2, because the resource demand for work exceeds the configured resource entitlement.
Issue the following query to determine the peak resource usage requirements for service class S1 over time:
```
SELECT DECIMAL(RESOURCE_ENTITLEMENT, 5, 2) AS ENTITLEMENT,
       DECIMAL(MAX(AGENT_LOAD_TRGT_UTILIZATION_TOP), 5, 2) AS MAX_THREAD_UTIL,
       DECIMAL(MAX(SORT_SHRHEAP_UTILIZATION_TOP), 5, 2) AS MAX_SORT_UTIL
FROM SUPERCLASSSTATS_EVMONSTATISTICSU1
WHERE SERVICE_SUPERCLASS_NAME IN ('S1’)
GROUP BY RESOURCE_ENTITLEMENT
```
The output shows that, although the service class S1 has a resource entitlement of 50%, the work in the service class has never exceeded more than 25% of the configured resources. In this scenario, consider increasing the share of the resources assigned to service class S2, because you are satisfied with the performance of work in S1, and the work in S1 does not require more than 25% of the resources (that is, there is no need to cap the resources available to service class S2 to only 50%).
```
ENTITLEMENT MAX_THREAD_UTIL MAX_SORT_UTIL
----------- --------------- -------------
      50.00          25.49         17.56
...
```
Example 3: Important work runs in service class S1, which has a minimum resource share defined.
Work running in S1 is consistently meeting its SLA, while work in other service classes is queuing. To examine the resource utilization of S1 to see if the minimum resource share is configured too high, issue the following query:
```
SELECT DECIMAL(RESOURCE_ENTITLEMENT, 5, 2) AS ENTITLEMENT,
       DECIMAL(MAX(RESOURCE_ENTITLEMENT * (MINRESOURCESHAREPCT) / 100), 5, 2) AS MINIMUM_ENTITLEMENT,
       DECIMAL(MAX(AGENT_LOAD_TRGT_UTILIZATION_TOP), 5, 2) AS MAX_THREAD_UTIL,
       DECIMAL(MAX(SORT_SHRHEAP_UTILIZATION_TOP), 5, 2) AS MAX_SORT_UTIL
FROM SUPERCLASSSTATS_EVMONSTATISTICSU1 A,
     SYSCAT.SERVICECLASSES B
WHERE SERVICE_SUPERCLASS_NAME IN ('S1') AND 
      A.SERVICE_CLASS_ID = B.SERVICECLASSID
GROUP BY RESOURCE_ENTITLEMENT, MINRESOURCESHAREPCT
```
The output shows that the service class S1 has a minimum resource share of 25% (50% of its 50% entitlement). The output also shows that the resource utilization in the service class has at most been 10%. If the work in the service class is meeting its SLAs, consider reducing the minimum resource share for this service class so that workloads in other service classes can make use of these resources.
```
ENTITLEMENT MINIMUM_ENTITLEMENT MAX_THREAD_UTIL MAX_SORT_UTIL
----------- ------------------- --------------- -------------
      50.00               25.00          10.09          5.89
```