Jeff,
I am sorry if I have confused you.
I still stand on the same point that there are set of update statements in a stored procedure.Due to these updates, CPU is touching almost 100%.
But when I have executed one single update query and compared the old and new query ,
new query is taking high cpu than the old one
If I could tune one update statement then I can apply the same to rest of the update statements and reduce cpu issue
Please find the below query which I have modified
Update dnbatr set fax=x.fax
from de_norm_buscard_attr dnbatr
inner join view_a x on x.contractid=dnbatr.contract_id
inner join table_a pcm WITH (NOLOCK) on pcm.contract_id=dnbatr.contract_id
inner join table_b pra on pcm.pcm_party_role_id=pra.party_role_id
INNER JOIN table_c y WITH (NOLOCK) ON pra.party_phn_id=y.party_phn_id
where (y.lst_updt_dtm >='02/12/2012' OR pra.lst_updt_dtm >='02/12/2012' )
FYI,
The no of rows per table
de norm table -- 297352
view_a -- 296781
table_a -- 297347
table_b -- 450238
table_b -- 276249
Thanks