UPDATEが動いたり止まったり…

質問

SQLServer2000SP4の環境で、
夜間バッチ処理で

update テーブルA
set 項目A1 = (select 項目B1 from テーブルB where 項目B2 = 項目A2)
where 項目A2 in(
select 項目A2　from テーブルA
left outer join テーブルB on 項目A2 = 項目B2
where 項目A1 <> 項目B1
)

という処理で、テーブルAとテーブルBの項目1という値が違うものだけ対象にして、テーブルBの項目1をテーブルAの項目1にセットしています。

この処理で正常に終わることがほとんどなのですが、月に2度程、このコマンドのまま停止した状態（エラーなし。コマンドタイムアウトは0にしています）になることがあります。

きっかけとしては、テーブルAのA1にインデックスを設けたということがあり、エラー対策としてインデックスデフラグを処理直前にかけていました。いっときは正常に動いていたのですが、また停止したので、インデックスフラグメントが原因ではなさそうです。（ちなみにこのUPDATEで更新される件数は数十件です）

テーブルA：100万レコード程
主キー：項目A2
インデックス：項目A1

テーブルB：100万レコード程
主キー：項目B2

エラーメッセージも何もでなくて、処理が継続中のような状態で止まっているのでこのUPDATE一文でインデックス更新にロックが掛かっているのかなと勝手に想定はしておりますが、どなたか原因と回避を教えていただきたく質問させて頂きました。よろしくお願いいたします。

回答No.1

jamshid6

クエリプランを見てみないとはっきりは言えないのですが、A1のインデックスが使われる内容には見えません。
フルスキャンが増えるとロックが競合する可能性も高くなりますし、とりあえずクエリ自体を書き換えてみてはどうでしょうか（以下のクエリと同じに見えます）。

update テーブルA
set 項目A1=b.項目B1
from テーブルA a
inner join テーブルB on b.項目B2 = a.項目A2
where a.項目A1 <> b.項目B1

お礼

01/05からのご返信頂いたSQL文を参考にしたもので動かしておりますが、今のところ正常に動いております。もっと長期で見る必要もあるかもしれませんが、いったんここで締め切らさせていただきます。
アドバイスありがとうございました！

補足

早速のご返信ありがとうございます。
私の書き方が悪かったのですが、テーブルAとテーブルBはレコード数が違うことが極稀にあり、その場合、項目A1にはNull値を入れたいので「Left Onter Join」となる(質問ではisnullを省略してました)のですが、ご返信頂いたSQL文を参考に以下のように変更して実験しました。

update テーブルA
set 項目A1 = 項目B1
from テーブルA
left outer join テーブルB
on 項目A2 = 項目B2
where isnull(項目A1,'') <> isnull(項目B1,'')

クエリコストで明らかに参照箇所が減ったので速度期待したところ、既存の2倍～6倍の速度で更新が可能になりました！！ありがとうございました。

しかし、問題の「停止の回避」についてはもともと発生頻度が低いため長期運用してみないとなんとも言えないところです。

クエリコストでは項目A1に対して参照時にインデックスを見ることは無いのですが、当然ですが、更新後に項目A1のインデックスに対して「IndexUpdate」が行われます。これはご返信頂いたSQL文でも同じとなります。
しかし、私がもともと記述していたSQL文ではもしかしたらレコード単位で「IndexUpdate」が走ってしまっているのでは？？？
と、ご返信頂いたSQL文と比較しながら感じております。
たまたまINDEXのページ分割の時に動かなくなってしまっているとか…ですね。

とりあえず、ご指導頂いた構文で運用してみます。長期運用後になりますが、結果をご報告させて頂きます。