none
SQLServer2005上でのストアド実行について(応答が遅い) RRS feed

  • 質問

  • こんにちは。

     

    以前はお世話になりました。また質問させていただきます。

     

    旧サーバ OS WindowsServer2000 SP4

           DB SQLServer2000 Standard Edition SP4

    から


    新サーバ OS WindowsServer2003 SP2(64-bit)

           DB SQLServer2005 Standard Edition SP2 (64-bit)

     

    へサーバを移行しようとしています。

     

    旧サーバーのDBをデタッチしてファイルを新サーバにコピー後アタッチすることでデータを移行し、
    現在動いているアプリが新サーバーで動くか試したところ、ストアドの返答が遅くなる現象が起きました。

     

    デバックモードでアプリを実行し、ストアドを実行したら、SQL Server Profiler上ではストアドの実行が
    終わっているにもかかわらず、アプリに戻ってきたのはそれから約5分後のことでした。

     

    またストアド実行の後に、ADOを利用して、あるテーブルから1レコード取得するクエリを実行するのですが
    実行後5分以上経っても戻ってきませんでした。

     

    動きは以下の通りです


    トランザクション開始

      (コード)

      Cn.BeginTrans                (CnはDB接続変数)

    ストアド実行
      (コード)
      Set wCmd = New ADODB.Command     (ADO利用)
      wCmd.ActiveConnection = Cn      (CnはDB接続変数)
        wCmd.CommandText = StrSt               (StrStはストアド関数Call文)
        wCmd.Execute
      Set wCmd = Nothing

    クエリ実行
        (コード)
        Set Rec = New ADODB.Recordset  (ADO利用)
      Str=select * FROM  A  WITH (NOLOCK) where retu='a'
        Rec.Open Str, Cn, adOpenKeyset, adLockReadOnly

    (中略)

    トランザクション終了

      (コード)

      Cn.CommitTrans            (CnはDB接続変数)


    クエリ実行時、Microsoft SQL Server Management Studio Expressで同じSQL文を実行するとデータを取得します。

    利用状況モニタをみてもデッドロックは起きていません。

     

    全てのストアド同じ現象が起きるわけではなく、あるストアドだけで起きます。
    (ストアド実行で、問題のストアドの代わりに別のストアドを実行するとSQL Server Profiler上でストアドの実行が終わった後すぐにアプリに戻り、またクエリ実行もすぐデータを返してきました)

     

    問題のストアドが他のストアドと違うのは、ローカルの一時テーブルを使っていること、カーソルをグローバル宣言していることです。

     

    このような現象が起きる原因として何が考えられるか、どなかたわかる方がいらっしゃいましたら教えてください。

     

    開発ツールはVisualBasic6.0 EnterpriseEdition です。

    よろしくお願いします。
    2008年11月18日 4:52

