• Before considering esoteric wait stats and such, your code would benefit from an overhaul to bring your join syntax up to date. Your first query equates to this:

    UPDATE pro SET

    actual_amount=isnull(amount,0),

    earned_amount=isnull(amount,0)

    FROM processed_data pro

    INNER JOIN employee_current emp

    ON emp.pay_group_id = pro.pay_group_id

    INNER JOIN salary_fixed fix

    ON fix.employee_id = emp.employee_id

    AND fix.employee_id = pro.employee_id

    INNER JOIN component comp

    ON comp.component_id = fix.component_id

    AND comp.component_id = pro.component_id

    AND comp.pay_group_id = emp.pay_group_id

    INNER JOIN process_employee proemp

    ON proemp.employee_id = pro.employee_id

    AND proemp.pay_group_id = comp.pay_group_id

    WHERE emp.pay_group_id = @mpaygroupid

    AND comp.s_calculatiotype in ('X','C','F')

    AND proemp.s_timestamp = @mTimestamp

    which is quite messy. I think this can be rewritten as follows:

    UPDATE pro SET

    actual_amount=isnull(amount,0),

    earned_amount=isnull(amount,0)

    FROM processed_data pro

    INNER JOIN employee_current emp

    ON emp.pay_group_id = pro.pay_group_id

    AND emp.employee_id = pro.employee_id

    INNER JOIN salary_fixed fix

    ON fix.employee_id = pro.employee_id

    AND fix.component_id = pro.component_id

    INNER JOIN component comp

    ON comp.component_id = pro.component_id

    AND comp.pay_group_id = pro.pay_group_id

    INNER JOIN process_employee proemp

    ON proemp.employee_id = pro.employee_id

    AND proemp.pay_group_id = pro.pay_group_id

    WHERE emp.pay_group_id = @mpaygroupid

    AND comp.s_calculatiotype in ('X','C','F')

    AND proemp.s_timestamp = @mTimestamp

    where I've made a point of joining each table to one other table. I'm sure I'm not alone in finding queries written in this style far easier to tune than those employing old-style joins.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden