twoeyed (10/23/2012)

---

The following query was executing within 1 minutes for the past 2 years without any issues.

update
dbo.INT_AS_VENTE_TITRE
set
P_STATUS = 'R',
P_ERROR_CODE = 'E000026'
from
Interface a
INNER LOOP JOIN AnotherDb.dbo.Trans b ON
(convert(varchar, a.TICKET_NUM) = b.TICKET_NUM and
a.TXN_DT = b.TXN_DT)

where
a.P_STATUS <> 'R'
and b.TICKET_NUM is not null
and b.TXN_DT is not null
OPTION (maxdop 0)

Since last month, the query has been taking more than an hour everyday, with CPU load at 99-100%. Trans contains in excess of 200million lines and Interface contains around 200 000 lines.
The Trans table has had its clustered index rebuilt, and statistics are regularly updated. I created an additional index with row_id, txn_dt and ticket_num after running tuning advisor, but the statement is still having same behaviour, even after a dbcc freeproccache.
I am running out of ideas.
Please suggest reasons as to why this statement could have changed behaviour so much, and how I could resolve this issue.
Thanks

twoeyed (10/26/2012)

---

Thank you all for taking the time to reply and for your valuable inputs.
I have managed to reduce the execution to around 6 minutes through the ideas proposed, which is not bad at all.

Thank you Chris and David for the posting advice.

Lee Crain (10/25/2012)

---

This observation may or may not be helpful.

I couldn't help but notice your WHERE clause:
"WHERE
a.P_STATUS <> 'R'
and b.TICKET_NUM is not null
and b.TXN_DT is not null"

All NOT comparisons cause scans instead of seeks. If you can find some way to avoid performing NOT comparisons, it would likely improve the performance of your query, if you have properly implemented supporting indexes. To what degree will depend on the sizes of your tables.