Determining why queries are queued

Queries are queued when resources are constrained. You can view your current resource consumption, to identify your most constrained resource, by running the MON_GET_SERVICE_SUPERCLASS table function.

The MON_GET_SERVICE_SUPERCLASS table function can expose whether your most constrained resource is the number of threads or the amount of sort memory. Based on the output, you can run the following procedures to identify potentially problematic queries:
  • Queuing caused by high thread usage.
  • Queuing caused by high sort memory usage.
  • Queuing caused by queued queries with high sort memory estimates.
  • Displaying resource usage information for executing or queued queries.

Examples

The following example shows the command syntax for running the table function:
WITH PER_PART_VALUES(THREAD_UTIL_PCT, SORT_DEMAND_PCT, SORT_UTIL_PCT) AS 
    (SELECT SUM(AGENT_LOAD_TRGT_DEMAND) AS THREAD_UTIL_PCT, 
            SUM(SORT_SHRHEAP_DEMAND) AS EST_SORT_PCT, 
            SUM(SORT_SHRHEAP_UTILIZATION) AS SORT_PCT 
     FROM TABLE(MON_GET_SERVICE_SUPERCLASS(NULL, -2)) AS T 
     GROUP BY MEMBER) 
SELECT MAX(DEC(SORT_UTIL_PCT,5,2)) AS PERCENT_SORTMEM_USED,
       MAX(DEC(SORT_DEMAND_PCT,5,2)) AS PERCENT_SORTMEM_DEMAND, 
       MAX(DEC(THREAD_UTIL_PCT,5,2)) AS PERCENT_THREADS_USED
FROM PER_PART_VALUES;
The following example shows the output from the previous operation, which indicates that sort memory, not threads, is the most constrained resource.
PERCENT_SORTMEM_USED PERCENT_SORTMEM_DEMAND PERCENT_THREADS_USED
-------------------- ---------------------- --------------------
               76.99                  80.03                11.76