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
I've seen this and similar hundreds of times. If you look carefully in Books Online, you'll never find an UPDATE there that looks like the one you have. You're updating 1 table from 2 other tables with no join between the table being updated and the source tables. Basically, you have a CROSS JOIN that SQL Server forgave up until the data got big enough to drive it nuts.
The target table of the update ABSOLUTELY MUST be in the FROM clause and properly joined to the other table to work. I wouldn't be suprised to see your times drop to just a couple of seconds or less if you were to write the update as I suggested.
And get rid of that LOOP join hint... it's part of the reason you weren't able to catch this error earlier.
--Jeff Moden
Change is inevitable... Change for the better is not.