none
同じ ストアドプロシージャが だんだん 遅くなる RRS feed

  • 質問

  • 同じストアドプロシージャを実行すると、はじめの2回ぐらいは数分で結果が返ってくるのですが、3度目以降だんだん遅くなり、2時間以上かかります。

    そのストアドは、tempdb領域のファイルを最初に削除し、その後Tempファイルを20個ぐらい作成するものです。

    環境は、Windows7 professional SP1、CPUはIntel(R)Core(TM)i3-2100 3.1GHzで、メモリは4GBです。それに2012SQLServer評価版を入れています。

    データ件数を減らしてみたり、ログファイルの大きさを大きくしてみたりしてみたのですが、変わらずです。

    評価版やEXPRESS版を新しく入れなおすと最初の2回ぐらいは反応が早いのです。

    temp領域に何か残っているのでしょうか?どこかにごみがたまっていっているのでしょうか?

    チューニングの仕方で何か変わるのでしょうか?また、その方法はどのようにしたら良いのでしょうか?


    NTT Medias System Plan group   Hiroko Tahara

    2012年7月5日 6:07

すべての返信

  • >そのストアドは、tempdb領域のファイルを最初に削除し、その後Tempファイルを20個ぐらい作成するものです。

    とのことですが、tempdb のデータファイル(mdf/ndf) を削除 / 追加しているのでしょうか?
    それとも一時テーブルを作成されているのでしょうか?

    公開が可能な範囲でストアドの内容を書かれると他の方もご意見が出しやすいと思います。

    データファイルの追加 / 削除を行っている場合ブロッキング (ロックの競合) が発生している可能性がありますので、ストアドを実行し、時間がかかっているときに利用状況モニターや SQL Server プロファイラのようなツールを使用してブロッキングの情報を取得してみるとよいかもしれません。

    利用状況モニターは Management Studio を起動して、サーバー名を右クリックすると表示することができます。
    プロセスの欄を見ると、実行されている処理とブロッキングが発生しているかを確認することができますのでロック競合が発生しているかを調査できるかと思います。

    利用状況モニターを開く方法 (SQL Server Management Studio)

    ご参考になれば幸いです。

    2012年7月8日 23:24
    モデレータ
  • 返信が遅くなって申し訳ありません。

    データファイルではなく、一時テーブルを作成しています。

    調子の悪いときには、#xxxxx________________________________________________________________________________________________________000000000008

    というようなファイルがtempdb領域に7つほど作成されました。

    結局、データベースファイルを削除して新たに作成しなおし、データをインポートしなおすと早くなるのですが、2,3回まで、だんだん遅くなります。

    早くなるというのも、確実なことではなく、根本的な原因と対処法がなく困っております。

    何か良いアイディアはないでしょうか?


    NTT Medias System Plan group   Hiroko Tahara

    2012年7月11日 1:32
  • tempdbに対して更新するデータ量がどの程度かわからないのでなんとも言えませんが、ファイルのサイズ増分のオーバーヘッドで応答時間が遅くなっている可能性はないでしょうか。

    この場合、ディスクドライブの空き容量にもよりますが、ファイルサイズの初期値、増分値を大きくするという対処が考えられます。


    • 編集済み tenmanten 2012年7月11日 7:49
    2012年7月11日 7:39
  • ご返信ありがとうございます。ファイルの初期値は大きくしました。勉強不足ですみませんが、増分値の変更の仕方を教えていただけませんか?

    ファイルの初期値はプロパティのファイルタブですぐに変更できたのですが、増分値は変更できません。

    ご教示よろしくお願いいたします。


    NTT Medias System Plan group   Hiroko Tahara

    2012年7月12日 2:48
  • >ファイルの初期値はプロパティのファイルタブですぐに変更できたのですが、増分値は変更できません。

    ↑↑↑

    おそらくManagementStudioで変更されたのだと推測されますが、ManagementStudioのファイルタブの初期サイズの右となりに自動拡張の欄があります。その右端のボタンを押下すると自動拡張サイズの変更ダイアログが表示されます。

    また、以下のようにalter databaseコマンドを実行することにより、サイズを変更する方法もあります。(http://msdn.microsoft.com/ja-jp/library/bb522469.aspx より抜粋)

    USE master;
    GO
    ALTER DATABASE AdventureWorks2012 
    MODIFY FILE
        (NAME = test1dat3,
        SIZE = 20MB);
    GO
    


    2012年7月12日 3:02
  • いつも丁寧に教えていただいてありがとうございます。

    試してみます。 


    NTT Medias System Plan group   Hiroko Tahara

    2012年7月12日 4:41
  • 返信が遅くなって申し訳ありません。

    データファイルではなく、一時テーブルを作成しています。

    調子の悪いときには、#xxxxx________________________________________________________________________________________________________000000000008

    というようなファイルがtempdb領域に7つほど作成されました。

    結局、データベースファイルを削除して新たに作成しなおし、データをインポートしなおすと早くなるのですが、2,3回まで、だんだん遅くなります。

    早くなるというのも、確実なことではなく、根本的な原因と対処法がなく困っております。

    何か良いアイディアはないでしょうか?


    NTT Medias System Plan group   Hiroko Tahara

    しばらく時間が経ってしまったので、自己解決されているかもしれませんが、このトピックの投稿を見直して1つ思ったことがあります。
    作成された一時テーブルが残り続けているということはないでしょうか。そうであれば、不要なセッション、タスクが残り続けていて、tempdbアクセス時の負荷を上げている可能性があります。プロシージャの処理内容や起動トリガがわからないのでなんとも言えませんが、

    ①予期せずプロシージャの実行セッションが残り続けていないか

    ②グローバル一時テーブルの場合、予期しない別タスクが一時テーブルを参照していないか

    などを見直してみるとよいかもしれません。

    ===================================

    CREATE TABLE (Transact-SQL)(http://msdn.microsoft.com/ja-jp/library/ms174979.aspx)より抜粋

    一時テーブルは、DROP TABLE を使用して明示的に削除される場合を除き、有効範囲外になったときに自動的に削除されます。

    • ストアド プロシージャで作成されたローカル一時テーブルは、ストアド プロシージャが終了すると自動的に削除されます。 テーブルは、そのテーブルを作成したストアド プロシージャによって実行される任意の入れ子になったストアド プロシージャから参照できます。 テーブルは、そのテーブルを作成したストアド プロシージャを呼び出したプロセスから参照することはできません。

    • その他すべてのローカル一時テーブルは、現在のセッションの終了時に自動的に削除されます。

    • グローバル一時テーブルは、テーブルを作成したセッションが終了し、その他すべてのタスクがテーブルの参照をやめたときに自動的に削除されます。 タスクとテーブルの間の関連付けは、1 つの Transact-SQL ステートメントが存続する間のみ維持されます。 したがって、グローバル一時テーブルは、テーブルを作成したセッションが終了したときに、テーブルを能動的に参照していた最後の Transact-SQL ステートメントが完了したときに削除されます。

    ===================================

    • 回答の候補に設定 山本春海 2012年8月7日 8:30
    2012年7月27日 5:09
  • いつも、返信ありがとうございます。

    作成された一時テーブルが残り続けているということはありません。ストアアドの最初に明示的に削除してしまっています。

    いつも、時間がかかるのは、テーブルが作成されるまでに一番時間がかかっており、作成された後、しばらくするとストアドが完了します。

    関係ないのかもしれませんが、テンポラリ領域でない、元のDBを削除し、作成しなおすと少しは、早くなるような気がします。


    Hiroko Tahara

    2012年8月9日 5:16
  • いつも、返信ありがとうございます。

    作成された一時テーブルが残り続けているということはありません。ストアアドの最初に明示的に削除してしまっています。

    いつも、時間がかかるのは、テーブルが作成されるまでに一番時間がかかっており、作成された後、しばらくするとストアドが完了します。

    関係ないのかもしれませんが、テンポラリ領域でない、元のDBを削除し、作成しなおすと少しは、早くなるような気がします。


    Hiroko Tahara

    一時テーブルなんですよね?
    ストアドの”最初”に明示的に削除しているのですか?
    ※最後ではなくて・・・

    2012年8月9日 22:52
  • いつも、ありがとうございます。日にちがあいてすみません。

    そうなんです。一時テーブルなんですが、何度も続けてテストするためだと思われますが、最初に明示的に削除しています。


    Hiroko Tahara

    2012年8月21日 0:10
  • ちなみに削除を最初に行っている理由ってなんでしょうか?

    一時テーブル(テンポラリテーブル)であれば、セッションが切れたら無くなるはずですし・・・

    削除ってDROPですか?DELETEですか?
    ※DELETEの発行に時間がかかっているとかってオチはじゃないですよね。

    2012年8月21日 1:00
  • お返事ありがとうございます。

    たぶん、デバッグ用に作成された名残りで、実行されているのだと思います。

    ちなみに削除は、DROPです。


    Hiroko Tahara

    2012年8月22日 7:28