We had an issue arise last night where there was a Forms SQL query that ended up blocking other queries. The query in question is this one:

SELECT milestone,

MST.name AS milestone_name,

MST.is_deleted AS milestone_deleted,

MST.m_order AS canvas_order,

SUM([dbo].[CalculateStepStageCycle](@bp_id, H.milestone, H.bp_instance_id, @start_date_from, @start_date_to, @end_date_from, @end_date_to, @timezone_offset, @exclude_weekends, @step_id, @is_inprogress, @current_utc_time) / @SECONDS_PER_DAY) AS total_cycle_time,

SUM(DATEDIFF(second, MI.start_date,  CASE @is_inprogress WHEN 2 THEN MI.end_date ELSE @current_utc_time END) / @SECONDS_PER_DAY) AS total_instance_cycle_time,

COUNT(H.bp_instance_id) AS main_instance_count

FROM (SELECT DISTINCT MI.bp_instance_id,

S.milestone,

P.process_id

FROM cf_bp_processes AS P

INNER JOIN cf_bp_main_instances MI ON P.process_id = MI.process_id

INNER JOIN cf_bp_worker_instances WI ON MI.bp_instance_id = WI.bp_instance_id

INNER JOIN cf_bp_worker_instance_history WIH ON WIH.instance_id = WI.instance_id

INNER JOIN cf_bp_steps S ON CASE @is_inprogress WHEN 2 THEN WIH.step_id WHEN 1 THEN WI.current_step_id END = S.step_id AND WIH.process_id = S.process_id

WHERE P.bp_id = @bp_id

AND MI.status = @is_inprogress

AND (@is_test_mode IS NULL OR MI.is_test_mode = @is_test_mode)

AND (@step_id IS NULL OR WIH.step_id = @step_id)

AND (@start_date_from IS NULL OR MI.start_date >= @start_date_from)

AND (@start_date_to IS NULL OR MI.start_date <= @start_date_to)

AND (@end_date_from IS NULL OR MI.end_date >= @end_date_from)

AND (@end_date_to IS NULL OR MI.end_date <= @end_date_to)

AND (((WI.status IN (2,5,11,3,1,13)) AND (@is_inprogress = 1)) OR (@is_inprogress = 2))) H

INNER JOIN cf_bp_milestones MST ON H.milestone = MST.milestone_id AND H.process_id = MST.process_id

INNER JOIN cf_bp_main_instances MI ON MI.bp_instance_id = H.bp_instance_id

GROUP BY H.milestone, MST.name, MST.m_order, MST.is_deleted

I am trying to narrow down what would execute this query. My best guess is that it is related to the Forms Insights reports. Can anyone tell me if this is a query that would be run as part of a schedule or would someone have to manually go to one of the reports for it to execute, or is this something completely different?

We are on Forms 12.0.2509.20409.