SQL Server上の一つの処理が急に時間を費やすようになった（以前まで表示されていなかったメッセージが現れている）

Question

助けてください．．．．

環境：SQLServer V.14 / Windows Server 2016 Standard

昨日までSQL Serverのエージェント上のジョブで実行していた処理が急に多大な時間を費やすようになりました。

ジョブの中では　あまり世の中で知見が得られないIntegrationServicesパッケージの起動が行われています。１つのタスクです。（ちなみにパッケージは　OracleDatabase上のあるビュについてADO.NET接続で　SQLServer側にデータ取得してくる内容です）

IntegrationServicesのパッケージは　実行状況をManagementStudio上で確認することができるのですが　その一部について　昨日までの状況と、今朝多大な時間を費やした状況を　以下に示しました。

SQL Server側の問題か　OracleDatabase側の問題かも　現在分からない状態で　本当に困っています。

昨日までの正常処理

今朝から時間を費やすようになった状況

上記で　青枠で囲んだメッセージに由来し時間を費やしているものと　推察していますが、どういった対策が考えられるでしょうか？　
一応に処理は終えます、昨日3分弱が2時間くらいかかるようになりましたが...

Answer 1

先頭のログにある経過時間というのが、そのタスクにおける所要時間という事なら、扱う在庫データ数が多くなったという事ではないでしょうか。

扱うデータ量が多い場合に、スワップアウトが発生しその分遅くなっているという事もあるでしょうが、
遅くなった時間中にスワップアウトがどの程度の割合を占めているかは分かりませんので、直接的な原因ではないかもしれません。

改善の手順としては、
・メモリの割り当てを増やす。
・一度に大量のデータを処理しないように、扱うデータを分割して処理する。
といった所でしょうか。

Comments

[saya24]

saziさん、いつもお世話になっております。
どちらのサーバ（SQLServer/OracleDatabase）について　申されているか　教えて頂けますか？

Oracle側のビュのデータ取得を　SQLServerから　対応しております。

[sazi]

ログだけの情報なので、どんな処理を行っているのか分からないですから、お答えは出来ません。
メモリーに関するログはSQLSERVER側のものだと思いますから、SQLSERVER側に原因があると思いますけどね。
仮に、ORACLEのビューが、データが増えてもあまり時間を掛けないようなチューニングが出来たとしても、扱うデータの件数によるメモリ不足なら、SQLSERVER側の処理そのものを変更しないと駄目かもしれませんし。

[saya24]

saziさん　ありがとうございます。無茶なことを聞いてすみません。

どのみち　ビュ（Oracle）はベンダ様提供の基幹システムのものなので、そちらに対処できなることは考え難く　できる限りこちらSQLServer側で　頑張るしかないかなぁ.....

[saya24]

ご報告になります。SQLServerの運用状況をManagementStudioは　利用状況モニタで確認することができますが、ず～っとプロセスにあがり続けいているセッションが見受けられたので、強制終了したら、なんと話題にしている処理は　以前同様の処理時間に戻りました。
稼働状況をよく精査した上で　QAに掲載すべきでした、皆様の貴重なお時間を頂いてしまい申し訳ございませんでした。
で、強制終了したプロセスですが　ACCESSからのODBC接続によるSQL文と捉えています。利用者がACCESSを強引に閉じたか、何か？？を疑っています。

今朝、当該人に話をうかがったのですが　記憶なし　との返答でしたが....

[sazi]

Accessのリンクテーブルなどを直接編集している状態（鉛筆マーク）の状態の場合、データがロックされた状態になりますので、後続のプログラムがそのロックによって待たされているような状況はあるかもしれません。
Accessの画面操作などで、直接リンクテーブルを更新するような作りになっているようでしたら、見直しされた方が良いかと思います。

[saya24]

内容了解しました、ありがとうございます。
今回、話題にしている処理は　SQLServerからOracleDatabase上のデータ取得であり、ACCESSで接続されるSQLServer上のデータベースとは一切関係がないと思われるのですが、メモリ圧迫に起因していた可能性は否定できませんね....

と、いうのは　他の処理のブロックになっていた可能性をを考えれば　SQLServer全体に影響しますよねぇ？？

[sazi]

> 強制終了したプロセスですが　ACCESSからのODBC接続によるSQL文と捉えています。
それで改善したのなら、無関係じゃないとしか言いようが無いですけど。

取り敢えず解決したなら、質問は閉じた方が良いと思います。
進展したとしても、それは別な内容の質問になるでしょうから。

Answer 2

単純にコンピューターのメモリ不足のように見えます。
今まで一気に処理できたものが小出しに処理せざるを得ない状況になったという感じですかね。

警告文を読む限り手っ取り早い改善方法はメモリーの増設になるかと思います。

あとはあまりメモリを使いすぎないように処理をチューニングしたり、
データベースサーバーが他の役割をしてメモリを使っていたらいたら
そういうのを仕分けることになるかと。

チューニングはやろうと思えば結構骨が折れる作業になりますね～。

Comments

[saya24]

sousukeさん、ご見解をありがとうございます。不安な心境のなかコメント頂けて本当にうれしかったです。もしお分かりになれば　引き続きご見解ください。
これはSQLServer側の問題でしょうか、OracleDatabase側の問題に受けて取れますか？？
昨日まで大丈夫だったのに、急に今回のような事象って　発生してしまうもののでしょうか．．．．

[sousuke]

おそらくSQL Server側の問題でしょうね。発生するしないでいえば『相当慎重に開発して頻度が下がる程度』に思っておいたほうがいいですね。私はOracleDatabaseというのがわからないのであれですが。

他のやり取りを見る限り引き出し側のSQLを変えるのは難しい？ように見えます。
なので今はどうかわかりませんがSQL Serverはメモリの割り当てが結構適当だったので
「データベースが使用できるメモリの上限」を設定してみるなどやれるところからやる感じですかね。

[saya24]

ご見解ありがとうございます。
SQLServerのIntegrationServicesのパッケージ（ADO.NET）による
サーバ跨ぎのSQL発行って　どちらのメモリ使っているんだろう・どちらでSQL発行されているんだろう、というのが　そもそも私　分かっていないです！！　だから　すぐさまSQLの見直しに手を出してよいものか　考えていたんです。
手っ取り早く　メモリ増設の手立て・確認へ移りたいところですが.....仮想環境ゆえ　下からの割り当てとか　色々　障壁が....（もう余裕ないとか）

[saya24]

saziさん向けの返答覧にも記載しましたが、話題にしている処理が　本日から以前同様の処理時間に戻りました。稼働状況をよく精査した上でQAに掲載すべきでした、貴重なお時間を頂いてしまい申し訳ございませんでした。
ManagementStudioで、不用意に残っていると思われたプロセスを強制終了したことで事態回復です。
（ACCESSからのODBC接続によるSQL文。利用者がACCESSを強引に閉じたか？？）

再発防止策は　どうしようかな....