すいません、VB4しかやってないんです、VBAはやったけど(ぼそ) チラシの裏だって立派な書き込み空間なんだからねっ!資源の有効活用なんだからねっ!とか偉そうに言ってるけど、実は色々と書き残したいだけ

だからなに? どうしろと? くるみサイズの脳みそしかないあやしいジャンガリアンベムスターがさすらう贖罪蹂躙(ゴシックペナルティ)

ホーム 連絡をする 同期する ( RSS 2.0 ) Login
投稿数  632  : 記事  35  : コメント  11676  : トラックバック  143

ニュース


片桐 継 は
こんなやつ

かたぎり つぐ ってよむの

大阪生まれ河内育ちなんだけど
関東に住みついちゃったの
和装着付師だったりするの
エセモノカキやってたりするの
VBが得意だったりするの
SQL文が大好きだったりするの
囲碁修行中だったりするの
ボトゲ好きだったりするの
F#かわいいよF#

正体は会った人だけ知ってるの

空気読まなくてごめんなさいなの


わんくまリンク

C#, VB.NET 掲示板
C# VB.NET掲示板

わんくま同盟
わんくま同盟Blog


WindowsでGo言語
WindowsでGo言語


ネット活動


SNSは疲れました

記事カテゴリ

書庫

日記カテゴリ

ギャラリ

イベント活動

プログラムの活動

その発端
http://blogs.wankuma.com/esten/archive/2008/03/22/129035.aspx

FinalWars その1
http://blogs.wankuma.com/esten/archive/2008/04/09/132463.aspx

オブジェクトのブロッキングで絡まってしまったおろちの首。絡まらないで仕事をさせるには、ロックをはずせば良い。そう考えた胡桃の脳な私は、ちょこっと細工をした。

「インデックスの行ロック」「インデックスのページロック」

チェックボックスをOFF。インデックスを再構築して、再び、おろちで仕事。

でも、止まる。おんなじところで「ScanStart/End」。いや、こんどの場合は

利用状況の画面に「X」がさらに倍にどばーーっ!

最悪やん!

なんでやねーーんっ!

しかもトランザクションロックが動いて、ブロッキングも頻繁してえらいことに。で、このロック、運悪くTransactionScopeの中、はい、DTSががーーーーーっつり掴んでます。強制終了なんて効きやしねぇ。あー、もう、ど・う・に・も・とまらない~(号泣)SQLServer強制再起動(恐)
周りに謝りまくり、始末書もどき書き、ごめんなさい会議までやっちゃう羽目に(大汗)これで一日丸つぶれ(はぁと)

というわけで、これはやっちゃいけないことだったんだね。うん、反省。ちょっと色々といぢりすぎた。

で、気持ちを切り替えて、

別の方面からのアプローチ。問題のSQLの実行プランをだしてみた。実行プランは出てくる。「なーんだ、ちゃんと解析できてるやん」とホッとしたのもつかの間、ふと、比べていて気がついた。おろちとギドラ、同じレコード同じテーブル同じ件数同じデータでありながら、実行プランが微妙に違う。

ギドラはテーブル副問い合わせにはクラスタインデックスを使用せず、おろちはそこにクラスタインデックス。両方とも内部パラレル処理なのは同じなんだけど……

何で違うん?

ギドラとおろちはテーブル構成も一緒、作りも一緒。違うのはCPUの数、メモリの量。それに……それに、実行回数(爆死)で、はたと考えた。テスト機のギドラはそりゃもう、とんでもない回数をこなしてきた百戦錬磨の練りこまれた統計情報を持っている。一方おろちは、必要な時に必要な回数だけをピンポイントに実行してきた、いわば世間知らずな深窓のおぜうさまw

まさか、まさかぁ、ねぇ? なんて思いながら、蓋を開けてみた。

 統 計 情 報 それはパンドラの箱w

