none
同一のSQLを投げているのに、処理に掛かる時間が異なる。 RRS feed

  • 質問

  • Silverlight 3.0、SQL Server 2008でWebシステムの開発をしています。
    サーバーはWindows Server 2008、クライアントはWindows 7です。

    SilverlightのプログラムからWebサービス(C#2008)を呼び出して
    あるSQL(SELECT文)を実行しているのですが、タイムアウトエラーが発生してしまいます。

    そこでSQL Server Profilerを使い、SQLの監視をしたところ、
    CPU:29843、Reads:2124516、Duration:30000という結果が得られました。

    このときに実行しているSQLをSQL Server Management Studioから実行してみると、
    CPU:125、Reads:16438、Duration:242という結果となりました。

    WebサービスからSQLを実行した場合には、10回に9回くらいの頻度でタイムアウトが発生します。
    しかしSQL Server Management Studioから実行した場合には
    タイムアウトが発生することはないです。

    SELECT文の結果は、列数11、行数379です。SQLの取得結果は多くありません。
    サブクエリは使っていますが、さほど複雑なSQLだとは思っていません。

    Webサービスで実行しているSQLはこれだけで、
    同一トランザクション内で他のSQLが実行されているということは
    ありません。

    原因の調査を続けているのですが、なかなか解決することができません。
    何か原因として考えられることはございますでしょうか?

    アドバイスいただけましたら、とても嬉しいです。
    宜しくお願い致します。

    2010年10月13日 14:45

回答

  • SSMSのときとWEBからの時のプランを比較されたでしょうか?
    プロファイラであればShowplan AllやShowplan statistics profile等を取得すればわかります。
    もしプランが違うのだとすると、WEBからのクエリとSSMSからのクエリは違うものとして
    認識され、その結果片方が遅い結果になっているということです。
    なおWHERE句の条件指定を行う時に直接文字列で渡す時とパラメータで渡す時では
    結果同じ値で検索したとしてもSQL Serverとしては違うSQL文として見なします。
    • 回答としてマーク リオ 2010年10月16日 11:16
    2010年10月13日 14:50
    モデレータ
  • ElanLilacさん

    ご回答ありがとうございます。

    > SSMSのときとWEBからの時のプランを比較されたでしょうか?
    > プロファイラであればShowplan AllやShowplan statistics profile等を取得すればわかります。
    すいません。
    プロファイラを起動して、テンプレートで
    Showplan AllやShowplan statistics profileを追加してSQLの監視をするところまでは
    できたのですが、何を見ればプランが確認できるのかが分かりませんでした。
    もう少し詳しく説明をお願いできませんでしょうか?

    > なおWHERE句の条件指定を行う時に直接文字列で渡す時とパラメータで渡す時では
    > 結果同じ値で検索したとしてもSQL Serverとしては違うSQL文として見なします。
    そうなのですね。
    今回のSSMSからとWEBからのときの比較では、
    WEBからのときのプロファイラに記録されたパラメータ付きのSQLをそのままコピーして、
    SSMSで実行しました。
    なので、同じSQL文と見なしてくれているのかなと思っています。

     

    ElanLilacさんから教えて頂いたプランというキーワードを元に、
    私なりに調べてみまして、プランキャッシュに問題があるのではないかと推測して
    プランキャッシュをすべて削除してみました。
    実行したSQLは次の通りです。
    DBCC FREEPROCCACHE WITH NO_INFOMSGS;

    結果としてはこれで問題が解決されました。
    結果としては、実行プランに問題があったのだと思うのですが、
    プロファイラを元に実行プランに問題があったことを確認する方法は
    理解しておきたいと思っております。

    もしよろしければ、上に記載した質問に対してご回答頂けましたら、
    とても嬉しいです。

    よろしくお願い致します。

    • 回答としてマーク リオ 2010年10月16日 11:17
    2010年10月16日 11:14

すべての返信

  • SSMSのときとWEBからの時のプランを比較されたでしょうか?
    プロファイラであればShowplan AllやShowplan statistics profile等を取得すればわかります。
    もしプランが違うのだとすると、WEBからのクエリとSSMSからのクエリは違うものとして
    認識され、その結果片方が遅い結果になっているということです。
    なおWHERE句の条件指定を行う時に直接文字列で渡す時とパラメータで渡す時では
    結果同じ値で検索したとしてもSQL Serverとしては違うSQL文として見なします。
    • 回答としてマーク リオ 2010年10月16日 11:16
    2010年10月13日 14:50
    モデレータ
  • ElanLiacさんの挙げられているプランもそうですが、もっと根本的に、実際に実行されたSQL文は確認できていますか?
    リオさんは同じだと思っているかもしれませんが、実は違うSQL文だったりしませんか?
    2010年10月13日 15:19
  • ElanLilacさん

    ご回答ありがとうございます。

    > SSMSのときとWEBからの時のプランを比較されたでしょうか?
    > プロファイラであればShowplan AllやShowplan statistics profile等を取得すればわかります。
    すいません。
    プロファイラを起動して、テンプレートで
    Showplan AllやShowplan statistics profileを追加してSQLの監視をするところまでは
    できたのですが、何を見ればプランが確認できるのかが分かりませんでした。
    もう少し詳しく説明をお願いできませんでしょうか?

    > なおWHERE句の条件指定を行う時に直接文字列で渡す時とパラメータで渡す時では
    > 結果同じ値で検索したとしてもSQL Serverとしては違うSQL文として見なします。
    そうなのですね。
    今回のSSMSからとWEBからのときの比較では、
    WEBからのときのプロファイラに記録されたパラメータ付きのSQLをそのままコピーして、
    SSMSで実行しました。
    なので、同じSQL文と見なしてくれているのかなと思っています。

     

    ElanLilacさんから教えて頂いたプランというキーワードを元に、
    私なりに調べてみまして、プランキャッシュに問題があるのではないかと推測して
    プランキャッシュをすべて削除してみました。
    実行したSQLは次の通りです。
    DBCC FREEPROCCACHE WITH NO_INFOMSGS;

    結果としてはこれで問題が解決されました。
    結果としては、実行プランに問題があったのだと思うのですが、
    プロファイラを元に実行プランに問題があったことを確認する方法は
    理解しておきたいと思っております。

    もしよろしければ、上に記載した質問に対してご回答頂けましたら、
    とても嬉しいです。

    よろしくお願い致します。

    • 回答としてマーク リオ 2010年10月16日 11:17
    2010年10月16日 11:14
  • 佐祐理さん

    ご回答ありがとうございます。

    ElanLilacさんへの回答の中でも記載させて頂いたのですが、
    プロファイラを見てタイムアウトになっていたSQLを、プロファイラに書かれた形式そのままに
    SSMSから実行しましたので同じSQL文で間違いないと思っております。

    ご回答ありがとうございました。

    2010年10月16日 11:16
  • 解消したようで何よりです。

    プロファイラを使ってトレースを取得して、ファイルに保存しておきます。
    そしてそのファイルを別途プロファイラから参照し、該当するクエリを投げた時の
    showplan xxxのイベントを参照するとテキスト形式でプランが参照できます。
    http://msdn.microsoft.com/ja-jp/library/ms191501.aspx
    http://msdn.microsoft.com/ja-jp/library/ms187941.aspx

    手元に適切なサンプルが無かったのですが、プランを見るとインデックスを
    スキャンしているのか、シークしているのか、結合はNested Loopを使っているのか
    Hash Joinを使っているのか等を確認することができます。
    何が良いか悪いかは一概には言えないのですが、同じクエリの顔つきなのであれば
    良いときと悪いときのプランをそれぞれ比較するのが最も分かり易いと思います。

     

    2010年10月16日 15:02
    モデレータ