Our entire Thinkwise stack is running on Azure, this means we try to give resources just enough resources for the expected usage.

Recently we started having performance issues on our dev environment, I was able to trace the problem down to the query I included below. It appears to be a clean up for all the process_flow_schedule_logs, this runs every 5 mins and takes on average 40 seconds per run. In this time it caps out our SQL CPU for our plan.

The select query is fast, the delete makes it slow. This is as far as I looked as it's IAM but no matter what I feel like there might be some issue here causing the delete to be this slow. 

(@system_flow_log_retention int)with log_items as (
        select log_id
        from process_flow_schedule_log l
        where not exists (select 1
                          from system_flow_excluded_from_log_clean_up s
                          where s.project_id = l.project_id
                            and s.project_vrs_id = l.project_vrs_id
                            and s.gui_appl_id = l.gui_appl_id
                            and s.process_flow_id = l.process_flow_id)
          and (l.end_date_time is not null or l.abandoned = 1) --Make sure the system flow has finished or abandoned its scheduled run
          and l.log_id < (select log_id
                          from process_flow_schedule_log l2
                          where l2.project_id = l.project_id
                            and l2.project_vrs_id = l.project_vrs_id
                            and l2.gui_appl_id = l.gui_appl_id
                            and l2.process_flow_id = l.process_flow_id
                          order by log_id desc
                          offset @system_flow_log_retention rows --Keep first X amount of rows
                          fetch first 1 rows only)

    )
    delete l  
    from log_items l