うそぉぉん。全然違う、チャウチャウチャウチャウ!ちゃいすぎぢゃっ! 回数をこなしたギドラがこのSQLを実行するときに選択する最適なプラン、クラスタインデックスを外のDで使い内側のSではあえて別のインデックス(まぁちょっと似てるようなやつなんだけど、スキーマバインディングしていたビューのクラスタインデックスw)を使っていた理由はそこかぁ!(意義ありぃっ!びしぃっ!画面が揺れるっってくらいの衝撃w)。恐らくはテストで張ったりはずしたりしたインデックスの情報さえも残すのかナンなのか、ギドラはすでにロックをうまくすりぬける方法をマスターしていてそれを駆使して仕事をする荒業をやってのけていた。さすがギドラだぜ!空間移動で光りながら現れてピロピロピロだぜ(昭和)!地球守護聖獣千年龍王だぜ(平成)!引力光線吐きまくって空飛ぶぜ(共通w)!

さらに、その観点から再度両方のProfilerログを見直して、ふと、気づいた。ブロッキングが頻発していたおろちの自動統計情報更新処理はトランザクションロックタイムアウトでことごとくダウンして中断。バックグラウンド処理のために、誰にも注意を払われていなかったのだけれど、これがジワジワと効いていて、気がつけば必要な情報がろくに揃ってない状態になっていたらしい。そんな中で、それでも首はある限りの情報で仕事を計画し、働こうとして、でもやっぱり首が絡まって……かわいそう、なんてかわいそうなんだ!やまだのおろち!

ひととおり同情したら、基本に立ち返ろう。

問題のSQLにある外SQLのDと副問いSQLのS、ギドラがやろうとしたように異なるインデックスで実行すれば、おろちでも早くなれるはず。確かにSQLServerは与えられているリソースの中で最適な方法で処理をしようとプランをつくるけど、しょせんはロジック、機械、AIじゃない。人間がなんとかしようとしなければ、結局はハコの中で動作する0と1の電気信号にすぎないと思うの。そこをキチンと把握して、うまく仕事ができるようにしてやらないとイカンのではない?

というわけで

  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も再起動!で、まっさら?なギドラとおろち、再び、ゴング!

ギドラで実行→5分42秒、うん、なんか、心持ち早い(笑)
おろちで実行→1分53秒、うおー!すげーーーっ!はええぇぇっっ!

というわけで、おろち、復活。つか、前より早くなってねぇかい?

あらためて、ProFilerとパフォーマンスモニターでチェックしてみたところ、新しいインデックスが参戦したことで統計情報が一新されたらしくHOGE1の処理をしていた他のSQLまでもが高速化(笑)統計情報更新もロックタイムアウトすることなく全ての処理を美しく完了。ブラボー♪それってなんて素敵だし爽やかなチューニング!!(自画自賛!)

つまりはだ、おろちでは

副問い合わせと本体が同一テーブルのUPDATEを行おうとしたときに統計情報の不足から同一のテーブルしかも同一のインデックスを使って処理を行うプランが作成され、それを実行しようとして自分で自分の使いたいリソースをロックして自分で自分をロック解除待ちにしてしまい、それによってその接続プールのトランザクションがロック状態。マルチスレッド上の別のSQL実行もそのトランザクション処理を待ってしまうために統計情報自動更新の処理さえもトランザクションタイムアウトを発生して全体が鈍足化してしまっていた。CPUとメモリが豊富なマシンでは同時に走る事のできるスレッドの数もふえるため、少ないマシンに比べてロック待ちとなる処理も増える。

ということだったんだな。おろち、てめぇ、らめぇ(笑)

やらなくちゃいけなかったこと

