やまだのおろちのスペック
CPU８個
メモリ32G
キングギドラのスペック
CPU２個
メモリ４G
それ以外は全部一緒。OSも設定もほとんど一緒。まぁ本番擬似サーバーと開発サーバーだから、似ててないと困るんだけど、色々と。
そもそもの話、キングギドラで６分の処理が、おろちで30分近くかかること、から全ての話は始まった。
SQLServer2005でのモニター
使うもの。
パフォーマンスモニタ
SQLServer2005 ProFiler と 利用状況画面
おろちとギドラのそれぞれで、同じログ情報を取得します。
パフォーマンスモニタで監視するもの
Memory\Available MBytes
Memory\Cache Faults/sec
Memory\Page Reads/sec
Memory\Pages/sec
Memory\Pool Nonpaged Bytes
PhysicalDisk(_Total)\Avg. Disk Queue Length
PhysicalDisk(_Total)\Avg. Disk Read Queue Length
PhysicalDisk(_Total)\Avg. Disk sec/Read
PhysicalDisk(_Total)\Avg. Disk sec/Transfer
PhysicalDisk(_Total)\Avg. Disk Write Queue Length
Process(_Total)\Private Bytes
Process(_Total)\Working Set
Processor(_Total)\% Privileged Time
Processor(_Total)\% Processor Time
Server\Pool Nonpaged Peak
Server\Pool Paged Failures
System\Context Switches/sec
System\Processor Queue Length
SQLServer2005 ProFilerで監視するもの
SQL:StmtStart/Completed
SQL:BatchStart/Completed
SP:StmtStart/Completed
Data File Auto Grow/Shrink
Log File Auto Grow/Shrink
Lock:Cancel/Deadlock/Timeout
Degree of Parallelism
Server Memory Change
Scan Started/Stoped
まぁ、こんなもん。で、実際にとってみたわけですよ。
ちなみに、SQLServerでのSQL構文実行プロセスは
●SQLServerのSQL実行動作
SQL構文の解析
コンパイル有無の判断
実行プランの策定
SQL構文実行
取得結果展開
おおざっぱにこんなカンジ。なんで、これらの動きの時に、メモリやCPUがどう動き、接続プールがどうなっていてSQLServer内部で何が起きているのかをとりあえず記録にとるってことからとりあえず。
で、実際にギドラとおろちで動かした結果。やっぱおろち、遅い、つか、んんん？止まってないか？こいつ？
SQLServerには利用状況を表示できる画面があるのだけれど、そこには、この問題のSQLを実行しようとしているプロセスがいる。こいつが延々とRunnableとSuspendを繰り返していたのだけれど、ProfilerにはそのSQLのSQLｓｔｍｔSTARTが表示されていない。さらにProFiler画面をよく見てみると、ScanStarted/Stopedを延々と繰り返していて無限ループっぽい動き。んでもって、もつっと利用状況画面から問題のプロセスをあけてみると、件のプロセスではSch-SとXがどばーーっ！
つまり、確かにSch-S（SchemaScan）してるんだよね。ProFilerの動きとあってる。SQLServerにしてみたら、実行プラン作るために必要な情報をScanしてんのにうまく情報を取れなくて、諦めないでひたすら仕事。探し物はナンですか～♪と見てみると、微妙にロック要求（Ｘ）が出てる、そのオブジェクト。つまり、自分で自分にロックして自分で自分を探してる、それなんて自虐？。クラスタインデックスオブジェクトでガッツリロック掛かってんじゃん、インデックスよめねーよ！おろちの首、絡まってます～（笑）。そのせいで恐ろしく時間がかかっていたわけだね。その他はまぁらくちん♪ってカンジで動いてくれてたのに。
で、その止まっているSQL
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
みたいな、自テーブルのある条件で連番したレコードの番号で自テーブルのIDをセットする、みたいな自虐なUPDATESQLだったのさ。該当レコードは数万ってとこかな。クラスタインデックスはA1,A2,A3。うん、勘の良い人っつか、某パパさんとかにはこの辺りでもうすでにネタばれだと思うんだ。
でもね、当時の私はここで、んじゃ、このオブジェクトロックはずせば良いんじゃね？としか考えなかったわけ（笑）＜単純な胡桃脳ｗ
ああ、地獄のはじまりだったさ（笑）