その発端
http://blogs.wankuma.com/esten/archive/2008/03/22/129035.aspx
FinalWars その１
http://blogs.wankuma.com/esten/archive/2008/04/09/132463.aspx
オブジェクトのブロッキングで絡まってしまったおろちの首。絡まらないで仕事をさせるには、ロックをはずせば良い。そう考えた胡桃の脳な私は、ちょこっと細工をした。
「インデックスの行ロック」「インデックスのページロック」
チェックボックスをOFF。インデックスを再構築して、再び、おろちで仕事。
でも、止まる。おんなじところで「ScanStart/End」。いや、こんどの場合は
利用状況の画面に「X」がさらに倍にどばーーっ！
最悪やん！
なんでやねーーんっ！
しかもトランザクションロックが動いて、ブロッキングも頻繁してえらいことに。で、このロック、運悪くTransactionScopeの中、はい、DTSががーーーーーっつり掴んでます。強制終了なんて効きやしねぇ。あー、もう、ど・う・に・も・とまらない～（号泣）SQLServer強制再起動（恐）
周りに謝りまくり、始末書もどき書き、ごめんなさい会議までやっちゃう羽目に（大汗）これで一日丸つぶれ（はぁと）
というわけで、これはやっちゃいけないことだったんだね。うん、反省。ちょっと色々といぢりすぎた。
で、気持ちを切り替えて、
別の方面からのアプローチ。問題のSQLの実行プランをだしてみた。実行プランは出てくる。「なーんだ、ちゃんと解析できてるやん」とホッとしたのもつかの間、ふと、比べていて気がついた。おろちとギドラ、同じレコード同じテーブル同じ件数同じデータでありながら、実行プランが微妙に違う。
ギドラはテーブル副問い合わせにはクラスタインデックスを使用せず、おろちはそこにクラスタインデックス。両方とも内部パラレル処理なのは同じなんだけど……
何で違うん？
ギドラとおろちはテーブル構成も一緒、作りも一緒。違うのはCPUの数、メモリの量。それに……それに、実行回数（爆死）で、はたと考えた。テスト機のギドラはそりゃもう、とんでもない回数をこなしてきた百戦錬磨の練りこまれた統計情報を持っている。一方おろちは、必要な時に必要な回数だけをピンポイントに実行してきた、いわば世間知らずな深窓のおぜうさまｗ
まさか、まさかぁ、ねぇ？ なんて思いながら、蓋を開けてみた。
統 計 情 報 それはパンドラの箱ｗ
うそぉぉん。全然違う、チャウチャウチャウチャウ！ちゃいすぎぢゃっ！ 回数をこなしたギドラがこのSQLを実行するときに選択する最適なプラン、クラスタインデックスを外のDで使い内側のSではあえて別のインデックス（まぁちょっと似てるようなやつなんだけど、スキーマバインディングしていたビューのクラスタインデックスｗ）を使っていた理由はそこかぁ！（意義ありぃっ！びしぃっ！画面が揺れるっってくらいの衝撃ｗ）。恐らくはテストで張ったりはずしたりしたインデックスの情報さえも残すのかナンなのか、ギドラはすでにロックをうまくすりぬける方法をマスターしていてそれを駆使して仕事をする荒業をやってのけていた。さすがギドラだぜ！空間移動で光りながら現れてピロピロピロだぜ（昭和）！地球守護聖獣千年龍王だぜ（平成）！引力光線吐きまくって空飛ぶぜ（共通ｗ）！
さらに、その観点から再度両方のProfilerログを見直して、ふと、気づいた。ブロッキングが頻発していたおろちの自動統計情報更新処理はトランザクションロックタイムアウトでことごとくダウンして中断。バックグラウンド処理のために、誰にも注意を払われていなかったのだけれど、これがジワジワと効いていて、気がつけば必要な情報がろくに揃ってない状態になっていたらしい。そんな中で、それでも首はある限りの情報で仕事を計画し、働こうとして、でもやっぱり首が絡まって……かわいそう、なんてかわいそうなんだ！やまだのおろち！
ひととおり同情したら、基本に立ち返ろう。
問題のSQLにある外SQLのDと副問いSQLのS、ギドラがやろうとしたように異なるインデックスで実行すれば、おろちでも早くなれるはず。確かにSQLServerは与えられているリソースの中で最適な方法で処理をしようとプランをつくるけど、しょせんはロジック、機械、AIじゃない。人間がなんとかしようとしなければ、結局はハコの中で動作する０と１の電気信号にすぎないと思うの。そこをキチンと把握して、うまく仕事ができるようにしてやらないとイカンのではない？
というわけで
UPDATE D SET D.ID = S.ID FROM HOGE1 D , (SELECT A1,A2,A3, ROW_NUMBER over (Order by A1 ) as ID FROM HOGE1 WHERE A4 is null ) S WHERE D.A1 = S.A1 AND D.A2 = S.A2 AND D.A3 = S.A3
HOGE1テーブル用に、クラスタインデックス（A1,A2,A3）とは別に、A1とA4の非クラスタ（付加列A2,A3）を追加。そして最重要？おろちのメンテ。DBCC CHECKDBと統計情報更新とインデックス再構築を手動で実行！ついでにSQLServerも再起動！で、まっさら？なギドラとおろち、再び、ゴング！
ギドラで実行→５分４２秒、うん、なんか、心持ち早い（笑）
おろちで実行→１分５３秒、うおー！すげーーーっ！はええぇぇっっ！
というわけで、おろち、復活。つか、前より早くなってねぇかい？
あらためて、ProFilerとパフォーマンスモニターでチェックしてみたところ、新しいインデックスが参戦したことで統計情報が一新されたらしくHOGE1の処理をしていた他のSQLまでもが高速化（笑）統計情報更新もロックタイムアウトすることなく全ての処理を美しく完了。ブラボー♪それってなんて素敵だし爽やかなチューニング！！（自画自賛！）
つまりはだ、おろちでは
副問い合わせと本体が同一テーブルのUPDATEを行おうとしたときに統計情報の不足から同一のテーブルしかも同一のインデックスを使って処理を行うプランが作成され、それを実行しようとして自分で自分の使いたいリソースをロックして自分で自分をロック解除待ちにしてしまい、それによってその接続プールのトランザクションがロック状態。マルチスレッド上の別のSQL実行もそのトランザクション処理を待ってしまうために統計情報自動更新の処理さえもトランザクションタイムアウトを発生して全体が鈍足化してしまっていた。CPUとメモリが豊富なマシンでは同時に走る事のできるスレッドの数もふえるため、少ないマシンに比べてロック待ちとなる処理も増える。
ということだったんだな。おろち、てめぇ、らめぇ（笑）
やらなくちゃいけなかったこと
同一テーブルへの処理であっても、副問い合わせを使うということは別テーブルをメモリというか一時テーブルに展開するのと同意。なので、本体処理と別のインデックスを提供もしくはスキーマバインドビューをつかって競合を避けさせる必要がある。その処理が複数CPUによるパラレル処理を実行プランに持つ場合、自身のリソース同士のブロッキングを引き起こしやすい状況になることも考慮すること。そして、サーバーでの統計情報更新、インデックスの再構築、データベースファイルおよびトランザクションログファイルの適切なメンテナンスはデータベースサイズが大きくなり、かつ使用するCPUとメモリが多いほど不可欠な作業、怠ると劣化鈍足化の原因になる。
小回りなギドラにくらべ、おろちはずっと手のかかる構ってチャンだったわけだね。だからこそ、きちんと手をかけてやればそれだけの働きもするってことで（笑）
まぁ、これで、おろちはギドラに勝った……んだけどさ。おろちの追加インデックス作成の申請を通せるのかどうかは、大人の事情だな。でもね、一つ、すごいこと。この実行テストの後に追加したインデックスを削除したとしても、それ以降ロックを起こした実行プランを使うことは全く起きなかった、どころか、回数を重ねれば重ねるほど高速化して、最後には1分10秒台に。ギドラで57分掛かっていたデータ処理も最終的には16分で処理するところまでやりきれた。なんだかもう、カイザーギドラおろちモードってカンジだったね、ラストは（謎）プランキャッシュとリコンパイルを徹底的に意識したSQL文を作って処理させたというのはあるにせよ、それにしても統計情報の蓄積とプランキャッシュの有効活用によって起きる効果ってのはホント絶大なんやねぇ。それゆえに、うまくいかないとボロボロにもなるわけだがｗ うん、良い経験をした、ってことにしておこう（笑）
今回、読みまくって、脳みそを胡桃味噌に発酵させてくれたモノたち
Microsoft SQL Server 2005 のクエリ オプティマイザが使用する統計情報
http://www.microsoft.com/japan/technet/prodtechnol/sql/2005/qrystats.mspx
SQL Server 2005 のバッチのコンパイル、再コンパイル、およびプランのキャッシュに関する問題
http://www.microsoft.com/japan/technet/prodtechnol/sql/2005/recomp.mspx
ぷらす、SQLServer2005 Books Online & TechNet & MSDN あちこち
以下、海の向こうのサイト（おい）
よく出入りしている、SQLServerPeformance.com の記事から、
統計情報更新について
http://www.sql-server-performance.com/tips/update_statistics_p1.aspx
インデックスを作って使う時のツボ系ｗ
http://www.sql-server-performance.com/tips/index_main.aspx
パフォーマンスチューニングのツボ。とりあえずひととおりｗ
http://www.sql-server-performance.com/tips/performance_main.aspx
後、ここいらの本。めっちゃ勉強になった良書。SQLServerいぢるなら読んどいて損はないです。でも日本語版ないよね……翻訳してみたいくらいなんだけど（笑）
Inside Microsoft SQL Server 2005: Query Tuning and Optimization
http://www.amazon.co.jp/Inside-Microsoft-SQL-Server-2005/dp/0735621969/ref=sr_1_1?ie=UTF8&s=english-books&qid=1207789032&sr=1-1
Inside Microsoft SQL Server 2005: T-sql Querying
http://www.amazon.co.jp/Inside-Microsoft-SQL-Server-2005/dp/0735623139/ref=pd_sim_fb_img_1
いぢょ♪