同一テーブルへの処理であっても、副問い合わせを使うということは別テーブルをメモリというか一時テーブルに展開するのと同意。なので、本体処理と別のインデックスを提供もしくはスキーマバインドビューをつかって競合を避けさせる必要がある。その処理が複数CPUによるパラレル処理を実行プランに持つ場合、自身のリソース同士のブロッキングを引き起こしやすい状況になることも考慮すること。そして、サーバーでの統計情報更新、インデックスの再構築、データベースファイルおよびトランザクションログファイルの適切なメンテナンスはデータベースサイズが大きくなり、かつ使用するCPUとメモリが多いほど不可欠な作業、怠ると劣化鈍足化の原因になる。

小回りなギドラにくらべ、おろちはずっと手のかかる構ってチャンだったわけだね。だからこそ、きちんと手をかけてやればそれだけの働きもするってことで(笑)
まぁ、これで、おろちはギドラに勝った……んだけどさ。おろちの追加インデックス作成の申請を通せるのかどうかは、大人の事情だな。でもね、一つ、すごいこと。この実行テストの後に追加したインデックスを削除したとしても、それ以降ロックを起こした実行プランを使うことは全く起きなかった、どころか、回数を重ねれば重ねるほど高速化して、最後には1分10秒台に。ギドラで57分掛かっていたデータ処理も最終的には16分で処理するところまでやりきれた。なんだかもう、カイザーギドラおろちモードってカンジだったね、ラストは(謎)プランキャッシュとリコンパイルを徹底的に意識したSQL文を作って処理させたというのはあるにせよ、それにしても統計情報の蓄積とプランキャッシュの有効活用によって起きる効果ってのはホント絶大なんやねぇ。それゆえに、うまくいかないとボロボロにもなるわけだがw うん、良い経験をした、ってことにしておこう(笑)

 

今回、読みまくって、脳みそを胡桃味噌に発酵させてくれたモノたち

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

インデックスを作って使う時のツボ系w
http://www.sql-server-performance.com/tips/index_main.aspx

パフォーマンスチューニングのツボ。とりあえずひととおりw
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

いぢょ♪

投稿日時 : 2008年4月10日 20:04

コメント

# re: やまたのおろちvsキングギドラ FinalWars その2 2008/04/10 20:23 片桐
そうそう、追記。
ことの発端はINSERT句が遅い。だったんだけど、どんどん調べると、それ以前に、ブロックて停止するUPDATEがいることがいて、そいつのところがもっとネックってわかったっていう話があるの。問題となっているのは全てHOGE1テーブルってのがポイントだったのかもんない。
今回の件で、INSERT句もなぜか解決w


# re: やまたのおろちvsキングギドラ FinalWars その2 2008/04/10 20:44 黒龍
勉強になりました!!
が、ちょっとはてなが。スキーマバインディングされたビューとインデックスではどっちがいいんだろう??
他の用途もかねるならインデックスなんだろうけどなぁ。

# re: やまたのおろちvsキングギドラ FinalWars その2 2008/04/11 1:14 Streetw☆
お疲れさまでした!!読むだけでも疲れましたw
そっか。統計情報の存在を忘れてました。
つまりはだ、のところで私もロック状態になったので、
今回出てきた単語について情報収集したいと思います。
やまださんも良かったですね♪

# re: やまたのおろちvsキングギドラ FinalWars その2 2008/04/11 6:39 はつね
おつかれさまでした~。
やっと首が別々に動くようになったのね。

> 追加したインデックスを削除したとしても、

これ実行プランで使っていないインデックスって事ですよね。


あともしかしたらAUTO_UPDATE_STATISTICS_ASYNCはOffのまま?(統計情報がブロッキングされてたってことは?)

# [SQL Server] おろち vs ギドラ 2008/04/11 12:59 biac の それさえもおそらくは幸せな日々@nifty
技術的には、 たぶんきっとよくある話なんだけど、 文章が素晴らしい (^^;その2 は、 とくに笑わせていただきました。 f(^^; すいません、VB4しかやってないんです、VBAはやったけど(ぼそ) より。・ やまだのおろちvsキングギドラ FinalWars その1・ やまたのおろちvsキングギドラ FinalWa

Post Feedback

タイトル
名前
Url:
コメント