Workload Monitoring Run Count

Help
1

Hi! I have a question about Workload Monitoring, specifically what “Run Count” really means. I am working through debugging/optimizing some particularly complex queries for my organization. I ran the Workload Monitoring tool for 1 minute, and it captured a query activity that should have only one once during that time period. However, “Run Count” was 2.

When I ran it for 5 minutes, again during a time interval with the expectation that these queries would be run once. Their Run Counts were > 100! I was wondering if what I’m seeing could include parallel executions of the same query? Or does this mean that client(s) executed the given query that many times during the recording?

Thank you

1 Like
2

Hi Eric! Welcome. :wave:

Would you happen to be on the managed service or self-hosted; and what version number?

3

Hi! Self-hosted, version 7.5.8

4

Erica, What you’re looking for is the total executions of your query, which needs to be calculated. This active session history query will give you the top 10 most executed queries.

use information_schema;
SELECT
(run_count+success_count+failure_count) AS total_executions,
substr(replace(replace(query_text,’\n’,’’), ’ ‘,’ '),1,30) as query_text,
mv_queries.activity_name,
database_name,
(elapsed_time_ms/(run_count+success_count+failure_count)) AS avg_elapsed_time_ms,
last_finished_timestamp AS last_run,
(cpu_time_ms/(run_count+success_count+failure_count)) as avg_cpu_time_ms,
(cpu_wait_time_ms/(run_count+success_count+failure_count)) AS avg_cpu_wait_time_ms,
(lock_time_ms/(run_count+success_count+failure_count)) AS avg_lock_time_ms,
(network_time_ms/(run_count+success_count+failure_count)) AS avg_network_time_ms,
(disk_time_ms/(run_count+success_count+failure_count)) AS avg_disk_time_ms,
(round((disk_b/1024/1024),2)/(run_count+success_count+failure_count)) AS avg_io_mb,
(round((network_b/1024/1024),2)/(run_count+success_count+failure_count)) AS avg_network_mb,
round((1000*(memory_bs/1024/1024)/(elapsed_time_ms)),2) AS avg_memory_mb,
(memory_major_faults/(run_count+success_count+failure_count)) AS avg_major_faults
FROM information_schema.mv_activities_cumulative JOIN information_schema.mv_queries
ON mv_queries.activity_name = mv_activities_cumulative.activity_name
WHERE activity_type = ‘Query’
and database_name = ‘s2_dataset_tpch’
and last_finished_timestamp <= now() and last_finished_timestamp >= (now() - interval 10 minute)
order by
total_executions desc
limit 10

2 Likes