回答

  • こんにちは、naginoです。

     

    直接の回答ではありませんが、デタッチ・アタッチで DB のアップグレードを行った場合は、DBCC UPDATEUSAGE を実行することをお勧めします。

    詳細は以下のページの手順4 をご参照ください。

    http://msdn.microsoft.com/ja-jp/library/ms189625(SQL.90).aspx

     

    なお、一時テーブルは tempdb を使用しますので、旧サーバと新サーバで tempdb のファイルグループの構造やディスク構成が異なるなど設計に差異がある場合は、パフォーマンスに差がでることが考えられます。

    こちらについては、旧サーバと新サーバとで tempdb の設計を比較してください。

     

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

    2008年11月18日 6:03
  • 問題のストアドプロシージャをsp_recompile再コンパイルしてみてはどうでしょうか?

     

    これでも解決しない場合、SQL Server プロファイラを使ってどこがボトルネックになっているか調べてみてはどうでしょうか?

    使用するテンプレートを「TSQL_SPs」を選択する事によって、ストアドプロシージャの中身までトレースします。

    これで処理に時間が掛かっている部分(Duration)が解ると思いますので、それから原因を追ってみてはどうでしょうか?

     

    ※バージョンが変わる事によって、クエリプラン等が変わるので、逆に遅くなる場合もあるようです。

     

    2008年11月20日 1:43
  • 投稿後に CatTail様の返信を確認しまして、編集・追記しています。

     

    CatTail様の手法のほうが手軽に確認できますので、まずはそちらのご確認をされることをお勧めします。

     

    ここまで追記

    ----------------------------------------

     

    こんにちは、naginoです。

     

    DBCC UPDATEUSAGE では状況変化無しとのこと、了解しました。

     

     

     

    パフォーマンス関連のチューニングや障害対応は、原因を詳細に分析してから根本対策を行う方法と、症状を見ながら対症療法で Try & Error を繰り返す方法とありますが、いずれにせよ環境依存となりますので、このような公式サポートではない掲示板では解決するかどうか確実ではない点、並びに要する時間が大きくなる点、あらかじめご承知おきください。

    お急ぎの場合、あるいは確実な解決が必要な場合は、有償サポートをご検討ください。

     

    とりあえず tempdb ですが、こちらは領域の自動拡張と切り捨てのオーバーヘッド、ファイルの分割による排他の回避、ディスクの RAID 構成ですとか、そういった箇所を念頭において記載しておりました。

    幸いドライブが別とのことですので、一旦保留で良いかと考えます。

    (パフォーマンスカウンタの Logical Disk の Queue Length などで負荷を測定できるため)

     

     

     

    以下は、さしあたって有償サポートを使用されない場合にご確認ください。

     

    それで、方策は様々あるためどうするか難しいのですが、とりあえず状況の整理からでしょうか。

    まず、今回問題となっているストアドプロシージャが以下のページの「カーソル」に記載があるケースに該当しないかご確認ください。

    http://msdn.microsoft.com/ja-jp/library/ms143359(SQL.90).aspx

    上記に限らず 2000 と 2005 は色々と実装が異なる点があるため、結果が変化する、パフォーマンスが変化するなど、様々な形で挙動が変化します。

    (今回のケースで現時点で思い当たるのは上記ですが、他にも可能性のある事項があるかもしれません)

     

    また、今回問題になっているストアドプロシージャを単体で実行した場合(例えば SQL Server Management Studio のクエリウィンドウで実行するなど)、どの程度の時間がかかりますでしょうか。

    そこで 5分程度かかっている場合、CPU、ディスク、メモリのいずれがボトルネックになっていますでしょうか。

    負荷の状況はシステムモニタで確認できます。

    http://msdn.microsoft.com/ja-jp/library/ms191246(SQL.90).aspx

    明確なボトルネックがある場合は、そこをヒントに原因を突き止められるかもしれません。

     

    あと、ユーザデータベースと tempdb それぞれの照合順序、サイズ、使用可能領域、互換性レベル、自動圧縮の設定はどのようになっていますでしょうか。

    それぞれ SQL Server Management Studio のオブジェクトエクスプローラ上で 「DB を右クリック」 > 「プロパティ」と操作していただくと開く、プロパティウィンドウの「全般」と「オプション」で確認することができます。

    ちょっと情報元を探しきれていないのですが、ユーザデータベースと tempdb とで照合順序が異なる場合、ソートの順序が異なるためクラスタ化インデックスのソート順が使えずにソートしなおすことなどがあり、パフォーマンスが劣化することがあったと思います。

     

     

     

    意外とストアドプロシージャの再コンパイルや、tempdb の拡張だけで解決しそうな気もしますが・・・少々しっくりこないところがあります。

     

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

    2008年11月20日 2:04
  •  ストアドプロシージャの問題ではなさそうですね。

     ストアドプロシージャを実行した後に、何か処理を行っているということは無いでしょうか?

    (Excelの話がありましたが、例えば結果をExcelにエクスポートしているとか)

     

    2008年11月26日 14:28
  •  可能性があるのではないか程度ですが、ストアドプロシージャやクエリ実行で大量にレコードが追加/更新/削除された結果、統計情報が自動で更新されて応答が戻ってこないと言うことは考えられないでしょうか?

     

     例えば、[統計の自動更新]をFalseにして手動でUPDATE STATISTICSを行ってみるとか、SQL Server 2005以降であれば、[データベースのプロパティ]で[統計の非同期的自動更新]をTrueにしてみるとか。

    (これらのオプションはT-SQLのALTER DATABASEで変更できます)

    ※[統計の非同期的自動更新]をTrueにすると、シングルユーザーモードで支障があるとの情報がありますので、シングルユーザーモードにする移行する場合は、移行する前にFalseにしてください。

     

     他に考えられる要因としては、ログファイルからデータファイルの書き込みに時間がかかるのでは? とか、壁ー時コレクションを行っているために応答が無いのでは? と思っていましたが、ログファイルからデータファイルの書き込みは非同期で行われますし、T-SQLでのストアドプロシージャではそもそもガベージコレクション自体発生しないし。

    (ちなみにSQLCLRでのストアドプロシージャではガベージコレクションが発生します)

     

     他に考えられる要因としてはtempdb絡みですが、tempdbのサイズには問題はなさそうですし…。

    (応答が無いときに、SQL Server Management Studioの[現在の利用状況を確認してみるとか)

     

     後、SQL Server Management Studioの[管理]-[SQL Serverログ]やイベントビューアに何かエラーや警告って出ていないでしょうか?

     

     何か取りとめがないですが、色々考えられる事柄を挙げてみました。

     ただ最初に書いたように、どの要因にも当てはまらないような気がしますが…。

     

    2008年11月27日 13:25

すべての返信

  • こんにちは、naginoです。

     

    直接の回答ではありませんが、デタッチ・アタッチで DB のアップグレードを行った場合は、DBCC UPDATEUSAGE を実行することをお勧めします。

    詳細は以下のページの手順4 をご参照ください。

    http://msdn.microsoft.com/ja-jp/library/ms189625(SQL.90).aspx

     

    なお、一時テーブルは tempdb を使用しますので、旧サーバと新サーバで tempdb のファイルグループの構造やディスク構成が異なるなど設計に差異がある場合は、パフォーマンスに差がでることが考えられます。

    こちらについては、旧サーバと新サーバとで tempdb の設計を比較してください。

     

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

    2008年11月18日 6:03
  • こんにちは。ひとまるです。

     

    nagino様、貴重なアドバイスをありがとうございます。

     

    DBCC UPDATEUSAGE 件は知らなかったので早速実行しました。

     

    また、tempdbは解説書等に初期配置位置から動かした方が良いと書いてあったので、OSとは別のドライブに

    データファイル、ログファイルを移動しました。(データファイル、ログファイルはそれぞれ別ドライブに置きました)

     

    以上を実行して再度試してみましたが、結果は変わりませんでした。

     

    また、何かお気づきの点がありましたら教えていただけませんでしょうか?

     

    よろしくお願いします。

    2008年11月20日 0:51
  • 問題のストアドプロシージャをsp_recompile再コンパイルしてみてはどうでしょうか?

     

    これでも解決しない場合、SQL Server プロファイラを使ってどこがボトルネックになっているか調べてみてはどうでしょうか?

    使用するテンプレートを「TSQL_SPs」を選択する事によって、ストアドプロシージャの中身までトレースします。

    これで処理に時間が掛かっている部分(Duration)が解ると思いますので、それから原因を追ってみてはどうでしょうか?

     

    ※バージョンが変わる事によって、クエリプラン等が変わるので、逆に遅くなる場合もあるようです。

     

    2008年11月20日 1:43
  • 投稿後に CatTail様の返信を確認しまして、編集・追記しています。

     

    CatTail様の手法のほうが手軽に確認できますので、まずはそちらのご確認をされることをお勧めします。

     

    ここまで追記

    ----------------------------------------

     

    こんにちは、naginoです。

     

    DBCC UPDATEUSAGE では状況変化無しとのこと、了解しました。

     

     

     

    パフォーマンス関連のチューニングや障害対応は、原因を詳細に分析してから根本対策を行う方法と、症状を見ながら対症療法で Try & Error を繰り返す方法とありますが、いずれにせよ環境依存となりますので、このような公式サポートではない掲示板では解決するかどうか確実ではない点、並びに要する時間が大きくなる点、あらかじめご承知おきください。

    お急ぎの場合、あるいは確実な解決が必要な場合は、有償サポートをご検討ください。

     

    とりあえず tempdb ですが、こちらは領域の自動拡張と切り捨てのオーバーヘッド、ファイルの分割による排他の回避、ディスクの RAID 構成ですとか、そういった箇所を念頭において記載しておりました。

    幸いドライブが別とのことですので、一旦保留で良いかと考えます。

    (パフォーマンスカウンタの Logical Disk の Queue Length などで負荷を測定できるため)

     

     

     

    以下は、さしあたって有償サポートを使用されない場合にご確認ください。

     

    それで、方策は様々あるためどうするか難しいのですが、とりあえず状況の整理からでしょうか。

    まず、今回問題となっているストアドプロシージャが以下のページの「カーソル」に記載があるケースに該当しないかご確認ください。

    http://msdn.microsoft.com/ja-jp/library/ms143359(SQL.90).aspx

    上記に限らず 2000 と 2005 は色々と実装が異なる点があるため、結果が変化する、パフォーマンスが変化するなど、様々な形で挙動が変化します。

    (今回のケースで現時点で思い当たるのは上記ですが、他にも可能性のある事項があるかもしれません)

     

    また、今回問題になっているストアドプロシージャを単体で実行した場合(例えば SQL Server Management Studio のクエリウィンドウで実行するなど)、どの程度の時間がかかりますでしょうか。

    そこで 5分程度かかっている場合、CPU、ディスク、メモリのいずれがボトルネックになっていますでしょうか。

    負荷の状況はシステムモニタで確認できます。

    http://msdn.microsoft.com/ja-jp/library/ms191246(SQL.90).aspx

    明確なボトルネックがある場合は、そこをヒントに原因を突き止められるかもしれません。

     

    あと、ユーザデータベースと tempdb それぞれの照合順序、サイズ、使用可能領域、互換性レベル、自動圧縮の設定はどのようになっていますでしょうか。

    それぞれ SQL Server Management Studio のオブジェクトエクスプローラ上で 「DB を右クリック」 > 「プロパティ」と操作していただくと開く、プロパティウィンドウの「全般」と「オプション」で確認することができます。

    ちょっと情報元を探しきれていないのですが、ユーザデータベースと tempdb とで照合順序が異なる場合、ソートの順序が異なるためクラスタ化インデックスのソート順が使えずにソートしなおすことなどがあり、パフォーマンスが劣化することがあったと思います。

     

     

     

    意外とストアドプロシージャの再コンパイルや、tempdb の拡張だけで解決しそうな気もしますが・・・少々しっくりこないところがあります。

     

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

    2008年11月20日 2:04
  • こんにちは。ひとまるです。

     

    CatTail 様、nagino様貴重なアドバイスありがとうございます。

     

    早速試してみたいのですが、別業務を先に処理しなければならず、試せるのは来週の水曜日(11/26)以降になると思います。

     

    ちなみにストアドプロシージャを単体での実行は以前行ったのですが、終了まで2分程度でした。

     

    後日結果を報告させていただきたいと思います。

    2008年11月21日 2:40
  • こんにちは。ひとまるです。

     

    CatTail 様、nagino様にアドバイスしていただいたことを実行してみました。

     

    sp_recompileを実行後、SQL Server Profilerでテンプレートを「TSQL_SPs」を選択、以前と同じようにアプリを
    デバックモードで実行しました。

    ストアド実行中、特に処理が遅いという部分はありませんでした。
    EventClass項目にSP:Completed、TextData項目に問題のストアド名が表示されてから約5分後にアプリに戻ってきました。

     

    カーソルですが、教えていただいたページを見ましたが、該当するものはありませんでした。

     

    データベースの各設定ですが、照合順序、サイズ、使用可能領域、互換性レベル、自動圧縮の設定は

    ユーザデータベース Japanese_CI_AS   18G   3G   SQLServer2000   False

    tempdb                Japanese_CI_AS   900M  800M   SQLServer2005    False

    となっています。

     

    他のストアドと違うところがもうひとつありました。

    ストアド実行前にDTS機能を使ってEXCELファイルの内容をテーブル(仮にテーブルAとします)に入れており、DTSはパッケージをVisualBasicのプログラムに変換したものを使用しています。(変換はSQLServer2000を使って行いました)

    テーブルAをdrop後createしてEXCELファイルの内容をテーブルAに入れる、という処理です。

    この処理の後にトランザクションを開始してテーブルAを参照して処理を行っています。

     

    最後の手段として有償サポートに尋ねることを考えていますが、もし、何かお気づきの点がありましたら教えていただければありがたいです。

     

    よろしくお願いします。

    2008年11月26日 7:31
  •  ストアドプロシージャの問題ではなさそうですね。

     ストアドプロシージャを実行した後に、何か処理を行っているということは無いでしょうか?

    (Excelの話がありましたが、例えば結果をExcelにエクスポートしているとか)

     

    2008年11月26日 14:28
  • こんにちは、naginoです。

     

    まず、以下で状況に相違ありませんでしょうか。

     

    VB6 のプログラム

    ADO 使用

    処理の流れは以下

      1. Excel からテーブルA へデータインポート

      2. ストアドプロシージャ実行

      3. クエリ実行

    ストアドプロシージャ単体は遅くない

    全体を通して実行するとストアドプロシージャ実行後 5分で処理全体が完了する

     

    その場合クエリの処理時間が気になりますので、お手すきの際にでもクエリのみを SQL Server Management Studio 等から実行してどの程度時間がかかるのか確認してみてください。

    2008年11月26日 23:09
  •  

    お世話になります。ひとまるです。

     

    CatTail 様、nagino様、早速の回答ありがとうございます。

     

    状況を改めて書かせていただくと、

     

    VB6 のプログラム

    ADO 使用

    処理の流れは以下のようになります

     

      1. Excel からテーブルA へデータインポート

      2. ストアドプロシージャ実行

     

     ストアドプロシージャ実行終了後約5分後

    (意図的に5分待ちではなくアプリに戻ってこない)

     

     3. クエリ実行

     5分以上経っても戻らない

     

    クエリ実行後にDTSを使用する処理はありません。

     

    ストアドプロシージャ単体は遅くありません。

     

    SQL Server Management Studioでクエリのみを実行したところ、0秒でクエリが終了しました。

     

    また何かお気づきになられましたらよろしくお願いします。

    2008年11月26日 23:58
  •  可能性があるのではないか程度ですが、ストアドプロシージャやクエリ実行で大量にレコードが追加/更新/削除された結果、統計情報が自動で更新されて応答が戻ってこないと言うことは考えられないでしょうか?

     

     例えば、[統計の自動更新]をFalseにして手動でUPDATE STATISTICSを行ってみるとか、SQL Server 2005以降であれば、[データベースのプロパティ]で[統計の非同期的自動更新]をTrueにしてみるとか。

    (これらのオプションはT-SQLのALTER DATABASEで変更できます)

    ※[統計の非同期的自動更新]をTrueにすると、シングルユーザーモードで支障があるとの情報がありますので、シングルユーザーモードにする移行する場合は、移行する前にFalseにしてください。

     

     他に考えられる要因としては、ログファイルからデータファイルの書き込みに時間がかかるのでは? とか、壁ー時コレクションを行っているために応答が無いのでは? と思っていましたが、ログファイルからデータファイルの書き込みは非同期で行われますし、T-SQLでのストアドプロシージャではそもそもガベージコレクション自体発生しないし。

    (ちなみにSQLCLRでのストアドプロシージャではガベージコレクションが発生します)

     

     他に考えられる要因としてはtempdb絡みですが、tempdbのサイズには問題はなさそうですし…。

    (応答が無いときに、SQL Server Management Studioの[現在の利用状況を確認してみるとか)

     

     後、SQL Server Management Studioの[管理]-[SQL Serverログ]やイベントビューアに何かエラーや警告って出ていないでしょうか?

     

     何か取りとめがないですが、色々考えられる事柄を挙げてみました。

     ただ最初に書いたように、どの要因にも当てはまらないような気がしますが…。

     

    2008年11月27日 13:25
  • こんにちは。ひとまるです。

     

    CatTail様、アドバイスありがとうございました。

     

    統計の自動更新をFalse、統計の非同期的自動更新をTrueにして実行しましたが、同じ現象が起きました。

     

    vb6のプログラムを.NETに書き換えてストアドプロシージャを呼ぶようにするか、

    ストアドプロシージャをvb6のプログラムに書き換えて対処してみようと思います。

     

    いろいろご助言いただき、ありがとうございました。

    2008年11月28日 9:31
  • こんにちは、フォーラムオペレータ 大久保です。

     

    本スレッドですが、Visual Basic フォーラム の下記スレッドへ続いていますので、

     

    SQLServer2005上でのストアド実行について(応答が遅い)

    http://forums.microsoft.com/MSDN-JA/ShowPost.aspx?PostID=4190956&SiteID=7

     

     

    ひとまず、アドバイスをくださった皆様の投稿に「回答済み」をつけさせていただきますね。

    本スレッドにご興味をもたれた方、上記 ↑ URL をご参照ください。

     

    2008年12月2日 4:40